Transaction Management

Part:   1  2  3  4  

(Continued from previous part...)

MySQL Transaction Isolation Level Test - Read Committed

My next test is about "read committed". Again, I started "session 1" in the first command window:

\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> INSERT INTO User VALUES (2, 'bill');
mysql> SELECT CURRENT_TIME();
+----------------+
| 21:11:51       |
+----------------+
mysql> -- continue with session 2
mysql> UPDATE User SET Name='bob' WHERE ID=2;
mysql> SELECT CURRENT_TIME();
+----------------+
| 21:16:57       |
+----------------+
mysql> -- continue with session 2

In the second command window, I used a "read committed" transaction:

\mysql\bin\mysql test
mysql> SELECT 'Session 2';
+-----------+
| Session 2 |
+-----------+ 
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> START TRANSACTION;
mysql> SELECT CURRENT_TIME();
+----------------+
| 21:13:51       |
+----------------+
mysql> SELECT * FROM User WHERE ID=2;
+----+------+
|  2 | bill |
+----+------+
mysql> -- continue with session 1
mysql> SELECT CURRENT_TIME();
+----------------+
| 21:19:25       |
+----------------+
mysql> SELECT * FROM User WHERE ID=2;
+----+------+
|  2 | bob  |
+----+------+

The result clearly shows that "non-repeatable read" could indeed happen in a transaction with "read committed" isolation level.

MySQL Transaction Isolation Level Test - Repeatable Read

My next test is about "repeatable read". Again, I started "session 1" in the first command window:

\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> INSERT INTO User VALUES (2, 'bill');
mysql> SELECT CURRENT_TIME();
+----------------+
| 22:10:47       |
+----------------+
mysql> -- continue with session 2
mysql> UPDATE User SET Name='bob' WHERE ID='2';
mysql> INSERT INTO User VALUES (3, 'jack');
mysql> SELECT CURRENT_TIME();
+----------------+
| 22:16:36       |
+----------------+
mysql> -- continue with session 2

In the second command window,

\mysql\bin\mysql test
mysql> SELECT 'Session 2';
+-----------+
| Session 2 |
+-----------+ 
mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> START TRANSACTION;
mysql> SELECT CURRENT_TIME();
+----------------+
| 22:13:23       |
+----------------+
mysql> SELECT * FROM User WHERE ID>=2;
+----+------+
|  2 | bill |
+----+------+
mysql> -- continue with session 1
mysql> SELECT CURRENT_TIME();
+----------------+
| 22:19:54       |
+----------------+
mysql> SELECT * FROM User WHERE ID>=2;
+----+------+
|  2 | bill |
+----+------+

The result clearly shows that "repeatable read" isolation level indeed guaranties repeatable read. But the result failed to show the phantom phenomenon. Any body knows how to create a phantom phenomenon?

Part:   1  2  3  4