add-ons:
Deadlocks and how to prevent them
Optimistic Vs. Pessimistic Locking
SSI (Serializable Snapshot Isolation) and MVCC (Multi-Version Concurrency Control)
| Pain you’d otherwise debug | Transaction guarantee that eliminates it | Field story |
|---|---|---|
| App crashes after 3 of 5 writes ⇒ half-written data | Atomicity – “all or nothing” rollback | Airline seat sold twice when server rebooted mid-write |
| Two sessions read same row, both write updates ⇒ counter wrong | Isolation – DB prevents racy interleavings (or aborts one) | Double-spend in wallet micro-service |
| Power cut after commit acknowledgement | Durability – data lives on (disk or replicas) | Clearing systems rely on it |
| Data in app tables suddenly violates its own rules | Consistency (app rule) – you combine A,I,D to keep invariants | Credits ≠ Debits; auditors visit |
<aside> 💡
Instead of locking rows, MVCC gives each transaction a consistent snapshot of the data at the time it starts. (used for READ_COMMITED, REPEATABLE_READ [Snapshot_Isolation])
Serializable Snapshot Isolation (SSI) is a way to achieve serializability — the strongest isolation level — without locking every read (used for SERIALIZABLE isolation level). If two transactions could violate serializability, one is aborted at commit.
</aside>
| Name & emoji | What happens | Blocked by… |
|---|---|---|
| 🧼 Dirty read | Tx B reads uncommitted writes of Tx A | Read-committed |
| 🖍 Dirty write | Two txns overwrite same row before either commits | RC & stronger |
| 🔀 Lost update | Two read-modify-writes; later write clobbers earlier | Need explicit lock/atomic op or Serializable/SSI |
| 🤹 Read skew / non-repeatable | Tx sees row A before change, row B after | Snapshot isolation |
| 🎲 Write skew | Each txn checks a condition then writes; together they violate rule (2 doctors off-call) | Only Serializable |
| 👻 Phantom | Tx reads “no rows match”; concurrent insert makes that false | Serializable via predicate / range locks or SSI |