Skip to content
Home » Transaction Management

Transaction Management

Below is a complete, exam-oriented explanation of Transaction Management — covering definitions, properties, states, schedules, concurrency control, locking, log-based recovery, checkpoints, and examples.

⭐ TRANSACTION MANAGEMENT

A transaction is a sequence of one or more database operations (read/write) performed as a single logical unit of work.
Examples of transactions:

  • Transferring money between accounts
  • Placing an order
  • Booking a ticket
  • Updating student marks

Transaction Management ensures that all database transactions are processed reliably, safely, and correctly, even in the presence of failures.

⭐ 1. Definition of Transaction

A transaction T is a sequence of operations such as:

  • read(X)
  • write(Y)
  • SQL statements

It must maintain the ACID properties.

Example transaction for fund transfer:

T1:
 read(A)
 A := A – 500
 write(A)
 read(B)
 B := B + 500
 write(B)

⭐ 2. ACID Properties of Transactions

  1. Atomicity
    • “All or nothing”
    • Entire transaction executes fully or not at all
    • Handled using undo logs
  2. Consistency
    • Transaction must transform the database from one valid state to another
    • Enforces integrity constraints
  3. Isolation
    • Concurrent transactions must not interfere
    • Achieved using:
      • Locks
      • Timestamps
      • MVCC
  4. Durability
    • Once committed, changes are permanent
    • Even if system crashes
    • Ensured using:
      • Write-Ahead Logging (WAL)
      • Checkpoints

These properties ensure reliability.

⭐ 3. States of a Transaction

A transaction moves through various states:

Active → Partially Committed → Committed → Terminated
          ↓
        Failed → Aborted → Restart/Terminate

✔ Active — executing operations

✔ Partially Committed — last statement executed

✔ Committed — changes permanently saved

✔ Failed — error/crash occurred

✔ Aborted — rolled back, database restored

✔ Terminated — end of execution

⭐ 4. Transaction Schedules

A schedule defines the order in which operations of multiple transactions execute.

✔ Serial Schedule

Transactions run one after another (no interleaving).
Always consistent, but slow.

✔ Non-Serial Schedule

Operations of transactions are interleaved.

Non-serial schedules must be equivalent to serial ones to be correct.

⭐ 5. Types of Schedule Equivalence

✔ 1. Conflict Serializability

Schedule can be converted to serial by swapping non-conflicting operations.

Conflicts occur when both transactions access same data item and at least one is a write.

✔ 2. View Serializability

Same final view as some serial schedule.
Weaker condition but harder to test.

✔ 3. Recoverable Schedules

A transaction commits only after all transactions it depends on commit.

✔ 4. Cascadeless & Strict Schedules

Prevent cascading rollbacks.

  • Strict 2PL ensures strict schedules.

⭐ 6. Concurrency Control

Concurrency Control ensures that multiple transactions can run simultaneously without violating isolation or consistency.

Main techniques:

⭐ A. Lock-Based Protocols

Locks prevent conflicts during concurrent execution.

✔ 1. Binary Locks

  • Only two states: Locked (1) or Unlocked (0)

✔ 2. Shared/Exclusive Locks (S / X locks)

  • Shared (S) → read-only; many transactions can hold
  • Exclusive (X) → read/write; only one transaction allowed

⭐ B. Two-Phase Locking (2PL)

Ensures conflict-serializability.

Two phases:

  1. Growing Phase
    • Transaction acquires locks
    • No lock release
  2. Shrinking Phase
    • Transaction releases locks
    • No new lock acquisition

Strict 2PL:

  • All locks held until commit
  • Ensures strict schedules
  • Prevents cascading aborts

⭐ C. Deadlocks in Locking

Deadlock occurs when:

T1 waits for T2's lock
T2 waits for T1's lock

Deadlock Handling:

✔ Deadlock Detection

  • Wait-for graph
  • Deadlock cycle → abort a transaction

✔ Deadlock Prevention

  • Timestamp-based rules:
    • Wait-die
    • Wound-wait

✔ Deadlock Avoidance

  • Banker’s algorithm (rare in DBMS)

⭐ D. Timestamp Ordering Protocol

Each transaction has a timestamp (TS).
Ensures serializability based on timestamps.

  • Read Timestamp (RTS)
  • Write Timestamp (WTS)

If a transaction violates ordering → it is aborted.

⭐ E. Multiversion Concurrency Control (MVCC)

Maintains multiple versions of data items.

  • Readers get snapshot (no locking).
  • Writers create new versions.

Used in PostgreSQL, Oracle, MySQL InnoDB.

Advantages:

✔ No read locks
✔ High concurrency
✔ Faster reads

⭐ 7. Recovery Management

After a crash, DB must recover using:

✔ Write-Ahead Logging (WAL)

Log is written before updating data.

Two types:

  • Undo log (for rollback)
  • Redo log (for recovery after commit)

✔ Log-Based Recovery Steps

  1. Analysis: Identify active transactions
  2. Redo: Reapply committed changes
  3. Undo: Rollback uncommitted ones

✔ Checkpoints

Periodic snapshots of system state.

  • Reduce recovery time
  • Mark point where logs before checkpoint can be ignored

⭐ 8. Commit and Rollback

✔ COMMIT

  • Ends a successful transaction
  • Writes all updates permanently

✔ ROLLBACK

  • Undo all operations
  • Restore database to previous state

⭐ 9. Example: Transaction with Locks and Logs

Suppose:

T1: read(A), write(A)
T2: read(A), write(A)

Concurrency control:

  1. T1 obtains X-lock on A
  2. T2 requests X-lock → waits
  3. T1 commits → releases lock
  4. T2 proceeds

Recovery logs:

<T1, start>
<T1, write, A, old=100, new=150>
<T1, commit>

If crash occurs after commit → redo T1.

⭐ 10. Perfect 5–6 Mark Summary

Transaction Management ensures that database transactions maintain ACID properties—Atomicity, Consistency, Isolation, and Durability.
It manages concurrency using locking, timestamp ordering, and MVCC, ensuring correct and serializable schedules.
Recovery uses write-ahead logging, checkpoints, and undo/redo to handle failures.
Thus, Transaction Management guarantees reliability, correctness, and high performance in a multi-user database environment.