Database Transactions & Concurrency Control: ACID, Locking, Recovery

TAKEAWAYS:

• A transaction is a logical unit of work (e.g., transferring money) that must complete atomically, consistently, isolated, and durably (ACID).
• Concurrency control (e.g., locking, timestamping) prevents dirty reads, lost updates, and cascading failures in multi-user systems.
• Recovery uses logs (redo/undo) and checkpoints to restore databases after crashes or corruption.
• Two-phase locking (2PL) is the gold standard for concurrency but can cause deadlocks; optimistic concurrency avoids locks but risks conflicts.
• Banks (e.g., NMB) and eSewa use transactions to guarantee funds transfers; Daraz relies on concurrency to handle thousands of orders simultaneously.
• Exam focus: Define ACID, explain 2PL, trace a recovery scenario, and compare locking vs. timestamping.

---

1. Transactions: The Atomic Unit of Work

A transaction is a sequence of operations (e.g., debiting Account A, crediting Account B) that must appear to users as a single, indivisible action. If any step fails, the entire transaction rolls back.

ACID Properties

Transactions must satisfy four properties to ensure reliability:

Worked Example: eSewa Funds Transfer

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A123';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B456';
COMMIT;

• If the network fails after the first UPDATE, eSewa rolls back both changes to avoid partial transfers.

---

2. Concurrency Control: Preventing Chaos

When multiple users access a database simultaneously, concurrency anomalies can occur:

Techniques to Avoid Anomalies

1. Locking (Pessimistic Approach)

   • Shared Lock (S-lock): Allows read-only access (e.g., checking account balance).
   • Exclusive Lock (X-lock): Blocks all other transactions (e.g., updating salary in a payroll system).
   • Two-Phase Locking (2PL):
     • Growth Phase: Acquire locks before releasing any.
     • Shrinkage Phase: Release locks but never acquire new ones.
     • Deadlock Example:

       sequenceDiagram
         participant T1 as Transaction 1
         participant T2 as Transaction 2
         participant DB as Database
         T1->>DB: Locks Account A (X-lock)
         T2->>DB: Locks Account B (X-lock)
         T2->>DB: Requests lock on Account A (deadlock)
         T1->>DB: Requests lock on Account B (deadlock)

       Solution: Use a deadlock detector (e.g., NTC’s billing system) or timeout.

2. Timestamp Ordering (Optimistic Approach)

   • Assign timestamps to transactions and enforce read/write rules based on them.
   • Example: Google’s Spanner database uses timestamps to serialize transactions globally.

3. Multi-Version Concurrency Control (MVCC)

   • Maintains multiple versions of data (e.g., PostgreSQL).
   • Use Case: WhatsApp’s message history shows edits without blocking readers.

---

3. Database Recovery: Undoing the Unthinkable

Crashes, power failures, or human errors can corrupt databases. Recovery mechanisms restore consistency:

Key Concepts

• Redo Log: Records changes to reapply after a crash.
• Undo Log: Records changes to roll back (e.g., failed transactions).
• Checkpoint: A snapshot of the database state to minimize redo/undo work.

Worked Example: Ncell Call Log Recovery

1. A crash occurs mid-transaction (e.g., updating a user’s call minutes).
2. The redo log applies committed changes (e.g., deducting 10 minutes from User X).
3. The undo log reverts any uncommitted changes (e.g., if User X’s call failed).

---

4. Real-World Applications

In the Real World

1. eSewa/Khalti (Transactions + Locking)

   • Idea: Uses two-phase locking to ensure funds transfers are atomic.
   • How: When User A sends ₹1,000 to User B, eSewa locks both accounts until the transfer completes. If the network fails, the transaction rolls back.

2. Daraz (Concurrency Control)

   • Idea: Multi-Version Concurrency Control (MVCC) allows thousands of users to check inventory simultaneously without blocking.
   • How: Daraz maintains multiple versions of stock levels, so a user’s "Add to Cart" doesn’t stall while another user updates the count.

3. NEPSE (Recovery Mechanisms)

   • Idea: Checkpoints and redo logs ensure stock trades persist even after server crashes.
   • How: If NEPSE crashes during a trade, the system redoes committed transactions (e.g., updating shareholder balances) and undoes any failed ones.

---

5. Comparison Table: Concurrency Control Techniques

---

6. Exam Tip

• Define ACID with examples (e.g., "Atomicity ensures a bank transfer doesn’t leave accounts in an inconsistent state").
• Trace a transaction through 2PL or MVCC (show lock acquisition/release or versioning).
• Compare techniques: Always include pros/cons (e.g., "2PL is safe but slow; MVCC is fast but complex").
• Recovery question: Draw a flowchart like the one above and label redo/undo steps.
• Real-world link: Connect answers to eSewa (transactions), Daraz (concurrency), or NEPSE (recovery).

---