Unit 6: Transaction Processing

Why This Chapter Pays Your Salary

Every ₹1 that moves through India's banking system — UPI transfers, ATM withdrawals, online payments — relies on transactions. A transaction is a promise: either ALL operations succeed, or NONE do. Without this guarantee, ₹10,000 could vanish from your account without reaching the recipient. Transaction processing is the foundation of database reliability, and it's the most tested DBMS topic in GATE CS (5-8 marks every year).

Learning Outcomes — Bloom's Taxonomy

Concept Explanations

3.1 ACID Properties — The Four Guarantees

SQL — Transaction in Action
-- ═══════════════════════════════════════
-- Bank Transfer: ₹10,000 from Rahul (A/C 1001) to Priya (A/C 1002)
-- ═══════════════════════════════════════

-- Oracle: transactions start implicitly with the first DML
-- PostgreSQL: explicit BEGIN required
BEGIN;  -- PostgreSQL; Oracle: not needed (auto-begin)

SAVEPOINT before_transfer;  -- Bookmark in case partial rollback needed

-- Step 1: Debit Rahul
UPDATE accounts SET balance = balance - 10000
WHERE account_id = 1001 AND balance >= 10000;  -- Balance check in WHERE

-- Step 2: Verify debit succeeded (row was actually updated)
-- In PL/SQL: IF SQL%ROWCOUNT = 0 THEN ROLLBACK; END IF;

-- Step 3: Credit Priya
UPDATE accounts SET balance = balance + 10000
WHERE account_id = 1002;

-- Step 4: Log the transaction
INSERT INTO transactions (from_acc, to_acc, amount, txn_date, status)
VALUES (1001, 1002, 10000, CURRENT_TIMESTAMP, 'SUCCESS');

COMMIT;  -- All 3 operations are now permanent (DURABLE)

-- If ANY step fails:
-- ROLLBACK TO before_transfer;  -- Undo to savepoint
-- OR
-- ROLLBACK;  -- Undo everything since BEGIN

3.2 Transaction States

Transaction State Diagram
                    ┌─────────────┐
                    │   ACTIVE    │ ← Transaction executing operations
                    └──────┬──────┘
                           │
              ┌────────────┼────────────┐
              │ (success)               │ (failure)
              ▼                         ▼
     ┌────────────────┐       ┌────────────────┐
     │ PARTIALLY      │       │   FAILED       │ ← Error occurred
     │ COMMITTED      │       └───────┬────────┘
     └───────┬────────┘               │
             │                        │ ROLLBACK
             │ COMMIT written         ▼
             │ to disk         ┌────────────────┐
             ▼                 │   ABORTED      │ ← All changes undone
     ┌────────────────┐       └────────────────┘
     │   COMMITTED    │            │
     └────────────────┘            │ (restart?)
                                   ▼
                             Re-execute or
                             terminate

3.3 Schedules & Serializability

Schedules — Example
T1: Transfer ₹100 from A to B     T2: Add 10% interest to A and B
    R(A), A=A-100, W(A),              R(A), A=A*1.1, W(A),
    R(B), B=B+100, W(B)               R(B), B=B*1.1, W(B)

Initial: A=1000, B=500

Serial Schedule S1 (T1 then T2):
  T1: A=1000-100=900, B=500+100=600
  T2: A=900*1.1=990, B=600*1.1=660
  Final: A=990, B=660

Serial Schedule S2 (T2 then T1):
  T2: A=1000*1.1=1100, B=500*1.1=550
  T1: A=1100-100=1000, B=550+100=650
  Final: A=1000, B=650

Both are correct! Any concurrent schedule equivalent to S1 or S2 is SERIALIZABLE.

Conflict Serializability — Precedence Graph Test

Conflict Serializability Test
Two operations CONFLICT if: (1) they belong to DIFFERENT transactions,
(2) they access the SAME data item, (3) at least one is a WRITE.

Conflict pairs: R-W, W-R, W-W (NOT R-R — both reads, no conflict)

Test: Build a PRECEDENCE GRAPH (directed graph)
  - Node for each transaction
  - Edge Ti → Tj if Ti has a conflicting operation BEFORE Tj's conflicting operation
  - If graph has NO CYCLE → schedule is CONFLICT SERIALIZABLE
  - If graph has a CYCLE → NOT conflict serializable

Example Schedule:
  T1: R(A)  W(A)            R(B)  W(B)
  T2:            R(A)  W(A)            R(B)  W(B)

Conflicts:
  T1.W(A) before T2.R(A) → T1 → T2
  T1.W(A) before T2.W(A) → T1 → T2
  T1.R(B) before T2.W(B) → T1 → T2
  T1.W(B) before T2.W(B) → T1 → T2

Precedence Graph: T1 → T2 (all edges same direction)
No cycle! ✓ CONFLICT SERIALIZABLE (equivalent to serial T1,T2)

3.4 Concurrency Problems — What Goes Wrong

3.5 Lock-Based Concurrency Control

Lock Types
┌─────────────────────────────────────────────────────┐
│ Lock Type       │ Symbol │ Allows Concurrent        │
├─────────────────┼────────┼──────────────────────────│
│ Shared (S)      │   S    │ Other S locks: YES ✓     │
│ (Read Lock)     │        │ Other X locks: NO ✗      │
├─────────────────┼────────┼──────────────────────────│
│ Exclusive (X)   │   X    │ Other S locks: NO ✗      │
│ (Write Lock)    │        │ Other X locks: NO ✗      │
└─────────────────┴────────┴──────────────────────────┘

Lock Compatibility Matrix:
         │ Requesting S │ Requesting X │
─────────┼──────────────┼──────────────│
Held: S  │   GRANTED ✓  │   WAIT ✗     │
Held: X  │   WAIT ✗     │   WAIT ✗     │
None     │   GRANTED ✓  │   GRANTED ✓  │

Rule: Multiple readers OK. Any writer = exclusive access.

Two-Phase Locking (2PL)

2PL Example
T1: Lock-S(A), Read(A), Lock-X(B), Write(B) | Unlock(A), Unlock(B)
    ← ─ ─ Growing Phase ─ ─ ─ ─ ─ ─ ─ ─ → ← Shrinking Phase →

VALID 2PL: all locks acquired before any release ✓

T2: Lock-S(A), Read(A), Unlock(A), Lock-X(B), Write(B), Unlock(B)
    ← ─ Growing ─ →     ← ─ ─ ─ NOT 2PL! ─ ─ →
    Released A then acquired B → VIOLATES 2PL ✗

3.6 Deadlocks — The Deadly Embrace

Deadlock Scenario
T1: Lock-X(A)  ───────────►  Lock-X(B)   ← WAITING for T2 to release B
T2: Lock-X(B)  ───────────►  Lock-X(A)   ← WAITING for T1 to release A

T1 holds A, wants B.
T2 holds B, wants A.
Neither can proceed. DEADLOCK! ☠️

Wait-For Graph:
  T1 ──► T2 ──► T1   (CYCLE detected → deadlock exists)

Resolution: The DBMS detects the cycle and ABORTS one transaction
(the "victim" — usually the one with least work done).
The victim is rolled back, its locks released, and the other proceeds.

Deadlock Handling Strategies

3.7 SQL Isolation Levels

SQL — Setting Isolation Levels
-- Oracle: default is READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- PostgreSQL: same syntax, default is READ COMMITTED
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your queries ...
COMMIT;

-- Session-level (all transactions in this session):
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;  -- Oracle
SET default_transaction_isolation = 'serializable';  -- PostgreSQL

3.8 Recovery Systems — Surviving Crashes

WAL Example — Bank Transfer
Log (written FIRST):         Data (written AFTER log):
LSN 1: [T1, BEGIN]
LSN 2: [T1, UPDATE, A, old=1000, new=900]
LSN 3: [T1, UPDATE, B, old=500, new=600]
LSN 4: [T1, COMMIT]          ← Log flushed to disk HERE

Now data pages can be written:
  Page(A): 1000 → 900
  Page(B): 500 → 600

CRASH after LSN 4 but before data pages written?
  Recovery: Read log → REDO T1's changes (A=900, B=600)
  Data is restored correctly! ✓

CRASH after LSN 3 but before COMMIT (LSN 4)?
  Recovery: T1 not committed → UNDO T1's changes
  A=1000 (restored), B=500 (restored)
  Transaction is rolled back. ✓

ARIES Recovery Algorithm

ARIES — Three Phases of Recovery
After a crash, the recovery manager runs three phases:

Phase 1: ANALYSIS
  Scan log from last checkpoint forward.
  Determine: which transactions were active at crash time.
  Build: dirty page table (pages that may not be on disk).

Phase 2: REDO (Repeating History)
  Replay ALL logged operations (committed and uncommitted).
  Bring database to exactly the state at crash time.
  Uses LSN (Log Sequence Number) to avoid re-doing already-applied changes.

Phase 3: UNDO (Rolling Back Losers)
  For transactions that were active (not committed) at crash time:
  Undo their operations in REVERSE order.
  Write compensation log records (CLR) to prevent re-undoing on another crash.

Result: Committed transactions → REDONE (durable).
        Uncommitted transactions → UNDONE (atomic).
        Database is consistent. ✓

Checkpoints

Checkpoint — Reducing Recovery Time
Without checkpoints: must scan ENTIRE log from the beginning. Slow!

Checkpoint: periodically flush all dirty pages to disk and record
            which transactions are active.

Recovery starts from the LAST CHECKPOINT, not the beginning of the log.

Oracle: Automatic checkpoint every 3 seconds (or configurable).
PostgreSQL: checkpoint_timeout (default 5 minutes).

Fuzzy Checkpoint: allows transactions to continue during checkpointing
(non-blocking). Used by all modern databases.

Industry Problems

SQL — Idempotent Transaction
-- Each UPI request has a unique txn_ref_id generated by the app
-- The database uses a UNIQUE constraint to prevent double processing

CREATE TABLE upi_transactions (
    txn_ref_id   VARCHAR2(50)  PRIMARY KEY,  -- App-generated unique ID
    sender_vpa   VARCHAR2(50)  NOT NULL,
    receiver_vpa VARCHAR2(50)  NOT NULL,
    amount       NUMBER(12,2),
    status       VARCHAR2(10),
    created_at   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);

-- Transaction procedure (idempotent)
CREATE OR REPLACE PROCEDURE sp_upi_pay (
    p_ref_id     IN  VARCHAR2,
    p_sender     IN  VARCHAR2,
    p_receiver   IN  VARCHAR2,
    p_amount     IN  NUMBER,
    p_result     OUT VARCHAR2
) AS
    v_existing NUMBER;
BEGIN
    -- Check if this exact request was already processed (idempotency)
    SELECT COUNT(*) INTO v_existing
    FROM upi_transactions WHERE txn_ref_id = p_ref_id;

    IF v_existing > 0 THEN
        p_result := 'DUPLICATE — already processed';
        RETURN;  -- Second request safely ignored
    END IF;

    -- Process the payment (SERIALIZABLE to prevent race condition)
    UPDATE accounts SET balance = balance - p_amount
    WHERE vpa = p_sender AND balance >= p_amount;

    IF SQL%ROWCOUNT = 0 THEN
        p_result := 'FAILED: Insufficient balance';
        ROLLBACK; RETURN;
    END IF;

    UPDATE accounts SET balance = balance + p_amount
    WHERE vpa = p_receiver;

    INSERT INTO upi_transactions VALUES
        (p_ref_id, p_sender, p_receiver, p_amount, 'SUCCESS', SYSTIMESTAMP);

    COMMIT;
    p_result := 'SUCCESS';
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN  -- Race condition: both requests arrive simultaneously
        ROLLBACK;
        p_result := 'DUPLICATE — concurrent retry caught';
    WHEN OTHERS THEN
        ROLLBACK;
        p_result := 'FAILED: ' || SQLERRM;
END;
/

Solution
Step 1: List all conflicting pairs (different Tx, same item, at least one W)

  R1(A) vs W2(A)? W2(A) doesn't exist. Skip.
  R2(A) vs W1(A): R2 before W1 → T2 → T1
  W1(A) vs R2(A)? W1 is AFTER R2 → already counted above.
  W2(B) vs R1(B): W2 before R1 → T2 → T1
  W2(B) vs W1(B): W2 before W1 → T2 → T1
  R1(B) vs W2(B)? R1 is AFTER W2 → already counted.

Step 2: Build precedence graph
  T2 → T1 (from R2(A) vs W1(A))
  T2 → T1 (from W2(B) vs R1(B))
  T2 → T1 (from W2(B) vs W1(B))

  All edges: T2 → T1. No reverse edge.

Step 3: Check for cycle
  T2 → T1 only. NO CYCLE! ✓

Step 4: Conclusion
  S is CONFLICT SERIALIZABLE.
  Equivalent serial order: T2, T1 (T2 before T1 in the serial schedule).

SQL — Deadlock Prevention
-- WRONG: Different lock ordering → deadlock possible
-- Process A: UPDATE merchant_1 ... UPDATE merchant_2
-- Process B: UPDATE merchant_2 ... UPDATE merchant_1

-- CORRECT: Always lock in the SAME order (by account_id ascending)
CREATE OR REPLACE PROCEDURE sp_settle (p_acc1 NUMBER, p_acc2 NUMBER) AS
    v_first  NUMBER := LEAST(p_acc1, p_acc2);
    v_second NUMBER := GREATEST(p_acc1, p_acc2);
BEGIN
    -- Always lock smaller account_id FIRST
    SELECT balance FROM accounts WHERE account_id = v_first FOR UPDATE;
    SELECT balance FROM accounts WHERE account_id = v_second FOR UPDATE;

    -- Now both are locked in consistent order → no deadlock possible
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = p_acc1;
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = p_acc2;
    COMMIT;
END;
/

-- Both Process A and B call sp_settle → both lock merchant_1 first
-- One waits, the other completes and releases → NO DEADLOCK ✓

Lab Exercises

MCQ Assessment Bank — 15 Questions

Hover to reveal answer and explanation.

Chapter Summary

ACID: Atomicity (all/nothing), Consistency (valid states),
      Isolation (no interference), Durability (survives crash)

TRANSACTION: BEGIN → DML → COMMIT or ROLLBACK
  SAVEPOINT sp1; ... ROLLBACK TO sp1;

SERIALIZABILITY TEST:
  1. Find conflict pairs (diff Tx, same item, ≥1 write)
  2. Draw precedence graph (edges: earlier conflict → later)
  3. No cycle = serializable ✓ | Cycle = NOT serializable ✗

LOCKS: S (read) — multiple OK | X (write) — exclusive only
  SELECT ... FOR UPDATE;  -- Acquires X-lock on selected rows

2PL: Growing (acquire) → Shrinking (release)
  Strict 2PL: X-locks until COMMIT (industry standard)

DEADLOCK: Cycle in wait-for graph → abort victim
  Prevention: lock resources in consistent order (LEAST/GREATEST)

ISOLATION LEVELS:
  READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE
  Default: READ COMMITTED (Oracle, PostgreSQL)

RECOVERY: WAL (Write-Ahead Log) + ARIES (Analysis → Redo → Undo)
  Checkpoint: limits recovery scan | PITR: backup + WAL replay

Interview & Career Preparation