Transaction Management & Concurrency Control

TAKEAWAYS:

• Transactions guarantee ACID properties (Atomicity, Consistency, Isolation, Durability) to maintain data integrity.
• Concurrency control prevents anomalies like lost updates, dirty reads, and cascading aborts in multi-user systems.
• Locking (e.g., two-phase locking) and timestamp-based methods manage concurrent access to shared data.
• Deadlocks occur when transactions wait indefinitely for locks; detection and resolution are critical.
• Recovery mechanisms (e.g., undo/redo logs) restore databases to a consistent state after failures.
• Real-world systems (banks, e-commerce) use these techniques to handle millions of concurrent transactions.

1. Introduction to Transactions

A transaction is a sequence of operations performed as a single logical unit of work. In databases, it ensures that data changes are applied consistently, even if errors or failures occur.

ACID Properties

Transactions must satisfy four key properties to guarantee reliability:

mindmap:
  root((ACID Properties))
    Atomicity
    Consistency
    Isolation
    Durability

• Atomicity: All operations in a transaction succeed or none do (all-or-nothing).
• Consistency: The transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions appear to execute sequentially (no interference).
• Durability: Once committed, changes persist even after system failures.

Why ACID matters? Without ACID, databases could corrupt data (e.g., a bank transfer failing halfway, leaving accounts in an inconsistent state).

2. Concurrency Control

When multiple users access a database simultaneously, concurrency control ensures data integrity. Without it, anomalies like:

• Lost Update: Two transactions overwrite each other’s changes.
• Dirty Read: A transaction reads uncommitted data.
• Non-repeatable Read: A transaction sees inconsistent data between reads.
• Phantom Read: A transaction re-reads rows that were inserted/deleted by another.

Concurrency Control Techniques

Example: Two-Phase Locking (2PL) A strict concurrency control method where:

1. Growing Phase: Transactions acquire locks.
2. Shrinking Phase: Transactions release locks.

sequenceDiagram
  participant T1
  participant T2
  participant DB

  T1->>DB: Lock Account A (read)
  T2->>DB: Lock Account A (write)  <!-- Should block T1 if strict 2PL -->
  alt T1 blocked
    T1->>T2: Waits for DB
  else T2 proceeds
    T2->>DB: Lock Account B (write)
    T2->>DB: Release lock on Account A
    T2->>DB: Commit
  end
  T1->>DB: Release lock on Account A
  T1->>DB: Commit

Worked Example: Transfer Rs. 500 from A to B Assume:

• A = 5500, B = 4500, C = 2000
• Transactions:
  • T1: Transfer Rs. 500 from A to B.
  • T2: Transfer Rs. 300 from B to C.
  • T3: Transfer Rs. 200 from C to A.

Using 2PL:

1. T1 locks A (read), B (write).
2. T2 locks B (read), C (write).
3. T3 locks C (read), A (write) → Deadlock! (T1 and T3 wait for each other).

Resolution: Use a deadlock detection algorithm (e.g., wait-for graph) to abort one transaction.

3. Deadlocks and Recovery

A deadlock occurs when two or more transactions are blocked forever, each holding a lock the other needs.

Deadlock Detection

1. Wait-for Graph: Nodes = transactions; edges = "waits for."
   • If a cycle exists → deadlock.
2. Timeouts: Abort a transaction if it waits too long.

Recovery Mechanisms

• Undo: Reverts changes (e.g., logs before-image of data).
• Redo: Reapplies committed changes (e.g., logs after-image).
• Checkpointing: Periodically saves database state to speed up recovery.

Example: Bank Transaction Failure If a system crashes during a transfer:

1. Undo log records the old balance of A and B.
2. After restart, the system rolls back the failed transfer.

4. Real-World Applications

In the Real World

1. eSewa/Khalti (Digital Payments)

   • Idea: Uses ACID transactions to ensure funds are deducted from one account and credited to another without partial updates.
   • Example: When you pay Rs. 1000 via eSewa, the system locks your account and the merchant’s account until the transfer completes. If the system crashes midway, the transaction is rolled back to avoid inconsistencies.

2. Daraz (E-commerce)

   • Idea: Concurrency control prevents "phantom stockouts." If two users try to buy the last item simultaneously, only one should succeed.
   • Example: Daraz uses optimistic concurrency—it checks stock levels at checkout time. If another user bought the item between the first user’s check and payment, the system rejects the duplicate order.

3. NEPSE (Stock Market)

   • Idea: Two-phase locking ensures no two traders can modify the same stock record at the same time, preventing fraudulent trades.
   • Example: When you buy shares of ABC Corp, NEPSE locks your account balance and the stock’s available quantity until the trade is confirmed. If the system fails, the lock is released, and the trade is rolled back.

5. Worked Example: Bank Transfer with Concurrency

Scenario:

• Accounts: A = 5500, B = 4500, C = 2000.
• Transactions:
  • T1: Transfer Rs. 500 from A to B.
  • T2: Transfer Rs. 300 from B to C.

Trace with 2PL:

1. T1 locks A (read), B (write).
2. T2 locks B (read) → Blocked (waits for T1 to release B).
3. T1 updates balances:
   • A = 5000, B = 5000.
4. T1 releases locks on A and B.
5. T2 acquires lock on B, updates:
   • B = 4700, C = 2300.

Final State:

• A = 5000, B = 4700, C = 2300.

Without Concurrency Control: If T2 reads B = 4500 before T1 updates it, T2 might deduct Rs. 300 from B = 4500, leading to B = 4200 (incorrect).

6. Comparison of Concurrency Control Methods

7. Exam Tip

• Define ACID properties clearly (e.g., "Atomicity ensures all operations in a transaction succeed or fail together").
• Draw wait-for graphs for deadlock detection questions.
• Practice 2PL traces—show lock acquisition/release steps.
• Compare concurrency methods in tables (like above).
• Relate to real systems: Mention how eSewa/Daraz use these concepts.
• For numerical problems:
  • Show initial balances.
  • List transactions with lock steps.
  • Highlight deadlocks or anomalies if they occur.

Common Pitfalls:

• Forgetting to mention durability (e.g., "changes persist after crashes").
• Not explaining why concurrency control is needed (e.g., "to avoid lost updates").
• Drawing locks incorrectly (e.g., missing the "shrinking phase" in 2PL).

A cycle in a wait-for graph indicates a deadlock between transactions. (Image: LimStift, CC BY 3.0, via Wikimedia Commons)