Database Transactions
Definition
A transaction is a unit of work that must execute atomically and leave the database consistent even under concurrency and failure. The classic guarantees are ACID — Atomicity, Consistency, Isolation, Durability. Concurrency control and recovery are the mechanisms that deliver them.
Core Ideas
Concurrency control — timestamping
The DBMS assigns each transaction a globally unique timestamp (relative start time). On conflict, older transactions (smaller timestamps) get priority. It requires extra storage and system resources (transactions get stopped, rescheduled, restamped).
Deadlock prevention
Two timestamp-ordered schemes decide who waits and who is killed when a transaction requests a resource held by another:
| Wait–Die | Wound–Wait | |
|---|---|---|
| Technique | Non-preemptive | Preemptive |
| Rule | Older waits for younger; younger requesting older’s resource is killed and restarted | Older preempts (kills) younger to take the resource; younger requesting older’s resource waits |
| Aborts/rollbacks | Higher | Lower |
The killed (younger) transaction restarts later with the same timestamp (so it eventually wins and doesn’t starve).
Crash recovery
- Deferred write — buffer changes and flush to permanent storage at each checkpoint.
- Write-through — even a change committed before a crash (after the last checkpoint) is re-done on recovery, just in case.
Relationships
- Databases — parent domain
- Query Optimization — isolation and locking interact with access plans
- Distributed Consensus — distributed transactions need agreement across nodes