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 walk through this article to explore in more details

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

  • Dirty reads occur when 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

  • Non-repeatable reads occur when 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

  • Phantom reads occur when 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 serializable. 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: 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