Isolation. Repeatable Read and Serializable Levels

Repeatable Read

The Repeatable Read isolation level in databases ensures that when you start a transaction, you see a snapshot of the database that remains the same throughout your task.
This means that even if other changes happen to the database while you're working, you'll only see the data as it was when you began.
Transactions under Repeatable Read isolation hold read locks on all rows they reference and write locks on rows they insert, update, or delete. This prevents lost updates, dirty and non-repeatable reads.
However, there is one issue that still occurs while using this type of isolation - phantom read.

Phantom read

The phantom read can occur when one transaction is reading data and another transaction adds new records to the database. Consequently, we may get different result sets for two consecutive read transactions.

Non-repeatable read vs phantom read

Non-repeatable read occurs when the same query executed by User A returns different values for the same row between consecutive reads, typically due to modifications by User B.

Phantom read, on the other hand, happens when the same query executed by User A returns different sets of rows between consecutive reads, even though individual rows themselves may not have been updated.

We can specify this isolation level in our SQL query using the folllowing command:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Serializable

The Serializable isolation level is the highest level of isolation in databases, ensuring the most stringent form of concurrency control.
In this level, transactions are executed as if they were processed serially, one after the other, even though they may actually be executed concurrently. This ensures that the outcome of the transactions is the same as if they were executed in some serial order, maintaining consistency and preventing all discovered concurrency anomalies.

Note

Using this type of isolation may lead to decreased performance due to increased locking.

We can use the following statement to set this isolation level in our SQL query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;