Transaction isolation levels in SQL - Step-by-Step Execution

Concept Flow - Transaction isolation levels

Start Transaction

↓

Set Isolation Level

↓

Execute Queries

↓

Check for Conflicts

↓

No Conflicts

↓

Commit

↓

End Transaction

This flow shows how a transaction starts, sets its isolation level, executes queries, checks for conflicts or read anomalies, and then commits or rolls back accordingly.

Execution Sample

SQL

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

A transaction starts with the highest isolation level, reads data, updates it, and commits to ensure no conflicts or anomalies.

Execution Table

Step	Action	Isolation Level	Data Read	Data Written	Conflict Check	Result
1	BEGIN TRANSACTION	Default (Read Committed)	None	None	None	Transaction started
2	SET ISOLATION LEVEL SERIALIZABLE	Serializable	None	None	None	Isolation level set
3	SELECT accounts WHERE id=1	Serializable	Row with id=1, balance=500	None	None	Data read with full isolation
4	UPDATE accounts SET balance=balance-100 WHERE id=1	Serializable	Same row	Balance updated to 400	Check for write conflicts	No conflicts, update allowed
5	COMMIT	Serializable	None	None	Final conflict check	Transaction committed successfully

💡 Transaction ends after commit with no conflicts under SERIALIZABLE isolation level

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Isolation Level	Read Committed	Serializable	Serializable	Serializable
Data Read	None	Row(id=1, balance=500)	Row(id=1, balance=500)	None
Data Written	None	None	Row(id=1, balance=400)	None
Transaction State	Not started	Active	Active	Committed

Key Moments - 3 Insights

Why does the data read at step 3 show balance=500 even though it will be updated later?

Because the SELECT reads the current committed data before the UPDATE changes it, as shown in execution_table row 3 and 4.

What happens if a write conflict is detected at step 4?

The transaction would either wait or rollback to avoid dirty writes, but in this example no conflict occurs (row 4).

Why is the isolation level set before executing queries?

Setting isolation level first ensures all queries in the transaction follow the same rules to prevent anomalies, as shown in step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the isolation level after step 2?

A Repeatable Read

B Read Committed

C Serializable

D Read Uncommitted

💡 Hint

Check the Isolation Level column at step 2 in the execution_table.

At which step is the data balance updated to 400?

A Step 4

B Step 3

C Step 5

D Step 2

💡 Hint

Look at the Data Written column in the execution_table.

If the isolation level was Read Uncommitted, what anomaly might occur during the SELECT at step 3?

A No anomaly

B Dirty Read

C Phantom Read

D Write Conflict

💡 Hint

Consider what Read Uncommitted allows compared to Serializable in the concept_flow.

Concept Snapshot

Transaction Isolation Levels:
- Control how transactions see and affect data
- Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
- Higher levels prevent more anomalies but reduce concurrency
- Set isolation level at transaction start
- Commit or rollback ends transaction

Full Transcript

This visual execution shows a transaction starting, setting its isolation level to SERIALIZABLE, reading data, updating it, and committing. The isolation level controls how data is read and written to avoid conflicts and anomalies. The execution table tracks each step, showing the isolation level, data read and written, conflict checks, and results. Variables like isolation level and transaction state change as the transaction progresses. Key moments clarify why data reads show old values before updates and the importance of setting isolation level early. The quiz tests understanding of isolation levels and their effects on data visibility and conflicts.

Practice

1. Which transaction isolation level allows a transaction to see uncommitted changes made by other transactions?

easy

A. READ UNCOMMITTED

B. READ COMMITTED

C. REPEATABLE READ

D. SERIALIZABLE

Solution

1. Step 1: Understand isolation levels and identify the matching one

   READ UNCOMMITTED is the lowest isolation level allowing dirty reads (seeing uncommitted changes from other transactions).

2. Final Answer:

   READ UNCOMMITTED -> Option A

3. Quick Check:

   Dirty reads = READ UNCOMMITTED [OK]

Hint: Lowest level allows dirty reads: READ UNCOMMITTED [OK]

Common Mistakes:

• Confusing READ COMMITTED with allowing dirty reads
• Thinking SERIALIZABLE allows uncommitted reads
• Mixing REPEATABLE READ with dirty read behavior

2. Which of the following is the correct SQL syntax to set the transaction isolation level to SERIALIZABLE?

easy

A. SET ISOLATION LEVEL TRANSACTION SERIALIZABLE;

B. SET TRANSACTION LEVEL ISOLATION SERIALIZABLE;

C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

D. SET SERIALIZABLE ISOLATION LEVEL;

Solution

1. Step 1: Recall and match the correct SQL syntax

   The standard syntax is SET TRANSACTION ISOLATION LEVEL [level]; Only SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches exactly.

2. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option C

3. Quick Check:

   Correct syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; [OK]

Hint: Remember: SET TRANSACTION ISOLATION LEVEL [level]; [OK]

Common Mistakes:

• Swapping keywords order incorrectly
• Omitting 'TRANSACTION' keyword
• Using non-standard syntax

3. Consider two transactions T1 and T2 running concurrently. T1 reads a row, then T2 updates and commits that row. If the isolation level is REPEATABLE READ, what will T1 see when it reads the same row again?

medium

A. The updated value committed by T2

B. The original value before T2's update

C. An error due to concurrent update

D. A partially updated value

Solution

1. Step 1: Understand REPEATABLE READ and apply to scenario

   REPEATABLE READ ensures a transaction sees the same data on repeated reads of the same row. T1's second read sees the original value despite T2's committed update.

2. Final Answer:

   The original value before T2's update -> Option B

3. Quick Check:

   REPEATABLE READ prevents non-repeatable reads = The original value before T2's update [OK]

Hint: REPEATABLE READ keeps data stable during transaction [OK]

Common Mistakes:

• Assuming T1 sees T2's committed update immediately
• Confusing REPEATABLE READ with READ COMMITTED
• Thinking it causes errors on concurrent updates

4. You wrote this SQL command to set the isolation level but get an error:
SET TRANSACTION LEVEL ISOLATION READ COMMITTED;
What is the error in this statement?

medium

A. The statement is correct; error is elsewhere.

B. 'TRANSACTION' keyword is missing.

C. 'READ COMMITTED' is not a valid isolation level.

D. The order of 'LEVEL' and 'ISOLATION' is incorrect.

Solution

1. Step 1: Check correct syntax and identify the error

   Correct syntax is SET TRANSACTION ISOLATION LEVEL [level]; The given statement swaps 'LEVEL' and 'ISOLATION', causing a syntax error.

2. Final Answer:

   The order of 'LEVEL' and 'ISOLATION' is incorrect. -> Option D

3. Quick Check:

   Syntax order matters = The order of 'LEVEL' and 'ISOLATION' is incorrect. [OK]

Hint: Remember correct keyword order: ISOLATION LEVEL [OK]

Common Mistakes:

• Swapping 'LEVEL' and 'ISOLATION' keywords
• Assuming 'READ COMMITTED' is invalid
• Missing semicolon causing error

5. A bank application uses SERIALIZABLE isolation level to prevent anomalies. However, users report frequent transaction failures due to serialization errors. What is the best approach to reduce these errors while maintaining data accuracy?

hard

A. Keep SERIALIZABLE and retry failed transactions automatically

B. Disable transactions to improve performance

C. Switch to READ UNCOMMITTED to avoid errors

D. Lower isolation level to READ COMMITTED and add explicit locking where needed

Solution

1. Step 1: Understand SERIALIZABLE issues and evaluate best practice

   SERIALIZABLE prevents anomalies but causes serialization errors on conflicts. Lowering isolation risks anomalies; best is to keep SERIALIZABLE and retry failed transactions automatically.

2. Final Answer:

   Keep SERIALIZABLE and retry failed transactions automatically -> Option A

3. Quick Check:

   Retry on serialization errors = Keep SERIALIZABLE and retry failed transactions automatically [OK]

Hint: Retry failed SERIALIZABLE transactions to keep accuracy [OK]

Common Mistakes:

• Lowering isolation level without safeguards
• Ignoring serialization errors
• Disabling transactions for performance