ACID properties, transactions and concurrency
50 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A transfer debits one account but never credits the other, or two customers book the same last seat. Interviewers use stories like these to see whether you can name the ACID property or the anomaly involved, and the fix.
ACID with a bank-transfer example
A transaction is a group of database operations that must succeed or fail as one unit, such as the debit and the credit of a money transfer. ACID names its four guarantees: atomicity, consistency, isolation and durability.
A transfer of Rs 500 from Asha to Bhavna is a debit and a credit. The program rolls back every transfer that fails halfway, so the total money never changes.
Example 1 · Atomicity: a transfer that rolls back on failure
balances = {"Asha": 2000, "Bhavna": 500}
def transfer(source, target, amount, crash_midway=False): undo_log = [] # (account, old value) pairs
def write(account, new_value): undo_log.append((account, balances[account])) # remember how to undo balances[account] = new_value
try: write(source, balances[source] - amount) if crash_midway: raise RuntimeError("server crashed after debit") if target not in balances: raise ValueError(f"no such account {target}") write(target, balances[target] + amount) print("COMMIT ", balances) except (RuntimeError, ValueError) as error: for account, old in reversed(undo_log): # undo in reverse order balances[account] = old print("ROLLBACK", balances, f"({error})")
transfer("Asha", "Bhavna", 500)transfer("Asha", "Bhavna", 300, crash_midway=True)transfer("Asha", "Chetan", 100)print("Total money:", sum(balances.values()))COMMIT {'Asha': 1500, 'Bhavna': 1000}
ROLLBACK {'Asha': 1500, 'Bhavna': 1000} (server crashed after debit)
ROLLBACK {'Asha': 1500, 'Bhavna': 1000} (no such account Chetan)
Total money: 2500What this code does
- Every write records its old value in an undo log. On any failure the log is replayed in reverse, so a half-done transfer leaves no trace: atomicity.
- The total stays Rs 2500 after all three attempts, the consistency rule. A real DBMS also writes a redo log to disk before COMMIT returns, which gives durability.
Each ACID property protects the transfer from a different kind of failure:
- Atomicity: all or nothing. If the server crashes after the debit, the debit is undone (rollback), using the undo log.
- Consistency: the transaction moves the database from one valid state to another; rules such as "balance ≥ 0" and "total money unchanged" hold before and after.
- Isolation: concurrent transactions do not see each other's half-done work; the result is as if they ran one after another.
- Durability: once committed, the change survives crashes and power cuts, because it is in the redo log (write-ahead log) on disk. Revise: ACID and a bank transfer (opens in a new tab).
Predict the output
Which ACID property?
Read the situation, then pick the property you would name aloud.
Round 1 of 3
Which property undoes the debit?
Transaction states, commit and rollback
COMMIT ends a transaction and makes its changes permanent and visible to others. ROLLBACK undoes every change since the transaction started, and a SAVEPOINT lets you undo only part of it.
A transaction moves through states from active to committed, or to failed and aborted. The SQL commits one transfer and uses a savepoint to undo part of another.
active --(last statement)--> partially committed --(log on disk)--> committed | | +----(error / abort)----> failed <-+ | (undo all) --> abortedExample 2 · COMMIT, ROLLBACK and SAVEPOINT in MySQL
Runs in: MySQL 8
CREATE TABLE account (name VARCHAR(20) PRIMARY KEY, balance INT CHECK (balance >= 0));INSERT INTO account VALUES ('Asha', 2000), ('Bhavna', 500);
START TRANSACTION;UPDATE account SET balance = balance - 500 WHERE name = 'Asha';UPDATE account SET balance = balance + 500 WHERE name = 'Bhavna';COMMIT;
START TRANSACTION;UPDATE account SET balance = balance - 100 WHERE name = 'Asha';SAVEPOINT after_debit;UPDATE account SET balance = balance + 9999 WHERE name = 'Bhavna';ROLLBACK TO SAVEPOINT after_debit;ROLLBACK;
SELECT name, balance FROM account ORDER BY name+--------+---------+ | name | balance | +--------+---------+ | Asha | 1500 | | Bhavna | 1000 | +--------+---------+
What this code does
- The first transaction commits, so Rs 500 has moved. In the second,
ROLLBACK TO SAVEPOINTundoes only the wrong credit, and the finalROLLBACKundoes the debit too. - The final balances reflect only the committed transfer. In a client with autocommit on, every statement outside
START TRANSACTIONcommits by itself.
The states: active (running its statements), partially committed (last statement done, changes not yet durable), committed (changes made permanent), failed (an error or abort happened) and aborted (all changes rolled back; it may be restarted or killed). With autocommit on, every statement outside START TRANSACTION commits by itself. Revise: Start, commit and rollback (opens in a new tab).
Order of execution
The path of a good transaction
Tap the steps in the order a successful transaction goes through them.
Dirty read, non-repeatable read and phantom read
A dirty read happens when one transaction reads data that another transaction has written but not yet committed. If the writer rolls back, the reader has used data that never existed.
A seat is shown as sold out because of a booking that is cancelled a moment later. The picture follows that timeline, and the example shows all three read anomalies.
Example 3 · Three read anomalies as timelines
Dirty read T1: UPDATE seats = 0 WHERE train = 12627 (not committed) T2: SELECT seats -> 0 -> shows "sold out" T1: ROLLBACK (seats back to 1)Non-repeatable read T1: SELECT fee FROM course WHERE id = 7 -> 50000 T2: UPDATE course SET fee = 55000 WHERE id = 7; COMMIT T1: SELECT fee FROM course WHERE id = 7 -> 55000Phantom read T1: SELECT COUNT(*) FROM application WHERE drive = 101 -> 40 T2: INSERT INTO application (... drive 101 ...); COMMIT T1: SELECT COUNT(*) FROM application WHERE drive = 101 -> 41Dirty read: T2 used a value that was never committed. Non-repeatable read: same row, two different values inside one transaction. Phantom read: same condition, a different set of rows. Lowest level that prevents each: dirty -> READ COMMITTED, non-repeatable -> REPEATABLE READ, phantom -> SERIALIZABLE (in the SQL standard)
What this code does
- A non-repeatable read is about an existing row changing; a phantom is about rows appearing or disappearing for a condition.
- Which anomalies a given database really shows also depends on its implementation; InnoDB at REPEATABLE READ avoids most phantoms.
When isolation is weakened for speed, interviewers expect a one-line definition and a tiny timeline for each anomaly:
- Dirty read: T2 reads a value T1 wrote but has not committed; T1 then rolls back, so T2 used data that never existed.
- Non-repeatable read: T1 reads a row, T2 updates and commits it, and T1 reads the same row again and gets a different value.
- Phantom read: T1 runs a query with a condition, T2 inserts (or deletes) a matching row and commits, and T1's repeated query returns a different set of rows.
- Lost update: two transactions read the same value and both write, so one write overwrites the other. Revise: Concurrency anomalies (opens in a new tab).
Find the bug
Find the wrong timeline line
Each story has one line that does not match its anomaly. Tap it.
A non-repeatable read of a course fee.
Round 1 of 3Isolation levels and serializability at a high level
An isolation level is a setting that decides how much one transaction can see of other transactions running at the same time. Higher levels prevent more anomalies but allow less concurrency.
SQL defines four isolation levels, from READ UNCOMMITTED to SERIALIZABLE. The program tests whether a schedule is conflict-serializable with a precedence graph.
Level Dirty Non-repeatable PhantomREAD UNCOMMITTED yes yes yesREAD COMMITTED no yes yesREPEATABLE READ no no yes (standard)SERIALIZABLE no no noExample 4 · Conflict serializability with a precedence graph
from itertools import combinations
def precedence_edges(schedule): edges = set() for (tx_a, kind_a, item_a), (tx_b, kind_b, item_b) in combinations(schedule, 2): # Different transactions, same item, at least one write: a comes before b. if tx_a != tx_b and item_a == item_b and "W" in (kind_a, kind_b): edges.add((tx_a, tx_b)) return edges
def has_cycle(edges): graph = {} for a, b in edges: graph.setdefault(a, []).append(b) state = {} # missing = new, 1 = on path, 2 = done
def dfs(node): state[node] = 1 for nxt in graph.get(node, []): if state.get(nxt) == 1 or (nxt not in state and dfs(nxt)): return True state[node] = 2 return False
return any(node not in state and dfs(node) for node in list(graph))
def analyse(name, schedule): edges = precedence_edges(schedule) verdict = "NOT conflict-serializable" if has_cycle(edges) else "conflict-serializable" print(f"{name}: edges {[f'T{a}->T{b}' for a, b in sorted(edges)]} -> {verdict}")
analyse("S1", [(1, "R", "A"), (1, "W", "A"), (2, "R", "A"), (2, "W", "A"), (1, "R", "B"), (2, "R", "B")])analyse("S2", [(1, "R", "A"), (2, "R", "A"), (1, "W", "A"), (2, "W", "A")])S1: edges ['T1->T2'] -> conflict-serializable S2: edges ['T1->T2', 'T2->T1'] -> NOT conflict-serializable
What this code does
- S1 has only T1 → T2 edges, so it is equivalent to running T1 then T2. S2 is the lost-update pattern: both read A, then both write it, giving T1 → T2 and T2 → T1, a cycle.
- Two reads never conflict, which is why R1(B) and R2(B) add no edge.
Each level prevents more than the one below: READ COMMITTED stops dirty reads, REPEATABLE READ also stops non-repeatable reads, and SERIALIZABLE stops phantoms too. MySQL InnoDB defaults to REPEATABLE READ; PostgreSQL, Oracle and SQL Server default to READ COMMITTED. Revise: Isolation levels (opens in a new tab).
A schedule is serializable if its result equals some one-after-another order of the same transactions. For the conflict test, two operations conflict when they come from different transactions, touch the same item and at least one is a write. Draw an edge Ti → Tj for each conflict where Ti comes first: the schedule is conflict-serializable exactly when the graph has no cycle.
Predict the output
Levels and schedules
Work out the answer before you pick.
Round 1 of 2
Which is the lowest level that prevents non-repeatable reads?
Locks and deadlock in databases
2PL (two-phase locking) means a transaction acquires all its locks before releasing any: a growing phase, then a shrinking phase. It guarantees conflict serializability; strict 2PL holds write locks until commit.
Databases use locks to isolate transactions, and locks bring deadlock back. The example shows two sessions locking rows in opposite order and one of them chosen as the victim.
Example 5 · A database deadlock and its victim
Session 1 Session 2START TRANSACTION; START TRANSACTION;UPDATE account SET balance = balance - 100 WHERE name = 'Asha'; -- X lock on Asha UPDATE account SET balance = balance - 50 WHERE name = 'Bhavna'; -- X lock on BhavnaUPDATE account SET balance = balance + 100 WHERE name = 'Bhavna'; -- waits for S2 UPDATE account SET balance = balance + 50 WHERE name = 'Asha'; -- waits for S1: cycleSession 2 receives: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Session 2's transaction is rolled back; Session 1's UPDATE now proceeds. Fix: lock rows in a fixed order (for example by account name or id) and retry on 1213.
What this code does
- Each session holds an exclusive row lock the other needs: hold and wait plus circular wait, exactly the OS deadlock conditions.
- InnoDB detects the cycle and picks a victim (usually the transaction that has done less work); which session is chosen can differ between runs.
A shared lock (S) is for reading and many transactions can hold it together; an exclusive lock (X) is for writing and only one can hold it. T1 locks row A and waits for B while T2 locks B and waits for A: a cycle. InnoDB detects it and rolls back one transaction as the victim with an error, so the application should catch it and retry. Revise: Deadlocks (opens in a new tab).
Many systems also use MVCC (multi-version concurrency control): readers see a snapshot and do not block writers. To avoid deadlocks, lock rows in a fixed order and keep transactions short.
Order of execution
How a deadlock forms
Tap the events in the order they happen.
Next step
Check your understanding
12 quick questions