Transaction isolation levels are used for determining how one transaction isolated from data modifications made by other transactions

It is one of the ACID properties of database transactions. ACID stands for Atomic, Consistency, Isolation, and Durability

Let's get started!

The read phenomena

There are 3 read phenomena defined by the SQL 92 standard when Transaction 1 (T1) reads data that Transaction 2 (T2) might have changed

  • A dirty read occurs when a transaction is allowed to read data from a row that has been modified but not yet committed by another running transaction

    Say T2 modifies a row. T1 then reads that row before T2 performs a COMMIT. If T2 then performs a ROLLBACK, T1 will have read a row that was never committed and that may thus be considered to have never existed

  • A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads

    Say T1 reads a row. T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted

  • A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first

    Say T1 reads the set of rows that satisfy some search condition S. T2 then adds or removes rows that satisfy S used by T1. If T1 repeats the initial read with the same S, it obtains a different collection of rows

The isolation levels

There are 4 isolation levels including Read uncommitted, Read committed, Repeatable read, and Serialize read. They are defined by 3 read phenomena including dirty, non-repeatable, and phantom reads

  • Read uncommitted: all of the read phenomena may occur

  • Read committed: non-repeatable and phantom reads may occur

  • Repeatable read: only phantom reads may occur

  • Serialize read: all of the read phenomena don't occur

Default isolation level in DBMS

The default isolation level varies between DBMS

  • Repeatable read is the default in MySQL

  • Read committed is the default in PostgreSQL, Oracle, SQL Server

  • Read uncommitted is the default in MongoDB

References

Design
Share to social

Van N.

Van N. is a software engineer, creator of HelloKoding. He loves coding, blogging, and traveling. You may find him on GitHub and LinkedIn