Learn Isolation. Repeatable Read and Serializable Levels

Swipe to show menu

Understanding the differences between the Repeatable Read and Serializable isolation levels is crucial when working with transactions in SQL, especially in scenarios involving financial data such as the bank_accounts and transfers tables. Both isolation levels are designed to prevent certain anomalies that can occur with concurrent transactions, but they offer different guarantees and impact performance in distinct ways.

With Repeatable Read, once you read a row within a transaction, that row’s data will not change if you read it again within the same transaction—even if another transaction modifies it and commits. This level prevents non-repeatable reads, ensuring consistency for rows you have already accessed. However, Repeatable Read does not fully prevent phantom reads. A phantom read occurs when a transaction re-executes a query returning a set of rows that satisfy a condition and finds that the set has changed due to another transaction inserting or deleting rows.

Serializable is the strictest isolation level. It ensures that transactions are executed in such a way that the outcome is as if they had been run one after another, never concurrently. This level prevents phantom reads by locking the range of data that a query accesses, blocking other transactions from inserting, updating, or deleting rows that would affect the result set. As a result, Serializable provides the highest level of data integrity but can lead to more blocking and reduced concurrency.

Consider a scenario with the bank_accounts table: if one transaction is reading all accounts with balances above $1,000, and another transaction is inserting a new account with a balance of $2,000, Repeatable Read may allow the second transaction to insert the new row, so a repeat of the original query within the first transaction could see this "phantom" row. Serializable would prevent this by blocking the insert until the first transaction is complete.


              12345678910111213141516171819202122232425262728293031323334
            
-- Transaction 1: Set isolation level to Repeatable Read and read accounts
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM bank_accounts WHERE balance > 1000;

-- Transaction 2: Insert a new account with balance > 1000
BEGIN;
INSERT INTO bank_accounts (owner_name, balance) VALUES ('Frank Miller', 2500.00);
COMMIT;

-- Transaction 1: Re-run the same SELECT
SELECT * FROM bank_accounts WHERE balance > 1000;
COMMIT;

-- In Repeatable Read, Transaction 1 may see the new row ("phantom read").

-- Now, using Serializable isolation level

-- Transaction 1: Set isolation level to Serializable and read accounts
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM bank_accounts WHERE balance > 1000;

-- Transaction 2: Try to insert a new account with balance > 1000
BEGIN;
INSERT INTO bank_accounts (owner_name, balance) VALUES ('George Lucas', 3000.00);
-- This operation will block until Transaction 1 commits or rollbacks
COMMIT;

-- Transaction 1: Re-run the same SELECT
SELECT * FROM bank_accounts WHERE balance > 1000;
COMMIT;

-- In Serializable, Transaction 1 will not see new rows inserted by other transactions until it is finished, preventing phantom reads.

Choosing between Repeatable Read and Serializable depends on your application's requirements and the trade-offs you are willing to make. Use Repeatable Read when you need to protect against non-repeatable reads but can tolerate the rare occurrence of phantom reads, which may be acceptable in many business applications for the sake of better performance and concurrency. Opt for Serializable when absolute consistency and correctness are critical, such as in banking or financial systems where phantom reads could result in incorrect calculations or double spending. However, Serializable can significantly reduce throughput due to increased locking and blocking, so it should be reserved for scenarios where its guarantees are truly necessary.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 6

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 1. Chapter 6

Isolation. Repeatable Read and Serializable Levels

1. What is the main difference between Repeatable Read and Serializable?

2. Which isolation level prevents phantom reads?

3. When might you choose a lower isolation level?