Transaction Isolation Levels
This section describes 4 transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
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
- 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
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".
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
Commonly Used Functions
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
What Is a Transaction?
Transaction Management in MySQL
►Transaction Isolation Levels
"Read Uncommitted" Isolation Level
"Read Committed" Isolation Level
"Repeatable Read" Isolation Level
Locks Used in MySQL
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
PDF Printing Version