Transactions & Concurrency Language

ACID property vocabulary with real-world mapping:

Option A is the only response that correctly maps all four ACID properties to their database-level guarantees.

Property	Guarantee	Bank transfer example
Atomicity	All operations in the transaction succeed or all are rolled back	Debit and credit are a single unit — no partial transfer
Consistency	The transaction moves the database from one valid state to another, honouring all constraints	Total balance is conserved; no constraint (e.g., balance >= 0) is violated
Isolation	Concurrent transactions appear to execute serially — intermediate states are not visible to others	A concurrent balance read does not see the "mid-transfer" state where A is debited but B is not yet credited
Durability	Committed data persists even after a crash (guaranteed by WAL)	The committed transfer record survives a power failure

Isolation level anomaly vocabulary:

Option D is correct. A phantom read occurs when a transaction re-executes a range query and finds rows that were inserted (or deleted) by another committed transaction since the first execution.

Anomaly	Description	Prevented from
Dirty read	Reads uncommitted data from another transaction	READ COMMITTED and above
Non-repeatable read	Same row read twice returns different values	REPEATABLE READ and above
Phantom read	Same range query returns different rows	SERIALIZABLE only
Serialization anomaly	Result is not achievable by any serial execution order	SERIALIZABLE only

Isolation level comparison:

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Cost caveat to raise in architecture reviews: SERIALIZABLE has the highest overhead — transactions may fail with serialization errors and require retry logic. Use it where correctness requires it (e.g., financial ledgers, seat reservation), but it is overkill for most CRUD workloads.

Optimistic vs. pessimistic locking vocabulary:

Option B is the correct description of optimistic locking. It is called "optimistic" because it assumes conflicts are rare — it does not hold any lock during the think-time between read and write, but checks for conflict only at commit time.

Approach	Lock held during user think-time?	Conflict detection	Best for
Optimistic locking	No	Version/timestamp check at update time	Low conflict rate; long user sessions
Pessimistic locking	Yes (SELECT FOR UPDATE)	Blocked until lock released	High conflict rate; short critical sections

Optimistic locking implementation vocabulary:

-- Read phase: capture version
SELECT id, name, version FROM products WHERE id = 42;
-- version = 7

-- Write phase: conditional update
UPDATE products SET name = 'New Name', version = 8
WHERE id = 42 AND version = 7;
-- If 0 rows updated → conflict; someone else changed it first

Key vocabulary:

• Version column — an integer incremented on every UPDATE; the "optimistic lock token"
• Stale write / lost update — the conflict that optimistic locking prevents: overwriting another writer's change
• Conflict retry — application logic that re-reads and re-attempts the update when a conflict is detected

Dirty read vocabulary:

Option C is the correct definition. A dirty read is the most dangerous read anomaly because the data you read may never have been "real" from the perspective of committed database state.

Dirty read scenario:

-- Transaction A begins a transfer:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- balance now 0

-- Transaction B (READ UNCOMMITTED) reads before A commits:
SELECT balance FROM accounts WHERE id = 1; -- sees 0 ← dirty read!

-- Transaction A is rolled back (e.g., network error):
ROLLBACK; -- balance restored to 500

-- Transaction B made a decision based on balance = 0 that never committed

Term	Definition
Dirty read	Reading uncommitted data from another in-progress transaction
Uncommitted data	Data written by a transaction that has not yet executed COMMIT
Rollback	Undoing all changes of a transaction — makes dirty reads retroactively invalid

Why READ UNCOMMITTED is rarely used: The isolation benefit is trivial in PostgreSQL (it behaves identically to READ COMMITTED in PG's MVCC implementation) and in most other databases it creates serious correctness risks — applications can make decisions based on data that was never durable. Legitimate use cases are extremely limited (e.g., approximate reporting where slightly stale data is fully acceptable and throughput is critical).

2PL vs. 2PC vocabulary:

Option B correctly distinguishes the two protocols. They share "two-phase" in their name but operate at entirely different levels of the database system.

Protocol	Domain	Two phases	Problem it solves
2PL (Two-Phase Locking)	Single node; concurrency control	Growing phase (acquire locks) + Shrinking phase (release locks)	Serializability — prevents anomalies from interleaved concurrent transactions
2PC (Two-Phase Commit)	Distributed systems; agreement	Prepare phase (vote) + Commit/Abort phase (finalize)	Atomicity across distributed nodes — ensures all-or-nothing outcome across participants

2PL key vocabulary:

• Growing phase — a transaction may only acquire locks; cannot release any
• Shrinking phase — a transaction releases locks; cannot acquire new ones
• Strict 2PL — holds all locks until COMMIT/ROLLBACK (standard in most RDBMS)

2PC key vocabulary:

• Coordinator — the node that orchestrates the distributed commit
• Participant — a node that votes and applies the coordinator's decision
• Prepare — participant acknowledges it can commit (durably logs the transaction)
• In-doubt transaction — a transaction stuck in prepared state because the coordinator crashed before sending the final decision