Isolation levels in DBMS

Introduction

Isolation is one of the ACID (Atomicity, Consistency, Isolation, Durability) guarantees that a DBMS provides which means that concurrently executing transactions are isolated from each other.

Isolation levels

The following isolation levels are defined by the SQL standard:

1. Serializable - This is the strongest isolation level wherein the database guarantees that for concurrently running transactions the end result would be same as if they ran in some serial order without any concurrency. In practice, serializable isolation has performance implications and database management systems generally provide weaker levels of isolation as discussed below.

2. Repeatable Reads - This isolation level guarantees that once a transaction reads a data, it would not change for the rest of the transaction. Transactions running with this isolation level could be prone to phantom reads.

3. Read Committed - This isolation level guarantees that a transaction can only read committed data, thereby preventing dirty reads. However, transactions running with this isolation level could encounter non-repeatable reads and phantom reads.

4. Read Uncommitted - This is the weakest isolation level and transactions running with this isolation level could encounter dirty reads, non-repeatable reads and phantom reads.

Conclusion

Choosing an Isolation level would depend on the requirements of an application. Stronger isolation levels provide higher data consistency whereas weaker isolation levels provides more performance.