Transactions & ACID Properties
A Transaction is a single logical unit of work. It can be a simple SQL query or a complex series of updates — like transferring money between bank accounts.
The DBMS ensures reliability through four properties known as ACID.
Learning Objectives
After completing this chapter, you will be able to:
- Define a transaction and its states.
- Explain all four ACID properties with examples.
- Differentiate between commit, rollback, and savepoint.
- Understand transaction states (Active → Committed/Aborted).
- Identify which DBMS component handles each ACID property.
- Answer transaction interview questions.
What is a Transaction?
A Transaction is a sequence of database operations treated as a single logical unit.
Example: Bank Transfer (T)
T: Read(A) → A = A - 100 → Write(A) → Read(B) → B = B + 100 → Write(B)This transaction has two operations — debit A, credit B. Either BOTH succeed, or NEITHER happens.
Transaction States
┌──────────┐
│ Active │
└────┬─────┘
│
┌────────┴────────┐
▼ ▼
┌──────────────┐ ┌──────────┐
│ Partially │ │ Failed │
│ Committed │ └────┬─────┘
└──────┬───────┘ │
│ │
▼ ▼
┌──────────┐ ┌──────────┐
│ Committed│ │ Aborted │
└──────────┘ └──────────┘| State | Description |
|---|---|
| Active | Transaction is executing |
| Partially Committed | After the last statement executes |
| Committed | Changes are permanent (Durable) |
| Failed | Normal execution can no longer proceed |
| Aborted | Rolled back to previous state |
The ACID Properties
1. Atomicity — “All or Nothing”
Either the entire transaction completes, or none of it does.
Who handles it: Transaction Manager (via logs — Undo).
Example: Bank transfer. If the system crashes after debiting A but before crediting B, the DBMS rolls back the debit. Your money is not lost.
2. Consistency — “Valid State to Valid State”
The database must satisfy all constraints before and after the transaction.
Who handles it: Application programmers + DBMS constraints.
Example: If a CHECK constraint says Age > 0, no transaction can leave a row with Age = -5. The programmer must ensure the transaction preserves business rules; the DBMS enforces schema constraints.
3. Isolation — “Concurrent Transactions Don’t Interfere”
Multiple transactions executing simultaneously should produce the same result as if they ran serially (one after another).
Who handles it: Concurrency Control Manager (locking, MVCC).
Example: Two users booking the last flight seat. Without isolation, both might succeed in booking. With isolation, one waits, and the other gets the seat.
4. Durability — “Committed Data is Permanent”
Once a transaction commits, its changes survive system crashes.
Who handles it: Recovery Manager (via logs — Redo).
Example: After the bank confirms your transfer, a power failure occurs. When the system restarts, the transfer is still there. Durability guarantees this.
ACID Summary Table
| Property | Meaning | Handled By | Real-World Example |
|---|---|---|---|
| Atomicity | All or nothing | Transaction Manager (Undo log) | Bank transfer — both accounts update or neither |
| Consistency | Valid state → Valid state | Programmer + Constraints | Age > 0 always enforced |
| Isolation | Concurrent = Serial | Concurrency Control (Locks, MVCC) | Two users booking last seat |
| Durability | Committed = Permanent | Recovery Manager (Redo log) | Data survives power failure |
Transaction Control Commands
| Command | Effect |
|---|---|
| BEGIN | Start a new transaction |
| COMMIT | Save all changes permanently |
| ROLLBACK | Undo all changes since BEGIN |
| SAVEPOINT | Set a rollback point within a transaction |
| ROLLBACK TO SAVEPOINT | Undo changes back to the savepoint |
Example:
BEGIN;
UPDATE Accounts SET Balance = Balance - 1000 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE ID = 2;
COMMIT; -- Both succeed, or neitherInterview Deep Dive
Q: How does a DBMS achieve Atomicity?
A: By using Logs. Every change is recorded in a log file before it happens (Write-Ahead Logging). If a transaction fails halfway, the DBMS uses the log to “Undo” the partial changes, restoring the database to its pre-transaction state.
Q: What is Serializability?
A: A concurrent schedule is serializable if its final result is equivalent to SOME serial schedule (transactions executed one after another). This ensures that even with concurrent users, the final result is correct — as if transactions ran sequentially.
Q: If data is in RAM but not on Disk, is it Durable?
A: No. Durability requires data to be written to non-volatile storage (Disk/SSD). This is why DBMS uses Write-Ahead Logging — the log is flushed to disk BEFORE the commit is acknowledged. If the power fails after the log is written but before the data page is written, the DBMS can Redo the change from the log.
Q: What is the difference between COMMIT and ROLLBACK?
A: COMMIT finalizes a transaction — all changes are made permanent and visible to other transactions. ROLLBACK undoes all changes made during the transaction — the database returns to the state before the transaction began. COMMIT is success; ROLLBACK is failure.
Key Takeaways
- A Transaction is a logical unit of work with ACID properties.
- Atomicity — all or nothing (handled via Undo logs).
- Consistency — valid state to valid state (programmer + constraints).
- Isolation — concurrent transactions don’t interfere (concurrency control).
- Durability — committed data survives crashes (Redo logs, WAL).
- Transactions have states: Active → Partially Committed → Committed, or Active → Failed → Aborted.
- COMMIT saves changes; ROLLBACK undoes them; SAVEPOINT provides granular rollback points.
Premium Content
Unlock Transactions & ACID Properties and all premium lessons with a subscription.
From ₹199.99/year — See plans