Course Content
Advanced Techniques in SQL
Advanced Techniques in SQL
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
For example, suppose a transaction queries all orders with a total amount greater than $1000. If another transaction inserts a new order with a total amount meeting this condition between the first and second execution of the query, the first transaction will observe this additional row in the second execution, resulting in a 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.
| Isolation Level \ Data Anomaly | Dirty Read | Non-Repeatable Read | Lost Update | Phantom Read |
|---|---|---|---|---|
| Read Uncommitted | + | + | + | + |
| Read Committed | - | + | + | + |
| Repeatable Read | - | - | - | + |
| Serializable | - | - | - | - |
We can use the following statement to set this isolation level in our SQL query:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Everything was clear?
Course Content
Advanced Techniques in SQL
Advanced Techniques in SQL
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
For example, suppose a transaction queries all orders with a total amount greater than $1000. If another transaction inserts a new order with a total amount meeting this condition between the first and second execution of the query, the first transaction will observe this additional row in the second execution, resulting in a 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.
| Isolation Level \ Data Anomaly | Dirty Read | Non-Repeatable Read | Lost Update | Phantom Read |
|---|---|---|---|---|
| Read Uncommitted | + | + | + | + |
| Read Committed | - | + | + | + |
| Repeatable Read | - | - | - | + |
| Serializable | - | - | - | - |
We can use the following statement to set this isolation level in our SQL query:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Everything was clear?
