"Repeatable Read" Isolation Level

MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang

MySQL Tutorials - Herong's Tutorial Examples

∟Transaction Management and Isolation Levels

∟"Repeatable Read" Isolation Level

A tutorial example is provided on the 'Repeatable Read' isolation level, where reads are guarantied to be repeatable.

My next test is about "repeatable read". Again, I used with two command window sessions.

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 'Session 2';
                              +-----------+
                              | Session 2 |
                              +-----------+ 
                              mysql> SET TRANSACTION ISOLATION LEVEL 
                                 REPEATABLE READ;
                              mysql> START TRANSACTION;
                              mysql> SELECT * FROM User WHERE ID>=2;
                              +----+------+
                              |  2 | bill |
                              +----+------+

mysql> UPDATE User SET Name='bob' WHERE ID='2';
mysql> INSERT INTO User VALUES (3, 'jack');

                              mysql> SELECT CURRENT_TIME();
                              mysql> SELECT * FROM User WHERE ID>=2;
                              +----+------+
                              |  2 | bill |
                              +----+------+

The result clearly shows that "repeatable read" isolation level indeed guaranties repeatable read. In session 2, the second "SELECT * FROM User WHERE ID>=2;" statement returned "bill" for id=2, which is the same as the first "SELECT * FROM User WHERE ID>=2;" statement.

But the result failed to show the phantom phenomenon, which should return the new "phantom" record of id=3. Any body knows how to create a phantom phenomenon?

There is no need to test the "serializable" isolation level, because "repeatable read" isolation level seems to be behaving the same way as "serializable".

Last update: 2015.

Table of Contents

About This Book

Introduction of SQL

MySQL Introduction and Installation

Introduction of MySQL Programs

Perl Programs and MySQL Servers

PHP Programs and MySQL Servers

Java Programs and MySQL Servers

Datatypes and Data Literals

Operations and Expressions

Character Strings and Bit Strings