Transactions, Locks, Isolation, Deadlocks & Recovery

What is a Transaction?

A transaction is a sequence of one or more database operations (SQL queries) that must be executed atomically—either all succeed or none do. Think of it as a single logical unit of work, like transferring money between two bank accounts or placing an order on Daraz.

ACID Properties

Transactions follow four key properties:

stateDiagram-v2
    [*] --> Active: Transaction starts
    Active --> Partially_committed: Last operation executed
    Partially_committed --> Committed: Commit succeeds
    Partially_committed --> Aborted: Rollback triggered
    Committed --> [*]
    Aborted --> [*]

In the Real World

1. Khalti Payments: When you transfer money via Khalti, the system ensures atomicity—either the sender’s balance decreases and the receiver’s increases, or neither changes. If the transfer fails midway, both accounts remain unchanged.
2. Ncell Top-Up: A top-up transaction must be durable. Even if the server crashes after processing your request, the minutes should still be added to your account upon recovery.
3. Daraz Order Processing: When you place an order, Daraz locks the inventory items (locking mechanism) to prevent overselling while processing payment. If payment fails, the lock is released (rollback).

Transaction States and Life Cycle

A transaction passes through several states:

1. Active: Executing SQL operations.
2. Partially Committed: Last operation executed, but commit not yet written to disk.
3. Committed: Changes are permanent.
4. Failed: Transaction cannot proceed (e.g., deadlock).
5. Aborted: Rollback triggered; changes discarded.

sequenceDiagram
    participant User
    participant DBMS
    User->>DBMS: BEGIN TRANSACTION
    User->>DBMS: UPDATE account_balance SET balance = balance - 1000 WHERE user_id = 1
    User->>DBMS: UPDATE account_balance SET balance = balance + 1000 WHERE user_id = 2
    User->>DBMS: COMMIT
    DBMS-->>User: Success (or Rollback on failure)

Concurrency Control

Concurrency control manages simultaneous transactions to ensure isolation and consistency. Without it, two users might read and modify the same data incorrectly (e.g., double-booking a Pathao ride).

Locking Mechanisms

Locks prevent conflicting operations on the same data:

graph LR
    A["Transaction T1: READS Account A"] -->|"Acquires Shared Lock"| B["Account A"]
    C["Transaction T2: WRITES Account A"] -->|"Waits for Exclusive Lock"| B
    D["Transaction T3: READS Account B"] -->|"Acquires Shared Lock"| E["Account B"]

Deadlocks

A deadlock occurs when two or more transactions wait indefinitely for locks held by each other. Example:

• T1 locks Account_X and waits for Account_Y.
• T2 locks Account_Y and waits for Account_X.

Deadlock Prevention Strategies

graph TD
    A["T1 holds Lock X, waits for Lock Y"] --> B["T2 holds Lock Y, waits for Lock X"]

Recovery Techniques

If a transaction fails or the system crashes, recovery mechanisms restore consistency:

1. Deferred Update (Write-Ahead Logging):

   • Changes are written to a log before the database.
   • Example: Ncell writes recharge records to a log before updating the user’s balance.

2. Immediate Update:

   • Changes are applied to the database immediately, but a redo log ensures durability.
   • Example: Khalti commits payment updates to the database and logs them for recovery.

3. Checkpointing:

   • Periodically saves the database state to disk.
   • Example: Banks perform checkpoints every hour to minimize data loss during crashes.

sequenceDiagram
    participant Transaction
    participant Log
    participant Database
    Transaction->>Log: Write operation to log
    Transaction->>Database: Apply changes
    Database-->>Log: Acknowledge
    Log->>Database: Recover on crash

Exam Tip

1. ACID Properties: Always explain atomicity (all-or-nothing) and isolation (no interference) with examples like bank transfers or e-commerce orders.
2. Locking: Differentiate between shared (read) and exclusive (write) locks. Use real scenarios (e.g., Pathao ride booking).
3. Deadlocks: Draw a wait-for graph and explain timeout or rollback as solutions.
4. Recovery: Compare deferred update (log-first) vs. immediate update (database-first) with examples like Khalti or Ncell.
5. SQL Keywords: Know BEGIN TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT for practical questions.

A graph illustrating two transactions waiting for each other’s locks. (Image: Kkaaii, CC BY-SA 4.0, via Wikimedia Commons)