MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang
"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
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
PHP Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
►Transaction Management and Isolation Levels
Transaction Management in MySQL
"Read Uncommitted" Isolation Level
"Read Committed" Isolation Level
►"Repeatable Read" Isolation Level
Defining and Calling Stored Procedures