Active Transactions: ACID, Concurrency & Recovery

TAKEAWAYS:

• ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee reliable transactions in mobile databases.
• Concurrency control (locking, MVCC, 2PL) prevents race conditions in multi-user apps (e.g., Khalti payments).
• Recovery mechanisms (checkpoints, logs, shadow paging) restore data after crashes (e.g., Ncell’s billing system).
• Mobile-specific challenges: Limited bandwidth, device failures, and offline-first sync (e.g., Daraz’s order processing).
• Optimistic vs. pessimistic locking: Trade-offs in latency vs. consistency for apps like Pathao’s ride allocation.
• Two-phase commit (2PC): Ensures atomicity across distributed services (e.g., NEPSE’s stock trades).

1. What Are Active Transactions?

Active transactions are self-managing database operations that:

• Automatically handle failures (e.g., app crashes, network drops).
• Maintain data consistency across distributed systems (e.g., bank transfers between Khalti and Nabil Bank).
• Support concurrency without corrupting data (e.g., simultaneous Daraz order updates).

Why Mobile Apps Need Them

Mobile apps often interact with remote databases (cloud or edge servers). Unlike desktop apps, they face:

• Unstable networks (e.g., NTC’s intermittent 4G).
• Device power-offs (e.g., Pathao driver’s phone dying mid-ride).
• Multi-user conflicts (e.g., two users editing the same eSewa bill).

2. ACID Properties: The Foundation

ACID ensures transactions are reliable. Break it down:

Visual: ACID in a Bank Transfer

flowchart TD
    A["User A: Transfer ₹500 to User B"] --> B["Check Balance (A: ₹1000, B: ₹500)"]
    B --> C["Lock A’s & B’s accounts"]
    C --> D["Debit A: ₹500"]
    D --> E["Credit B: ₹500"]
    E --> F["Commit: Update DB"]
    F --> G["Release Locks"]
    G --> H["Durable Log Entry"]

Worked Example: eSewa Payment Failure

1. User pays ₹200 for an NTC SIM.
2. Atomicity: If the SIM isn’t delivered, the money is refunded.
3. Isolation: Another user can’t buy the same SIM in the meantime.
4. Durability: The payment record survives a server reboot.

3. Concurrency Control: Handling Multiple Users

When multiple mobile apps access the same data (e.g., Khalti’s transaction log), conflicts arise. Solutions:

A. Locking Mechanisms

Visual: Optimistic vs. Pessimistic Locking

flowchart LR
    subgraph Pessimistic Locking
        A["User 1: Locks Ride"] --> B["User 2: Waits"]
        B --> C["User 1: Completes"]
    end
    subgraph Optimistic Locking
        D["User 1: Takes Ride"] --> E["User 2: Checks Conflict"]
        E -->|"No Conflict"| F["User 2: Success"]
        E -->|"Conflict"| G["Retry"]
    end

Worked Example: Pathao Ride Allocation

• Pessimistic: Lock the ride ID when a user books it (prevents double-booking).
• Optimistic: Let users "reserve" a ride; if another user books it first, show an error and retry.

B. Two-Phase Locking (2PL)

Ensures serializability (transactions appear to run one after another). Steps:

1. Growing Phase: Acquire all locks.
2. Shrinking Phase: Release all locks only after commit.

Visual: 2PL in a Mobile Payment

sequenceDiagram
    participant User
    participant App
    participant DB
    User->>App: Request ₹100 transfer
    App->>DB: Lock Account A
    App->>DB: Lock Account B
    DB-->>App: Grants locks
    App->>DB: Debit A
    App->>DB: Credit B
    App->>DB: Commit
    DB->>App: Release locks

Code Example: 2PL in SQL (Pseudocode)

BEGIN TRANSACTION;
-- Growing Phase
LOCK TABLE accounts IN ROW EXCLUSIVE MODE;
SELECT balance FROM accounts WHERE user_id = 'A';
-- Update balances
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';
-- Shrinking Phase
COMMIT;

Trace Table: 2PL Execution

4. Recovery Techniques: Handling Crashes

Mobile apps must recover from:

• App crashes (e.g., Khalti freezing mid-payment).
• Network failures (e.g., NTC dropping connection).
• Server crashes (e.g., Daraz’s database going down).

A. Write-Ahead Logging (WAL)

Before modifying data, log changes to a transaction log. Example: Ncell’s billing system logs every recharge before updating the database.

Visual: WAL Process

flowchart TD
    A["Transaction: Recharge ₹500"] --> B["Log to Disk: 'DEBIT User X: ₹500'"]
    B --> C["Update DB"]
    C --> D["Log Commit"]

B. Checkpointing

Periodically saves the entire database state to reduce recovery time. Example: eSewa takes a checkpoint every 5 minutes.

Visual: Checkpoint Recovery

flowchart LR
    A["Normal Operation"] --> B["Checkpoint: Save DB State"]
    B --> C["Crash"]
    C --> D["Restore from Last Checkpoint"]
    D --> D["Replay Logs Since Checkpoint"]

C. Shadow Paging

Maintains a copy of the database and swaps it atomically on commit. Example: Banks use this for audit trails.

Visual: Shadow Paging

flowchart TD
    A["Old Page"] --> B["New Page (Shadow)"]
    B --> C["Commit: Swap Pointers"]

5. Mobile-Specific Challenges

Worked Example: Daraz Order Processing

1. User places an order (offline).
2. Optimistic Lock: Daraz assumes no conflicts.
3. On reconnect:
   • If inventory is still available → Commit.
   • If sold out → Show error and retry.

6. Two-Phase Commit (2PC) for Distributed Systems

Ensures atomicity across multiple servers (e.g., Khalti → Nabil Bank → NTC). Phases:

1. Prepare Phase: Ask all participants if they’re ready.
2. Commit Phase: If all say "yes," commit; else, roll back.

Visual: 2PC in a Mobile Payment

sequenceDiagram
    participant App
    participant Khalti
    participant Bank
    participant NTC
    App->>Khalti: Initiate Transfer
    Khalti->>Bank: Prepare (Debit)
    Khalti->>NTC: Prepare (Credit)
    Bank-->>Khalti: Ready
    NTC-->>Khalti: Ready
    Khalti->>Bank: Commit
    Khalti->>NTC: Commit

Code Example: 2PC Coordinator (Pseudocode)

def two_phase_commit(participants, transaction):
    # Phase 1: Prepare
    for participant in participants:
        if not participant.prepare(transaction):
            for p in participants:
                p.abort()
            return False
    # Phase 2: Commit
    for participant in participants:
        participant.commit()
    return True

Trace Table: 2PC Execution

In the Real World

1. Khalti Payments

   • ACID: Ensures money transfers are atomic (no partial credits).
   • 2PC: Coordinates between Khalti, banks, and merchants (e.g., Daraz).
   • Recovery: Logs every transaction for audit trails.

2. Pathao Ride Allocation

   • Optimistic Locking: Lets users "reserve" a ride; conflicts are resolved via retries.
   • MVCC: Maintains ride history without blocking reads.

3. NEPSE Stock Trades

   • 2PL: Prevents two traders from buying the same share simultaneously.
   • Shadow Paging: Keeps a backup of trades for regulatory compliance.

4. eSewa Bill Payments

   • WAL: Logs every payment before updating the database.
   • Checkpointing: Saves state every hour to recover from crashes.

Exam Tip

1. ACID Questions:

   • Always define each property with a mobile example (e.g., "Atomicity in Khalti transfers").
   • Compare pessimistic vs. optimistic locking with latency vs. consistency trade-offs.

2. Concurrency Control:

   • Draw locking diagrams (e.g., 2PL phases).
   • Explain MVCC using a versioned table example.

3. Recovery:

   • Describe WAL with a log entry example.
   • Differentiate checkpointing vs. shadow paging in terms of overhead.

4. 2PC:

   • Show a sequence diagram for distributed transactions.
   • Highlight failure cases (e.g., one participant aborts).

Common Pitfalls:

• Forgetting durability (e.g., "The transaction is atomic but not durable if the log isn’t written").
• Confusing optimistic vs. pessimistic locking (optimistic = "hope for the best, retry on conflict").
• Skipping real-world ties (examiners love Khalti/Ncell examples).

Visual Summary: Active Transactions in Mobile Apps

mindmap
  root((Active Transactions))
    ACID
      Atomicity: All-or-nothing (Khalti transfers)
      Consistency: Valid state transitions (Ncell billing)
      Isolation: No interference (Pathao rides)
      Durability: Survives crashes (Daraz orders)
    Concurrency Control
      Pessimistic Locking: Lock before access (Pathao)
      Optimistic Locking: Check after access (Daraz)
      MVCC: Read snapshots (NEPSE)
    Recovery
      WAL: Log before write (eSewa)
      Checkpointing: Periodic snapshots (Khalti)
      Shadow Paging: Atomic swaps (Banks)
    Distributed
      2PC: Atomic across services (Khalti → Bank → NTC)