Understanding Database Transactions
1. Understanding Transaction Lifecycle
BEGIN ──► ACTIVE ──statements──► PARTIALLY COMMITTED ──fsync──► COMMITTED
│ │
│ ▼
│ FAILED ──► ABORTED ──► TERMINATED
└──ROLLBACK──────────────┘
| State | Meaning |
|---|---|
| Active | Executing statements |
| Partially Committed | Last stmt done, awaiting durable commit |
| Committed | Changes durable; locks released |
| Failed | Error occurred; awaiting rollback |
| Aborted | Rolled back; DB at pre-tx state |
2. Understanding Atomicity
| Mechanism | Detail |
|---|---|
| Undo log | Reverse operations on rollback |
| Shadow paging | Write to copy; swap pointer atomically |
| MVCC | New row version invisible until commit |
| Crash recovery | Uncommitted txs rolled back at startup |
3. Understanding Consistency
| Enforced By | Example |
|---|---|
| Constraints | PK, FK, CHECK, NOT NULL |
| Triggers | Custom invariants |
| Application logic | Business rules outside schema |
| Isolation | Concurrent writes don't break invariants |
4. Understanding Isolation
| Technique | Detail |
|---|---|
| Two-Phase Locking (2PL) | Acquire locks, then release; strict 2PL holds until commit |
| MVCC | Each tx sees a snapshot; writes create new versions |
| SSI (PG) | Detects unsafe read-write dependencies; aborts one |
| Timestamp ordering | Order operations by tx start time |
5. Understanding Durability
| Mechanism | Detail |
|---|---|
| Write-Ahead Log (WAL) | Log first, then apply |
| fsync on commit | Force log to disk before ack |
| Group commit | Batch fsyncs for throughput |
| Replication | Sync replica before ack (synchronous_commit) |
| Checkpoints | Flush dirty pages, advance log |
6. Understanding Transaction Logs
| Component | Purpose |
|---|---|
| WAL / Redo Log | Sequential record of changes |
| Undo Log | Pre-images for rollback / MVCC |
| LSN | Log sequence number (monotonic position) |
| Archive | Old WAL files retained for PITR |
| Replication stream | WAL shipped to replicas |
7. Handling Long-Running Transactions
| Problem | Mitigation |
|---|---|
| Bloat (PG) | Vacuum can't reclaim dead tuples → split into batches |
| Lock holding | Avoid SELECT FOR UPDATE in long txs |
| Replication lag | Long tx delays replay → use chunked work |
| Monitoring | pg_stat_activity WHERE xact_start < now() - interval '5min' |
8. Using Distributed Transactions
| Protocol | Detail |
|---|---|
| Two-Phase Commit (2PC) | Prepare → Commit; blocking on coordinator failure |
| XA | Open standard for 2PC across resource managers |
| Three-Phase Commit (3PC) | Non-blocking variant (rarely deployed) |
| Paxos / Raft | Consensus protocols (CockroachDB, Spanner, etcd) |
| Sagas | Sequence of local txs + compensations |
9. Implementing Compensating Transactions
| Aspect | Detail |
|---|---|
| Saga pattern | Series of local txs; each has an inverse |
| Orchestrated | Central coordinator (e.g., Temporal, Camunda) |
| Choreographed | Event-driven, each service reacts |
| Idempotency | Compensations must be idempotent & retryable |
10. Optimizing Transaction Performance
| Technique | Detail |
|---|---|
| Keep txs short | Less lock contention, less WAL |
| Batch writes | One tx for many rows vs N txs |
| Right isolation | READ COMMITTED unless need higher |
| Async commit | Trade durability window for latency |
| Group commit | Server-side coalesces fsyncs |