Transation Isolation Levels


Discussing with a colegue about defining transactions in our application we finished by talking about transaction isolation levels and finally I got into problems explaining the problems faced in concurrent transactions (I wasn’t able to tell the difference between non-repeatable and phantom reads [sigh/]). I decided to write down for further references.

Transaction isolation levels are defined according to the state of data during the following 3 scenarios (they compair the data accessed from 2 different transactions):
* dirty reads
occur when a transaction reads data written by concurrent uncommited transaction;
* non-repeatable reads
occur when a transaction re-reads data it has previously read and finds that data has been
modified by another transaction (that committed since the initial read);
* phantom read
occur when a transaction re-executes a query returning a set of rows that satisfy a search
condition and finds that the set of rows satisfying the condition has changed due to another
recently-committed transaction;

Note: even if it seems that non-repeatable reads and phantom reads are the same thing, the difference is made from the fact that in the 1st case the same rows are returned but their data is modified, while in the 2nd case another set of rows is returned.
Defined isolation levels:

DB Isolation Level Dirty Read Non-Repeatable Reads Phantom read
Read uncommitted Y Y Y
Read committed N Y Y
Repeatable read N N Y
Serializable N N N
Java Level Dirty Read Non-Repeatable Reads Phantom read
TRANSACTION_NONE Y Y Y
TRANSACTION_READ_UNCOMMITTED Y Y Y
TRANSACTION_READ_COMMITED N Y Y
TRANSACTION_REPEATABLE_READ N N Y
TRANSACTION_SERIALIZABLE N N N


Still I would like to see more details on the separation made on non-repeatable reads and phantom reads.

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s