Database Transaction States

Why This Matters

Transaction states form the backbone of how databases maintain ACID properties—the guarantees that keep your data reliable even when things go wrong. You're being tested on your ability to trace a transaction's journey from start to finish, understand why certain states exist, and predict what happens when failures occur. This isn't just theoretical: every time you've used an ATM, made an online purchase, or saved a document to the cloud, transaction states were working behind the scenes.

The key insight here is that transactions don't just "succeed" or "fail"—they move through a state diagram with specific transitions and rules. Exam questions will ask you to identify which state a transaction is in given a scenario, explain what triggers state transitions, and describe recovery procedures. Don't just memorize the six states—know what conditions cause each transition and what the database system does at each stage.

---

Execution States

These states represent a transaction that's actively working. The database is performing operations, and the outcome is still undetermined.

The transaction is "in flight"—reading, writing, and making changes that aren't yet permanent.

Active

• Initial state where all transactions begin—the transaction is currently executing read/write operations against the database
• Concurrent execution is normal here; multiple transactions can be active simultaneously, managed by the concurrency control system
• Remains active until the final operation completes (moving to partially committed) or an error occurs (moving to failed)

Partially Committed

• Final operation executed, but changes aren't permanent yet—this is the critical "point of no return" decision phase
• Buffer contents still need to be written to disk; if a crash happens here, the transaction may still fail
• Transitional state that exists because writing to stable storage takes time—the system is verifying everything succeeded before committing

---

Completion States

These states represent the final outcomes of a transaction. Once here, the transaction's fate is sealed—either its changes persist forever or they're completely erased.

The database has made a decision: either the transaction's work becomes part of the permanent record, or it never happened at all.

Committed

• Changes are permanent and durable—even if the system crashes immediately after, these modifications survive (the "D" in ACID)
• Visible to other transactions once committed; this is when concurrent users can finally see the updated data
• Irreversible state—you cannot "uncommit" a transaction; any corrections require a new compensating transaction

Aborted

• All changes rolled back completely—the database is restored to its state before the transaction began (as if it never happened)
• Triggered by either explicit user/application request or system decision after detecting a failure condition
• Maintains consistency by preventing partial updates; either all operations succeed or none do (the "A" in ACID—Atomicity)

---

Failure and Termination States

These states handle error conditions and resource cleanup. They ensure the system can recover gracefully and continue processing new transactions.

When things go wrong, the database must detect the problem, clean up the mess, and free resources for future work.

Failed

• Error detected that prevents successful completion—causes include deadlocks, constraint violations, system crashes, or timeout expiration
• Automatic transition to Aborted follows; the system initiates rollback procedures to undo any partial changes
• Logged for recovery purposes; the transaction log records what went wrong to support debugging and system recovery

Terminated

• End of transaction lifecycle—reached after either Committed or Aborted state completes its work
• Resources released including locks, buffer space, and log entries that are no longer needed for recovery
• No further database impact—the transaction ID may be reused, and the system moves on to process other transactions

---

Quick Reference Table

---

Self-Check Questions

1. A transaction has executed its last SQL statement but the system crashes before the commit record is written to the log. What state was the transaction in, and what happens during recovery?

2. Compare and contrast the Committed and Aborted states. What do they have in common, and why are these the only two "real" outcomes?

3. Which two states both involve uncommitted changes but differ in whether operations are still executing? What triggers the transition between them?

4. If a deadlock is detected, trace the state transitions a transaction would follow from detection to resource release. Name each state in order.

5. An FRQ asks you to explain how transaction states support the Atomicity property of ACID. Which states are most relevant to your answer, and why?