Transaction Isolation Levels in RDBMS

by

In this blog post, we have discussed about the ACID property of a database transaction and different isolation levels available to use for that transaction.

A transaction is a single unit of operation we either execute it entirely or do not execute it at all.

ACID properties must be followed for a transaction operation to maintain the integrity of the database.

What is ACID

ACID is an acronym which denotes a set of properties a database transaction should follow:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Definition of each property:AtomicityEither the transaction will be execute entirely or will not be executed at all.ConsistencyWhen the transaction has been executed then the database will move from one consistent state to another consistent state.Isolation

Transaction should be executed in isolation of other transactions.

Two concurrent transactions should not impact the another transaction’s flow/data. Even if these 2 transactions are running concurrently, but the result should be like as they would have run sequentially.DurabilityAfter successful completion of the transaction, the changes in the database should persist. Even if the application server/system gets restarted or failed.

What is Isolation Level

For the third transactional property defined above (I: Isolation), we can specify different levels for that.

Different isolation levels describe – how changes applied by concurrent transactions are visible to each other.

Each isolation level prevents zero or more concurrency side effects on a transaction. Ex: dirty read, nonrepeatable-read, phantom read.

Each isolation level prevents zero or more concurrency side effects on a transaction. Ex: dirty read, nonrepeatable-read, phantom read.

  1. Dirty Read: Read the uncommitted changes of a concurrent transaction.
  2. Non Repeatable Read: Get different value on re-read(in a single transaction only) of a row if a concurrent transaction updates the same row and commits.
  3. Phantom Read: Get different rows after re-execution of a range query if another transaction adds or removes some rows in the range and commits.

Different Transaction Isolation Levels

There are 4 types of isolation levels:

  1. READ_UNCOMMITTED:
    • This is the lowest level isolation(not supported in Postgres, Oracle)
    • We can set isolation level for method or class.
    • This level suffers from all the 3 above mentioned concurrency side effects.
    • This level allows for most concurrent access.
  2. READ_COMMITTED: (Default in Postgres, SQL Server, Oracle)
    • This isolation level prevents dirty read. So, any uncommitted changes in concurrent transactions have no impact on us, but if a transaction commits it’s changes then we can get different results when we do re-query.
  3. REPEATABLE_READ: (Default in Mysql, Oracle doesn’t support)
    • This isolation level prevents dirty and non-repeatable reads. So,we are not affected by uncommitted changes in concurrent transactions.
    • When we re-query for a row, we don’t get different result but if we re-execute the range query , we might get newly added or removed rows.
    • This is the minimum required isolation level to prevent the lost update. (Lost Update happens when 2 or more concurrent transactions read and update the same row.)
    • This level does not allow simultaneous access to row at all. Hence lost update can’t happen.
  4. SERIALIZABLE:
    • This is the highest level of isolation.
    • This isolation level prevents all the side effects.
    • In this isolation level, lowest concurrent access rate, since this level executes concurrent calls sequentially.

Leave a Comment