Learn Isolation

SQL Optimization and Query Features

Swipe to show menu

Isolation is a fundamental property of transactions in SQL, ensuring that concurrent transactions do not interfere with each other in a way that could produce incorrect or inconsistent results. When multiple users or processes access and modify the same data at the same time, several types of anomalies can occur if isolation is not properly enforced. Some common transaction anomalies include:

Dirty reads: when one transaction reads data that has been written by another transaction but not yet committed;
Lost updates: when two transactions overwrite each other's changes without proper coordination;
Non-repeatable reads: when a transaction reads the same row twice and gets different data each time because of another concurrent transaction;
Phantom reads: when a transaction re-executes a query and finds rows that were not present before due to another transaction's inserts or deletes.

By applying the right isolation level, SQL databases prevent these anomalies, ensuring that the results of transactions are reliable and predictable, even under heavy concurrency.


              12345678910111213141516171819202122
            
-- Transaction 1: Transfer $1000 from Alice (account 1) to Bob (account 2)
BEGIN TRANSACTION;
UPDATE bank_accounts SET balance = balance - 1000 WHERE account_id = 1;

-- Before Transaction 1 commits, Transaction 2 starts:
-- Transaction 2: Transfer $500 from Alice (account 1) to Charlie (account 3)
BEGIN TRANSACTION;
UPDATE bank_accounts SET balance = balance - 500 WHERE account_id = 1;

-- If both transactions read Alice's balance at the same time and subtract their amounts,
-- but only one value is written back, the other update could be lost.
-- With proper isolation (e.g., REPEATABLE READ or SERIALIZABLE), the database ensures
-- that both updates are applied correctly, preventing lost updates.

-- Commit Transaction 1
COMMIT;

-- Commit Transaction 2
COMMIT;

-- Show resulting balances in the accounts table
SELECT account_id, owner_name, balance FROM bank_accounts ORDER BY account_id;

In the code sample above, two transactions attempt to transfer money out of Alice's account at nearly the same time. Without adequate isolation, both transactions might read the same initial balance, subtract their respective amounts, and then write back their results. This could cause one update to overwrite the other, resulting in a lost update and incorrect account balance.

Isolation mechanisms—such as locking rows or using higher isolation levels like REPEATABLE READ or SERIALIZABLE—prevent these problems. When isolation is enforced, the database ensures that once a transaction modifies a row, other concurrent transactions cannot access or change the same row until the first transaction is complete. This guarantees that all updates are applied safely and no data is lost or corrupted, even under concurrent access.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 5

Ask AI

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

Section 1. Chapter 5

Isolation

1. What is a dirty read?

2. How does isolation prevent lost updates?

3. Which isolation level offers the highest protection against anomalies?