Parallel Transactions in mysql

Question

My database contains table named sample_table consists of one column named as user_first_name and the table was empty

Suppose I have 2 transactions like this

START TRANSACTION;
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
//......... till 10000 times
COMMIT

START TRANSACTION;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
//........ till 10000 times 
COMMIT

I run these two transactions in parallel in such a way, first the transaction1 started and then transaction2 when transaction1 was running. I expected that the result will have some rows with name TEJA. But I am getting an empty result. Please help me to explain why the result is empty?

Answer 1

It all depends on the ISOLATION LEVEL you have set.

You can see which one you have set with this query:

select @@global.tx_isolation, @@session.tx_isolation;

Before explaining the different isolation levels, let me explain which problems they are trying to avoid:

• The dirty read: Data of a not yet finished transaction are read by another transaction.

• The lost update: Two transactions modify an entry in a table in parallel. When both transactions finished, only one modification is applied.

• The non-repeatable read: Reoccuring reads lead to different results.

• The phantom read: During a transaction another transaction adds or removes table entries.

Now for the different isolation levels.

Read Uncommitted:
With this isolation level read operations ignore any kind of locks, therefore any of the above mentioned problems can occur.

Read Committed:
This isolation level sets a write lock for the whole transaction on objects that should be modified. Read locks are only set when reading data. Therefore non-repeatable read and phantom read can occur.

Repeatable Read:
With this isolation level it is ensured, that reoccuring read operations always yield the same result when the parameters are the same. Locks are set for read and write operations for the total duration of the transaction. Therefore only phantom reads can occur.

Serializable:
The highest isolation level guarantees, that the result of parallel running transactions is the same as if the transactions would be running one after another. Most databases do not really run those transactions one after another, it would be too much of a performance loss. Therefore it can happen, that one transaction gets aborted. MySQL for example realizes this with MVCC (Multi-Version Concurrency Control). Google it, if you want to know more. It's too much for this answer.

All in all this table explains it as well:

                 | Lost updates | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------------------------------------------------------------------
Read Uncommitted | possible     | possible   | possible            | possible
Read Committed   | impossible   | impossible | possible            | possible
Repeatable Read  | impossible   | impossible | impossible          | possible
Serializable     | impossible   | impossible | impossible          | impossible

• In this manual entry is some more info about it and how to set the isolation level.

---

All this is just theory for relational database management systems. Since MySQL uses MVCC (multi version concurrency control) however, phantom reads do not occur when using InnoDB and repeatable read isolation level.
From the manual:

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.