ACID Transactions, Concurrency Control & MVCC

🛡️ ACID Properties & Underlying Engine Mechanics

A transaction is a single logical unit of database work containing one or more DML statements. An RDBMS engine guarantees transactional reliability by strictly adhering to ACID properties:

• Analogy: Bank Vault Wire Transfers with Dual-Custody Locks.
  • Atomicity: Either the full $1,000 is debited from Account A and credited to Account B, or the wire transfer fails completely and no money moves.
  • Consistency: After the transfer, the sum of balances ($A + B$) remains strictly balanced and satisfies schema constraints.
  • Isolation: Two simultaneous wire transfers involving Account A process in isolation without seeing intermediate partial ledger amounts.
  • Durability: Once the receipt is printed (COMMIT), the wire log is written into non-volatile storage and survives power outages.

                               ACID Engine Implementation
                                           │
         ┌───────────────────┬─────────────┴─────────────┬───────────────────┐
         ▼                   ▼                           ▼                   ▼
     Atomicity          Consistency                  Isolation           Durability
   Undo Log / MVCC    Schema Constraints          Locks / Read Views    WAL / Redo Log
   All-or-Nothing     Invariants Preserved        Concurrent Isolation  Survives Crashes

🔄 Multi-Version Concurrency Control (MVCC)

Modern databases implement Multi-Version Concurrency Control (MVCC) so that "Readers never block Writers, and Writers never block Readers."

Instead of acquiring expensive shared read locks on table rows during SELECT queries, the database engine maintains multiple timestamped versions of tuple rows concurrently:

                            MVCC Tuple Versioning (PostgreSQL)
                                            │
        ┌───────────────────────────────────┴───────────────────────────────────┐
        ▼                                                                       ▼
Tuple Version 1 (Original)                               Tuple Version 2 (Updated)
xmin: Tx 101 (Created)                                   xmin: Tx 105 (Created)
xmax: Tx 105 (Superseded by update)                      xmax: 0 (Active live row)

• PostgreSQL Tuple Headers: Every row contains hidden metadata fields (xmin = creating transaction ID, xmax = deleting/updating transaction ID). A transaction only sees tuples where xmin is committed and xmax is uncommitted or higher than its transaction snapshot.
• MySQL InnoDB Undo Logs: Updates copy original tuple versions into an Undo Log Segment, constructing an in-memory linked-list snapshot read view for concurrent readers.

⚡ Concurrency Anomalies Deep Dive

When transactions execute concurrently without total isolation, five classic concurrency anomalies can corrupt data:

                                Concurrency Anomalies
                                          │
    ┌──────────────┬──────────────┬───────┴──────┬──────────────┬──────────────┐
    ▼              ▼              ▼              ▼              ▼              ▼
Dirty Read   Non-Repeatable  Phantom Read   Lost Update     Write Skew   Serialization
              Read (Fuzzy)                                               Failure

1. Dirty Read: Tx A updates a row without committing. Tx B reads the modified uncommitted row. Tx A issues a ROLLBACK. Tx B has read "phantom data" that never officially existed.
2. Non-Repeatable Read (Fuzzy Read): Tx A reads a row value ($100$). Tx B updates the row to $200$ and commits. Tx A re-reads the row and sees a different value ($200$).
3. Phantom Read: Tx A executes a range query (WHERE salary > 50000) returning 10 rows. Tx B inserts a new row matching the condition and commits. Tx A re-runs the range query and sees 11 rows (a new "phantom" record appeared).
4. Lost Update: Tx A and Tx B read the same row balance ($100$) concurrently. Both calculate updates ($100 - 20 = 80$ and $100 - 30 = 70$). Tx A writes $80$, and Tx B writes $70$, overwriting and losing Tx A's deduction!
5. Write Skew: Tx A and Tx B read overlapping data bound by a domain invariant (e.g., "Combined balance of Account A + B must remain $\ge 0$"). Tx A deducts from A, and Tx B deducts from B simultaneously. Individually both pass checks, but together they violate the invariant.

🔒 Transaction Isolation Levels Comparison

ANSI/ISO SQL defines four isolation levels offering tradeoffs between throughput performance and data safety:

-- Setting explicit transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
  UPDATE inventory SET stock = stock - 1 WHERE item_id = 42;
COMMIT;

🔐 Locking Hierarchy, Strategies & Deadlocks

1. Lock Granularity & Lock Modes

The Lock Manager enforces hierarchical lock levels:

                            Lock Granularity Hierarchy
                                        │
                    Database Lock ──► Table Lock ──► Page Lock ──► Row Lock

• Shared Lock (S): Acquired for read operations. Multiple transactions can hold Shared locks concurrently.
• Exclusive Lock (X): Acquired for write operations (UPDATE/DELETE). Only one transaction can hold an Exclusive lock on a target resource.
• Intent Locks (IS / IX): Acquired at higher table levels to indicate that fine-grained row locks are held below, preventing full-table lock escalation conflicts.

2. Explicit Pessimistic Locking

Prevents race conditions by locking selected rows during initial read queries:

BEGIN TRANSACTION;
-- Acquires an Exclusive Row Lock (X), blocking concurrent transactions
SELECT stock FROM inventory WHERE item_id = 99 FOR UPDATE;

-- Safely update inventory knowing no competitor can modify item 99
UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;
COMMIT;

3. Deadlocks & Detection

A Deadlock occurs when Tx A holds Lock 1 and requests Lock 2, while Tx B holds Lock 2 and requests Lock 1 (a circular wait-for dependency):

                        Deadlock Circular Dependency
                                      │
               Tx A (Holds Lock 1) ──► Waits for Lock 2 (Held by Tx B)
                       ▲                               │
                       └──────── Waits for Lock 1 ─────┘

The database background Deadlock Detector thread periodically evaluates the dependency wait-for graph, detects cycles, and aborts one transaction (the Deadlock Victim) with error 40P01 so the other can proceed.

❓ Conceptual Quizzes

💻 Practice Problems