ReadyToGo Logo ReadyToGo
Login Sign Up
› Module 7: Transactions & ACID Properties › 7.2 Transaction States & Lifecycle
M O D U L E

Module 7: Transactions & ACID Properties

The bedrock of data integrity, atomicity, consistency, and crash recovery

7.1 What is a Transaction? ACID Properties in Depth

A Transaction is a single logical unit of work consisting of one or more database operations (reads and writes). To guarantee database correctness even under hardware crashes or system failures, every enterprise DBMS adheres strictly to the ACID paradigm.

Figure 7.1: The Four ACID Guarantees of Database Transactions A - Atomicity All or Nothing • Either every operation succeeds, or the entire transaction is rolled back. • Managed via Undo Logs • Prevents partial state corruption. C - Consistency Preserves Invariants • DB transitions from one valid state to another valid state. • All integrity rules, foreign keys, & check constraints remain strictly satisfied. I - Isolation Concurrent Invisibility • Uncommitted changes from Txn A are invisible to Txn B. • Concurrent execution yields same result as serial execution. • Managed via 2PL/MVCC. D - Durability Survives System Crashes • Once a transaction commits, its updates are permanent. • Even a power outage cannot erase committed state. • Managed via WAL & Redo.

Figure 7.1: The ACID properties guarantee transactional reliability in relational database management systems.

Real-World Example: Bank Fund Transfer

Consider transferring $500 from Account A ($1000) to Account B ($200):

BEGIN TRANSACTION;
  UPDATE Accounts SET balance = balance - 500 WHERE account_id = 'A';
  -- If power cuts here, Atomicity rolls back; Account A balance remains $1000!
  UPDATE Accounts SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;
-- Once COMMIT returns success, Durability guarantees changes are flushed to disk.

Write-Ahead Logging (WAL)

DBMS engines do not synchronously flush modified data pages to disk on commit because random disk writes are slow. Instead, they use Write-Ahead Logging (WAL):

  • A log record detailing the change (old value and new value) must be written to non-volatile log storage before the corresponding database page is flushed to disk.
  • During system recovery following a crash, the ARIES recovery algorithm uses the log to REDO all actions of committed transactions and UNDO all operations of uncommitted transactions.

7.2 Transaction States & Lifecycle

During its execution lifetime, a database transaction transitions through a finite set of explicit states, moving from initialization to either successful commit or abort.

Figure 7.2: Transaction State Transition Diagram Active Executing ops Last op done Partially Committed Flushing log to disk WAL synced Committed Permanent Hardware/Logic error Failed Cannot proceed Rollback undo Aborted DB restored Terminated

Figure 7.2: Finite state machine transitions of a database transaction from initiation to termination.

State Descriptions

  • Active: Initial state where the transaction executes its sequence of READ and WRITE operations.
  • Partially Committed: Reached after the final operation has been executed, but while unwritten modifications and WAL log records are still buffered in volatile memory waiting to flush to disk.
  • Committed: Reached when all recovery log records are successfully flushed to persistent non-volatile media. The transaction cannot be undone.
  • Failed: Entered whenever a software bug, system crash, deadlock victim abort, or integrity constraint violation terminates normal execution.
  • Aborted: Reached when the database finishes applying the UNDO log, restoring all modified tables back to their clean pre-transaction state.
MODULE COMPREHENSIVE QUIZ

Module 7 Final Quiz

Test your understanding across all 2 topics of this module with interview-grade questions, tricky edge cases, and code output predictions.

Take Module 7 Final Quiz →
ReadyToGo · DBMS & SQL Study Guide
Yukti Mascot