Lecture 12: Transactions & ACID Properties
The ACID Guarantees, Transaction Syntax, Concurrency Anomalies, and the 4 ANSI SQL Isolation Levels
Lecture Source Slide Deck
Original Slides: Lecture 12 (51 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture12.pdf
1. What is a Transaction?
A Transaction is an atomic sequence of database operations (reads and writes) that represents a single logical unit of work.
BEGIN TRANSACTION
|
[ Operation 1 ] (Debit Account A)
|
[ Operation 2 ] (Credit Account B)
|
Check Constraints
/ \
Success Failure
/ \
COMMIT ROLLBACK2. The ACID Guarantees
| Property | Meaning | Subsystem Responsible |
|---|---|---|
| Atomicity | "All or Nothing": Either every statement in the transaction executes successfully, or the entire transaction is aborted and rolled back with zero disk trace. | Recovery Management & Write-Ahead Log (WAL) |
| Consistency | The transaction must transition the database from one valid integrity state to another valid state, preserving all primary keys, foreign keys, and CHECK rules. | Application Logic & Schema Constraints |
| Isolation | Intermediate states of a transaction are completely hidden from other concurrently executing transactions until commit. | Concurrency Control (Locking / 2PL / MVCC) |
| Durability | Once a transaction successfully commits, its changes survive permanently on non-volatile storage, even if the database server crashes or loses power a millisecond later. | Write-Ahead Log (WAL) & Disk Flush |
3. SQL Transaction Control Syntax
BEGIN TRANSACTION;
-- Transfer $500 from Account 101 to Account 202
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
-- If balance drops below zero, abort
-- Otherwise:
COMMIT;Partial Rollbacks with SAVEPOINT
BEGIN;
INSERT INTO orders VALUES (101, 'Alice');
SAVEPOINT item_insert;
INSERT INTO order_items VALUES (101, 'Laptop', 1200);
-- If stock check fails:
ROLLBACK TO SAVEPOINT item_insert;
COMMIT;4. The Three Concurrency Anomalies
When transactions execute concurrently without adequate isolation, three distinct data corruption anomalies can emerge:
1. Dirty Read (G1)
- Scenario: Transaction T1 updates a row without committing. Transaction T2 reads that updated value. T1 subsequently aborts and issues a
ROLLBACK. - Consequence: T2 made decisions based on phantom data that mathematically never existed!
2. Non-Repeatable Read (G2a / Fuzzy Read)
- Scenario: Transaction T1 reads a row. Transaction T2 modifies or deletes that row and commits. Transaction T1 reads the exact same row again.
- Consequence: T1 observes contradictory values for the same row within a single transaction!
3. Phantom Read (G2b)
- Scenario: Transaction T1 queries a range of rows (e.g.,
WHERE salary > 80000) and finds 3 matching employees. Transaction T2 inserts a new employee earning 90,000 and commits. T1 re-executes the exact same range query. - Consequence: A new "phantom" row appears that was not present in the first read!
5. The Four ANSI SQL Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Typical Engine Mechanism |
|---|---|---|---|---|
READ UNCOMMITTED | Allowed | Allowed | Allowed | Read without locks; fastest, dangerous. |
READ COMMITTED (Default in Postgres) | Prevented | Allowed | Allowed | Reads see only committed snapshots at query start. |
REPEATABLE READ | Prevented | Prevented | Allowed (Prevented in PG MVCC) | Reads see snapshot taken at transaction start. |
SERIALIZABLE | Prevented | Prevented | Prevented | Strict SSI (Serializable Snapshot Isolation) or 2PL. |
-- Setting isolation level in SQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;6. Topic Practice Questions
Answer:
Atomicity guarantees that the debit from Account A and the credit to Account B succeed together or neither occurs. When power is restored, the database crash recovery subsystem reads the Write-Ahead Log (WAL) and automatically undoes (rolls back) the incomplete debit transaction.
Answer:
In MVCC, when an update occurs, the engine does not overwrite the data in place; it writes a new version of the row with transaction timestamps (xmin, xmax). Readers only see versions of rows whose creating transaction had already committed prior to the reader's transaction/statement start snapshot, meaning readers never block writers and writers never block readers.
Answer:
A deadlock occurs when Transaction T1 holds Lock A and waits for Lock B, while concurrent Transaction T2 holds Lock B and waits for Lock A. The DBMS maintains a background Wait-For Graph (or lock timeout timer). If a cycle is detected in the graph, the engine aborts and rolls back one of the transactions (the "victim"), allowing the other to proceed.
Lecture 11: Views, Functions & Database Security
Updatable Views, WITH CHECK OPTION, Materialized Views, Stored Logic, and RBAC (GRANT / REVOKE)
LeetCode SQL Top 50 & Classic Problems
Complete collection of essential LeetCode SQL interview problems across Easy, Medium, and Hard with full solutions and explanations