SQL 9 : Transactions & Isolation Levels

D

Qubits of DPK

April 11, 2026

Core DBMS

1. Recap of SQL 8

Previously we learned indexes, B+ Trees, composite indexes, clustered vs non-clustered indexes, the left prefix rule, and when indexes help or hurt performance. Indexes help read performance. Now we move to something that ensures data correctness — and that concept is called Transactions.

2. What is a Transaction?

A transaction is a group of SQL operations executed as one single unit of work. Either all operations succeed, or none of them succeed.

3. Real-Life Example

Imagine transferring money in a banking system.
sql
QUBITS OF DPK
1Step 1 → Deduct ₹1000 from Account A
2Step 2 → Add ₹1000 to Account B
If the system crashes after Step 1, Account A loses money but Account B never receives it. Money disappears. A transaction prevents this problem.
sql
QUBITS OF DPK
1BEGIN;
2
3UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
4UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
5
6COMMIT;
If any step fails:
sql
QUBITS OF DPK
1ROLLBACK;

4. Transaction Lifecycle

A transaction moves through the following states:
sql
QUBITS OF DPK
1BEGIN → ACTIVE → PARTIALLY COMMITTED → COMMITTED
If something fails:
sql
QUBITS OF DPK
1ACTIVE → FAILED → ROLLED BACK

5. Why Transactions Are Important

Transactions solve three major problems.
Partial updates — without transactions, if Step 1 succeeds and Step 2 fails, the database becomes inconsistent.
Concurrent access — multiple users may modify data simultaneously (e.g. two users withdrawing money at the same time), and without transactions the balance can become incorrect.
System crashes — if the server crashes mid-execution, transactions ensure either a complete change or a full rollback.

6. ACID Properties

Every database transaction follows ACID properties:
sql
QUBITS OF DPK
1A → Atomicity
2C → Consistency
3I → Isolation
4D → Durability

7. Atomicity

All operations succeed, or none succeed. In a bank transfer, both the debit and the credit must succeed. If one fails, everything rolls back.

8. Consistency

The database always moves from one valid state to another. If a constraint says account balances cannot be negative, any transaction that would violate that constraint will fail and be rolled back.

9. Isolation

Transactions do not interfere with each other. When two users update the same account simultaneously, the database controls what each transaction can see. This leads to Isolation Levels, covered below.

10. Durability

Once a transaction is committed, data is permanently stored — even if the system crashes immediately after. This is achieved using Write Ahead Logs (WAL), where the database writes logs before updating data.

11. COMMIT and ROLLBACK

COMMIT makes all changes permanent. After a commit, changes cannot be undone.
sql
QUBITS OF DPK
1COMMIT;
ROLLBACK cancels all changes in a transaction and returns the database to its previous state.
sql
QUBITS OF DPK
1ROLLBACK;
Example:
sql
QUBITS OF DPK
1BEGIN;
2
3UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
4
5ROLLBACK;
6-- Balance remains unchanged

12. Transaction Isolation Levels

When multiple transactions run simultaneously, problems may occur. Databases define four isolation levels:
sql
QUBITS OF DPK
11 → Read Uncommitted
22 → Read Committed
33 → Repeatable Read
44 → Serializable
Higher isolation = safer but slower.

13. Concurrency Problems

Dirty Read — reading uncommitted data. T1 updates balance to 500, T2 reads 500, then T1 rolls back. T2 read invalid data.
Non-Repeatable Read — reading the same row twice gives different values. T1 reads salary as 5000, T2 updates it to 6000, T1 reads again and gets 6000.
Phantom Read — new rows appear during a transaction. T1 queries employees with salary > 5000, T2 inserts a new employee with salary 6000, T1 queries again and sees an extra row.
Lost Update — two transactions overwrite each other. Both T1 and T2 read balance as 1000, T1 writes 900, T2 writes 800 — T1's update is lost.

14. Isolation Level 1 — Read Uncommitted

The lowest isolation level. Transactions can read uncommitted changes from other transactions. Allows dirty reads, non-repeatable reads, and phantom reads. Rarely used in production.

15. Isolation Level 2 — Read Committed

Transactions can only read committed data. Prevents dirty reads, but still allows non-repeatable reads and phantom reads. Default in Oracle and PostgreSQL.

16. Isolation Level 3 — Repeatable Read

Guarantees that rows read within a transaction cannot change. Prevents dirty reads and non-repeatable reads, but still allows phantom reads. Default in MySQL InnoDB.

17. Isolation Level 4 — Serializable

The highest isolation level. Transactions behave as if executed sequentially. Prevents all three problems — dirty reads, non-repeatable reads, and phantom reads — but is slow due to heavy locking.

18. Isolation Level Summary

19. Setting Isolation Level

sql
QUBITS OF DPK
1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Full example:
sql
QUBITS OF DPK
1SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2
3BEGIN;
4SELECT * FROM accounts;
5COMMIT;

20. Real Production Example — E-commerce Order Placement

When a customer places an order, these steps must happen in one transaction:
sql
QUBITS OF DPK
11. Check inventory
22. Reserve product
33. Create order
44. Deduct stock
Without a transaction, multiple customers could buy the same item simultaneously, causing overselling.

21. Interview Questions

Why are transactions important? They ensure data integrity, concurrency safety, and crash recovery.
What does ACID stand for? Atomicity, Consistency, Isolation, Durability.
Which isolation level does MySQL use by default? Repeatable Read.

22. Quick Revision

sql
QUBITS OF DPK
1Transaction   → group of SQL operations executed as one unit
2
3ACID:
4  Atomicity   → all or nothing
5  Consistency → valid state to valid state
6  Isolation   → transactions don't interfere
7  Durability  → committed data survives crashes
8
9Isolation Levels (low → high):
10  Read Uncommitted → Read Committed → Repeatable Read → Serializable