Active Transactions: ACID, Concurrency, Recovery & Mobile Apps

TAKEAWAYS:

• ACID properties ensure reliable transactions in mobile apps where network failures are common.
• Locking (pessimistic) vs. MVCC (optimistic) concurrency control trade offs between consistency and performance.
• Write-ahead logging and checkpointing protect mobile data from crashes (e.g., Daraz order processing).
• Mobile-specific challenges: offline transactions, partial failures, and eventual consistency in Firebase.
• Real-world use: eSewa’s payment locks funds until confirmation; Pathao’s ride allocation uses MVCC.
• Exam focus: Compare ACID vs. BASE, trace a 2PL deadlock, and explain SQLite’s WAL mode.

Core Concepts: ACID Properties

Mobile transactions must guarantee reliability despite intermittent connectivity. The ACID model defines four invariants:

1. Atomicity

A transaction is all or nothing. In a mobile app, this means:

• If a user transfers ₹500 from Khalti to eSewa and the app crashes mid-transfer, the money must not disappear or duplicate.
• Mechanism: Undo/redo logs (e.g., SQLite’s BEGIN IMMEDIATE + ROLLBACK).

flowchart TD
    A["Transaction T1: Transfer ₹500"] -->|"Step 1: Deduct from Account A"| B["Log: (A, -500)"]
    B --> C["Step 2: Add to Account B"]
    C -->|"Crash"| D["Rollback: Revert (A, -500)"]
    C -->|"Success"| E["Commit: Apply both steps"]

Visual: Transaction Log

2. Consistency

A transaction moves the database from one valid state to another. Example:

• In NEPSE’s trading system, a buy order must not exceed available shares.
• Constraint: CHECK (balance >= amount) in SQLite.

Real-World Tie-In: eSewa enforces consistency by:

1. Locking the sender’s balance during transfer.
2. Rejecting if balance < amount (violates consistency).

3. Isolation

Concurrent transactions appear sequential. Without isolation:

• Dirty Reads: User A sees User B’s unconfirmed Khalti transfer.
• Lost Updates: Two Pathao drivers update the same ride’s status simultaneously.

Solutions:

Visual: Locking in SQLite

4. Durability

Committed transactions survive crashes. Example:

• Ncell’s billing system must persist charges even after a power outage.
• Mechanism: Write-ahead logging (WAL) in SQLite.

How WAL Works:

1. Log changes to disk before modifying the database.
2. On recovery, replay the log to restore consistency.

sequenceDiagram
    participant DB as SQLite DB
    participant Log as WAL Log
    DB->>Log: Write "UPDATE account SET balance = 1000"
    Log-->>DB: Acknowledge
    DB->>DB: Apply update
    loop Crash
        DB->>Log: Replay log on restart
    end

Concurrency Control in Mobile Apps

Mobile devices face unique challenges:

• Partial connectivity: Transactions may start offline and commit later.
• High latency: Cloud-based locking (e.g., Firebase) adds delay.
• Resource constraints: Limited CPU/memory for complex protocols.

Locking Protocols

Worked Example: Deadlock in Pathao Two drivers (D1, D2) try to update the same ride’s status:

1. D1 locks ride.status (sets to "accepted").
2. D2 locks driver.available (sets to "false").
3. D1 waits for driver.available → deadlock.

Solution: Timeout locks after 5 seconds (Pathao’s real-world fix).

Recovery Techniques

Mobile apps must recover from:

• Crashes: Sudden app termination (e.g., Daraz app killed by Android).
• Network Failures: Offline transactions in Firebase.

1. Checkpointing

Periodically save the database state to disk.

• Example: SQLite auto-checkpoints every 1000 pages.
• Trade-off: High overhead for frequent checkpoints.

2. Logging

Two types:

Visual: Logging During a Transfer

Mobile-Specific Challenges

Real-World Example: Firebase Transactions

// Firebase ensures atomicity even offline
firebase.database().ref('users/' + uid).transaction(function(currentData) {
  if (currentData.balance >= amount) {
    return { balance: currentData.balance - amount };
  } else {
    throw "Insufficient funds";
  }
});

In the Real World

1. eSewa Payments

   • Idea Used: ACID + Locking
   • How: When you transfer ₹500, eSewa:
     • Locks your balance (SELECT balance FOR UPDATE).
     • Deducts ₹500 and logs the change.
     • Releases the lock only after the recipient confirms.
   • Failure Case: If the app crashes mid-transfer, the log ensures your balance is restored.

2. Pathao Ride Allocation

   • Idea Used: MVCC + Optimistic Concurrency
   • How: Multiple drivers may "accept" the same ride simultaneously. Pathao:
     • Uses MVCC to track ride versions.
     • Only the first commit wins; others see the updated version.
   • Result: No deadlocks, but rare conflicts (resolved by retry).

3. Daraz Order Processing

   • Idea Used: Write-Ahead Logging
   • How: When you place an order:
     1. Daraz logs ORDER_PENDING to disk.
     2. Updates inventory (logged).
     3. If the app crashes, the log replays to either:
        • Ship the order (ORDER_SHIPPED), or
        • Cancel it (ORDER_CANCELLED).

Exam Tip

1. ACID vs. BASE:

   • ACID: Strict consistency (banks, eSewa).
   • BASE: Eventual consistency (WhatsApp, Firebase).
   • Exam Question: "Why does Firebase use BASE instead of ACID?" → Answer: Offline support, scalability.

2. Locking Protocols:

   • 2PL: Simple but deadlock-prone (avoid in mobile).
   • MVCC: Preferred for mobile (SQLite, Firebase).
   • Exam Question: "Trace a deadlock in a mobile app using 2PL." → Draw the wait-for graph.

3. Recovery:

   • Always mention write-ahead logging for durability.
   • Exam Question: "How does SQLite recover after a crash?" → Steps:
     1. Replay WAL log.
     2. Apply committed transactions.
     3. Ignore uncommitted changes.

4. Mobile-Specific:

   • Offline-first apps (Firebase) use optimistic concurrency.
   • Critical apps (eSewa) use pessimistic locking.
   • Exam Question: "Compare SQLite and Firebase for transactions." → Table:

   Feature	SQLite	Firebase
   ACID Support	Full (WAL mode)	Partial (optimistic)
   Offline	Manual handling	Built-in
   Concurrency	MVCC	Conflict resolution (last write)

5. Worked Example:

   • Always trace step-by-step (e.g., a transaction log or lock table).
   • Exam Question: "Show how a deadlock occurs in Pathao’s ride system." → Use the mermaid deadlock diagram above.

Final Note: Mobile transactions prioritize availability over strict consistency. Master the trade-offs between ACID and BASE, and always relate to eSewa, Firebase, or SQLite in exams.