Database Isolation Levels

Every database follows the ACID (Atomicity, Consistency, Isolation, Durability) properties.

Isolation: A transaction should happen in a way that it is the only transaction that is accessing the resources in the database system.

Isolation phenomena

Isolation for a database is described by the following 3 phenomena:

1. Dirty Read: Data that is updated but not yet committed by one transaction can be read by another transaction. In the end, if the former transaction reverts the update, the second transaction has the incorrect value.

2. Non-Repeatable Read: Executing a query twice gives you the same rows but different data. These queries can be different though.

3. Phantom Read: Executing similar select query results in different rows. It happens when new rows are inserted or removed from the table.

Isolation Levels

Based on the above phenomena we have four different isolation levels

1. Read Uncommitted: This is the lowest isolation level and does not rectify issues from any of the read phenomena.

2. Read Committed: Transaction acquires write lock on the rows that it is updating. But it releases the read lock as soon as the SELECT queries are completed. The write lock guarantees that whatever data is read is always committed thereby eliminating dirty reads.

3. Repeatable Read: Here the transaction acquires read and write locks on the selected data until the end of the transaction. Therefore guaranteeing repeatable reads, but phantom reads can still occur.

4. Serializable: This is the highest isolation level. Apart from acquiring read and write locks on selected data, it also acquires range locks for SELECT queries with ranged WHERE clause¹.

[1]: Serializable isolation level can also be implemented with non lock concurrency methods. This is done with snapshots to avoid write collisions.

[2]: Everything above is not a SQL statement, it’s pseudocode!

Thank you for reading, and I hope you learned something new today. If you want to provide any feedback or suggestions, please let me know in the comments. Have a great day!