In PostgreSQL, UPDATE does not overwrite disk rows in place; it inserts a new tuple with updated values and marks the old tuple with an expiration transaction ID (xmax). Read transactions see a consistent snapshot based on their transaction snapshot without acquiring read locks. Writers never block readers, and readers never block writers.
-- Inspecting PostgreSQL MVCC Tuple Headers -- Every row contains hidden system attributes: xmin (creating transaction ID) and xmax (deleting/updating transaction ID) SELECT xmin, xmax, ctid, -- Physical disk location (page number, tuple index) account_id, balance FROM accounts WHERE account_id = 'alice-123'; -- Concurrent Update with Row-Level Locking (Pessimistic Concurrency) -- SELECT ... FOR UPDATE acquires an Exclusive Row Lock (X-Lock), forcing concurrent transactions to wait BEGIN; SELECT balance FROM accounts WHERE account_id = 'alice-123' FOR UPDATE; UPDATE accounts SET balance = balance - 100 WHERE account_id = 'alice-123'; COMMIT;
Visual representation of control loops, memory layout, and execution flow for Concurrency Control, Serializability & Isolation Levels.
1. Dirty Read: T1 modifies row X without committing; T2 reads X; T1 aborts. T2 operated on "dirty" rolled-back data. 2. Non-Repeatable (Fuzzy) Read: T1 reads row X; T2 modifies/deletes row X and commits; T1 re-reads row X and sees different values. 3. Phantom Read: T1 executes a range query (e.g., WHERE age > 21); T2 inserts a NEW matching row and commits; T1 repeats range query and sees new "phantom" rows. 4. Lost Update: T1 and T2 read row X concurrently; both calculate updates and write; T2 overwrites T1 update without incorporating it. 5. Write Skew: In Snapshot Isolation, two transactions read overlapping data, verify a constraint (e.g. at least 1 doctor on call), and write disjoint rows simultaneously, violating the global constraint.
Conflicting Operations: Two operations conflict if they belong to different transactions, access the SAME data item, and at least ONE operation is a WRITE (Read-Write, Write-Read, Write-Write). Conflict Serializability: A schedule S is conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting adjacent operations. Precedence Graph Test: Construct a directed graph where Nodes = Transactions. Draw directed edge Ti -> Tj if an operation in Ti conflicts with and executes before an operation in Tj. Schedule is Conflict Serializable IF AND ONLY IF the graph has NO CYCLES (Acyclic). Topological sort yields equivalent serial order!
Lock Types: Shared Lock (S-Lock: multiple readers allowed) and Exclusive Lock (X-Lock: single writer, no readers). Two-Phase Locking (2PL) Rule: A transaction cannot acquire ANY new lock once it releases its first lock. - Phase 1 (Growing Phase): Transaction may acquire locks, but release none. - Phase 2 (Shrinking Phase): Transaction may release locks, but acquire no new locks. 2PL GUARANTEES Conflict Serializability! However, basic 2PL can suffer from Cascading Aborts and Deadlocks. Strict 2PL: Holds all Exclusive (X) locks until COMMIT/ABORT (Prevents Cascading Aborts). Rigorous 2PL: Holds ALL Shared and Exclusive locks until COMMIT/ABORT (Guarantees Strict Serializability).
Deadlock Detection: Engine maintains a Wait-For Graph (WFG); background thread checks for cycles and aborts a victim transaction. Deadlock Prevention (Timestamp based where older = lower timestamp number): - Wait-Die (Non-preemptive): If old requests lock held by young -> Old WAITS. If young requests lock held by old -> Young DIES (aborts). - Wound-Wait (Preemptive): If old requests lock held by young -> Old WOUNDS/preempts young (young aborts). If young requests lock held by old -> Young WAITS. MVCC Mechanics: Writers create a new version of the row with its transaction ID (xmin). Readers read the latest version committed prior to reader snapshot timestamp. Result: Readers never block writers, and writers never block readers.
| Feature / Dimension | Pessimistic Concurrency (2PL) | Optimistic Concurrency / MVCC |
|---|---|---|
| Core Philosophy | Assume conflicts will happen; acquire Shared/Exclusive locks before accessing data | Assume conflicts are rare; allow concurrent reads/writes on row versions; validate on commit |
| Reader-Writer Interaction | Exclusive write locks block readers; shared read locks block writers | Readers never block writers; writers never block readers (each reads snapshot versions) |
| Best Suited Workload | High-contention write workloads where transaction aborts/retries are expensive | Read-heavy workloads, analytics, web applications with distributed clients |
Detailed answers, interviewer pro tips, key takeaway summaries, and code examples formulated for technical rounds.
✅ Correction: Two-Phase Locking (2PL) guarantees Conflict Serializability, but basic 2PL DOES NOT prevent deadlocks! Transactions can still enter cyclic wait states requiring detection or prevention algorithms.
✅ Correction: In standard ANSI SQL, Repeatable Read locks individual rows, preventing Non-Repeatable Reads, but allows Phantom Reads (new rows matching range predicate). Serializable isolation (or Snapshot Isolation with Predicate Locks) is required to prevent Phantoms.
Mathematical correctness and locking protocols for concurrent transaction execution.