Transaction Management Explained — ACID, Concurrency Control & Recovery

Transaction management ensures that database operations execute reliably and correctly even with concurrent access, system failures, or crashes.

What You’ll Learn

In this tutorial, you’ll learn how ACID properties are implemented, concurrency control protocols (2PL, timestamp ordering, MVCC), deadlock detection and prevention, and crash recovery with ARIES and write-ahead logging.

Why It Matters

Without transaction management, a bank transfer could deduct money without depositing it, two users could book the same flight seat, and a database crash could corrupt days of work. Every mission-critical application depends on these mechanisms.

Real-World Use

When you buy something on Amazon, multiple records are updated: inventory decreases, your cart clears, order status changes, and payment is processed — all within a single transaction. If any step fails, the entire transaction rolls back. Durga Antivirus Pro uses transactional databases to ensure threat definitions are updated atomically, preventing partial updates that could miss a threat.

graph LR
  subgraph "Transaction States"
    A[Active] --> B[Partially Committed]
    B --> C[Committed]
    A --> D[Failed]
    D --> E[Aborted]
    B --> D
  end
  subgraph "Concurrency Control"
    F[2PL] --> G[Lock-Based]
    H[Timestamp] --> I[Non-Locking]
    J[MVCC] --> K[Snapshot Isolation]
  end

ACID Implementation

Atomicity

Implemented via undo logging. Before modifying a page, the DBMS writes the old value to a log. If the transaction aborts, the log is used to restore the original values.

Consistency

Enforced by the application and database constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL, triggers). The DBMS prevents writes that violate constraints.

Isolation

Achieved through concurrency control protocols — the DBMS schedules concurrent transactions so the result is equivalent to running them serially.

Durability

Implemented via redo logging (write-ahead logging). Before a transaction commits, all its modifications are written to a durable log on disk. Even after a crash, the log is replayed to recover committed changes.

Concurrency Control Protocols

Two-Phase Locking (2PL)

Transactions acquire locks in the growing phase and release them in the shrinking phase. After the first unlock, no new locks can be acquired.

Strict 2PL (most common): All locks are held until the transaction commits — this ensures strict schedules that are both serializable and recoverable.

Timestamp Ordering

Each transaction gets a timestamp. Reads and writes are ordered by timestamp — if a later transaction tries to read/write data already accessed by an earlier transaction, it aborts.

class TimestampConcurrency:
    def __init__(self):
        self.data = {}
        self.read_ts = {}  # item -> max read timestamp
        self.write_ts = {}  # item -> max write timestamp

    def read(self, item, txn_ts):
        if item in self.write_ts and self.write_ts[item] > txn_ts:
            print(f"T{txn_ts}: Abort! Written by T{self.write_ts[item]} (future)")
            return None
        if item in self.data:
            self.read_ts[item] = max(self.read_ts.get(item, 0), txn_ts)
            print(f"T{txn_ts}: Read {item}={self.data[item]}")
            return self.data[item]
        return None

    def write(self, item, value, txn_ts):
        if item in self.read_ts and self.read_ts[item] > txn_ts:
            print(f"T{txn_ts}: Abort! Read by T{self.read_ts[item]} after us")
            return False
        if item in self.write_ts and self.write_ts[item] > txn_ts:
            print(f"T{txn_ts}: Abort! Written by T{self.write_ts[item]} (future)")
            return False
        self.data[item] = value
        self.write_ts[item] = txn_ts
        print(f"T{txn_ts}: Write {item}={value}")
        return True

cc = TimestampConcurrency()
cc.write("A", 100, 5)
cc.read("A", 8)
cc.write("A", 200, 3)
cc.read("A", 10)

Expected output:

T5: Write A=100
T8: Read A=100
T3: Abort! Written by T5 (future)
T10: Read A=100

MVCC (Multi-Version Concurrency Control)

Each write creates a new version of the data. Readers see a snapshot as of when their transaction started. Writes never block reads, and reads never block writes.

Used by: PostgreSQL, Oracle, MySQL (InnoDB), SQL Server (snapshot isolation).

Deadlock Detection

A deadlock occurs when two or more transactions wait for each other to release locks. The DBMS builds a wait-for graph (WFG) and periodically checks for cycles.

def detect_deadlock(waits_for):
    """
    waits_for: {T1: [T2], T2: [T3], T3: [T1]}
    Returns cycle if found
    """
    for start_txn in waits_for:
        visited = set()
        stack = [start_txn]
        while stack:
            current = stack.pop()
            if current in visited:
                continue
            visited.add(current)
            for waiting_for in waits_for.get(current, []):
                if waiting_for == start_txn:
                    print(f"DEADLOCK DETECTED! Cycle involves {visited | {start_txn}}")
                    victim = min(visited | {start_txn})
                    print(f"Victim selected: T{victim}")
                    return True
                stack.append(waiting_for)
    print("No deadlock detected")
    return False

detect_deadlock({1: [2], 2: [3], 3: [1]})
detect_deadlock({1: [2], 2: [3], 3: [4]})

Expected output:

DEADLOCK DETECTED! Cycle involves {1, 2, 3}
Victim selected: T1
No deadlock detected

Recovery: ARIES

ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) is the most widely used recovery method. It uses write-ahead logging (WAL) — log records are written to stable storage before the actual data page is modified.

ARIES Recovery Phases

1. Analysis: Scan the log from the last checkpoint to identify dirty pages and active transactions
2. Redo: Replay all changes from committed transactions (repeat history)
3. Undo: Roll back all changes from transactions that were active at crash time

Common Mistakes

1. Not using transactions for related operations: Each related INSERT/UPDATE/DELETE should be in the same transaction. Otherwise, partial failures leave inconsistent data.
2. Setting isolation level too high: Serializable isolation prevents anomalies but kills concurrency. Use READ COMMITTED or SNAPSHOT ISOLATION unless you need strict serializability.
3. Ignoring deadlock retries: Deadlocks happen. Your application must handle deadlock errors and retry the transaction.
4. Long-running transactions: Holding locks for minutes blocks other transactions. Keep transactions as short as possible.
5. Not considering phantom reads: SELECT ... WHERE in REPEATABLE READ can see new rows inserted by other transactions (phantom reads). Use SERIALIZABLE or index-range locks.

Practice Questions

1. What is write-ahead logging? Log records must be written to stable storage before the corresponding data pages are modified. This ensures the log can be used for recovery.

2. How does MVCC differ from 2PL? MVCC creates new versions on writes and gives readers a consistent snapshot. 2PL uses locks to prevent conflicts. MVCC has better read concurrency.

3. What causes a deadlock? Two or more transactions each hold locks needed by the others, creating a cycle in the wait-for graph.

4. What is the difference between REDO and UNDO during recovery? REDO applies changes from committed transactions to ensure durability. UNDO rolls back uncommitted transactions to ensure atomicity.

5. Why is strict 2PL preferred over basic 2PL? Strict 2PL holds all locks until commit, preventing cascading aborts and ensuring recoverable schedules.

Challenge

Implement a simple lock manager that supports shared (S) and exclusive (X) locks with deadlock detection. Test it with a scenario that causes a deadlock and verify your detection works.

Real-World Task

Connect to a PostgreSQL database and run SHOW transaction_isolation;. Then run two concurrent transactions that demonstrate the difference between READ COMMITTED and REPEATABLE READ isolation levels.

Mini Project: Transaction Simulator

Build a Python simulator that creates concurrent transactions, applies 2PL or MVCC, and detects/resolves deadlocks. Measure throughput and abort rates for different concurrency protocols.

Security angle: Transaction isolation prevents race conditions that attackers could exploit to corrupt data or escalate privileges. Understanding concurrency is essential for building secure multi-user applications.

What’s Next

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.

What’s Next

Congratulations on completing this Transaction Management tutorial! Here’s where to go from here:

• Practice daily — Consistency is more important than long study sessions
• Build a project — Apply what you learned by building something real
• Explore related topics — Check out other tutorials in the same category
• Join the community — Discuss with other learners and share your progress

Remember: every expert was once a beginner. Keep coding!