Transaction Management

Part:   1  2  3   4 

(Continued from previous part...)

Transaction Isolation Levels

As we can see from previous sections, the impact of a transaction in the current session is simple. However, concurrent transactions in multiple sessions may impact each other in many ways. Three phenomena have been observed in concurrent transactions:

• Dirty Read - One transaction T1 reads uncommitted changes from another transaction T2. If T2 performs a rollback later, T1 may have used incorrect data from the uncommitted changes.
• Non-Repeatable Read - One transaction T1 reads a row, which is changed and committed by another transaction T2 later. Now if T1 reads the same row again, the result will be will be different from the first read.
• Phantom - One transaction T1 reads a set of rows that satisfy a condition. Another transaction T2 then inserts some new rows that satisfy the same condition. If T1 repeats the same read, it will receive some "phantom" rows.

To be able to control and avoid those phenomena, 4 transaction isolation levels have been defined by SQL standards:

• Read Uncommitted - This is the lowest isolation level. All three phenomena are possible.
• Read Committed - Dirty Read is prevented. But Non-Repeatable Read and Phantom are possible.
• Repeatable Read - Dirty Read and Non-Repeatable Read are prevented. But Phantom is still possible.
• Serializable - This is the highest isolation level. All three phenomena are prevented.

MySQL Support of Transaction Isolation Levels

• Transaction isolation levels are supported by the InnoDB storage engine.
• The default isolation level is "Repeatable Read".
• The SET statement can be used to change the isolation level for the next transaction: "SET TRANSACTION ISOLATION LEVEL level_name".
• The SET statement can be used to change the isolation level for the entire session, starting with the next transaction: "SET SESSION TRANSACTION ISOLATION LEVEL level_name".

MySQL Transaction Isolation Level Test - Read Uncommitted

To understand better the "Read Uncommitted" isolation level, I did the following test with two command windows.

In the first window, I started "session 1", and issued several statements at different times:

\mysql\bin\mysql test
mysql> SELECT 'Session 1';
+-----------+
| Session 1 |
+-----------+ 
mysql> DROP TABLE IF EXISTS User;
mysql> CREATE TABLE User (ID INT PRIMARY KEY, Name CHAR(8))
   ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> INSERT INTO User VALUES (2, 'bill');
mysql> SELECT CURRENT_TIME();
+----------------+
| 20:08:40       |
+----------------+
mysql> -- continue with session 2
mysql> SELECT CURRENT_TIME();
+----------------+
| 20:20:57       |
+----------------+
mysql> ROLLBACK;
mysql> -- continue with session 2

In the second window, I started "session 2", and started a transaction with "read uncommitted":

\mysql\bin\mysql test
mysql> SELECT 'Session 2';
+-----------+
| Session 2 |
+-----------+ 
mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
mysql> START TRANSACTION;
mysql> SELECT CURRENT_TIME();
+----------------+
| 20:17:35       |
+----------------+
mysql> SELECT * FROM User;
+----+------+
|  2 | bill |
+----+------+
mysql> -- continue with session 1
mysql> SELECT CURRENT_TIME();
+----------------+
| 20:23:28       |
+----------------+
mysql> SELECT * FROM USER;
Empty set (0.00 sec)

The result clearly shows that "dirty read" could indeed happen in a transaction with "read uncommitted" isolation level.

(Continued on next part...)

Part:   1  2  3   4