Isolation

In the context of databases, isolation refers to the ability of a database system to control the visibility of changes made by concurrent transactions. It ensures that transactions operate in a manner that is independent of each other, avoiding interference and maintaining data integrity.

There are 4 isolation levels in SQL:

• read uncommitted;
• read committed;
• repeatable read;
• serializable.

Read uncommitted

This is the lowest isolation level where transactions can see changes made by other transactions even before they are committed. This level allows dirty reads, meaning a transaction can read data that has been modified by another transaction but not yet committed.

Dirty reads

Implementation

To specify the isolation level for the transaction we can use the following command in our query:

-- Start transaction
BEGIN;
-- Set the isolation level for the transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/* Transaction query */
COMMIT;

• SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;: This statement changes the isolation level for the current transaction to "Read Uncommitted", allowing the transaction to potentially read data modified by other uncommitted transactions;

• this command must be used inside the transaction block only! Otherwise, it will have no effect, and a default isolation level will be used.

We can also check the current isolation level using the following command:
SHOW TRANSACTION ISOLATION LEVEL;

Read committed

The Read Committed isolation level ensures that a transaction only sees data that has been committed by other transactions.
This means that uncommitted changes made by other transactions are not visible to transactions operating under Read Committed isolation.
As a result, it prevents dirty reads by allowing a transaction to read only committed data. However, this transaction level has problems with non-repeatable reads.

Non-repeatable reads

The "Read committed" isolation level allows non-repeatable reads because it locks the read operation on values that are in the process of uncommitted transactions but doesn't lock the write operation.
As a result, we can write new data to the row that is currently being read by another transaction.

Lost update

Due to the absence of the write lock there is one more problem with read committed isolation level - lost updates.

The lost updates occur when two parallel transactions attempt to change the same row. As a result, the transaction that is committed later overwrites the values committed by other transactions.

Implementation

We can also specify this isolation level using the following commands:

-- Start transaction
BEGIN;
-- Set the isolation level for the session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* Transaction query */
COMMIT;

It's important to notice that Read Committed is the default isolation level for most Database Management Systems, which is why we can skip specifying it.