Transaction Isolation Levels in RDBMS
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.
A: Atomicity
C: Consistency
I: Isolation
D: Durability
Atomicity: Either the transaction will be execute entirely or will not be executed at all.
Consistency: When 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.
So, during the current transaction execution, intermediate transaction results of another (concurrently running) transaction should not be available to each other.
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.
Although, we will talk about this in detail later in this article.
Durability: After successful completion of the transaction, the changes in the database should persist. Even if the application server/system gets restarted or failed.
- 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.
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 5 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.
