Now that we've got our concepts clear on the prerequisites, let's talk about isolation levels.

Isolation Levels

Isolation is the property of databases that ensures their integrity. Isolation levels are levels defined within a database system, which define how much a certain transaction needs to be isolated from any modifications made in the database by other transactions.

The SQL standard defines four isolation levels.

Read Uncommitted

The lowest isolation level allowed by SQL is read uncommitted. In this level, transactions can read data that is not committed by other transactions, permitting dirty read. It is safe to say that transactions are not isolated from each other in this isolation level.

Read Committed

This isolation level allows for the reading of data after it is committed by a transaction. This means no dirty reads are possible. This isolation is unable to prevent non-repeatable reads. This is ensured as locks are applied on the row being read. This prevents the reading, updating, and deletion of data.

Consider an example where a transaction needs to read twice, after some time gap. It is possible that another transaction comes and updates data after the first and before the second read of the initial transaction. The data read would be committed which prevents dirty read, but the values have changed.

Repeatable Read

This isolation tries to improve on the previous isolation level by preventing both dirty reads and non-repeatable reads. This prevention is done by applying locks on rows that are read and rows that perform write operations. However, this cannot prevent the addition of new entries to the database if the transaction is performing some work (not between two reads).

Serializable

The highest isolation level allowed is serializable. This isolation level looks like a serial execution, with almost no concurrency. All the three read phenomena are prevented in this isolation level, but it compromises concurrent execution.