Transactions and Concurrency Control Patterns
summary
Explains concrete tradeoffs: two‑phase locking costs scale poorly while MVCC (demonstrated with PostgreSQL Xmin/Xmax MVCC demo) avoids reader‑writer blocking but requires vacuuming and can produce write skew and snapshot anomalies; contrasts Oracle, SQL Server, PostgreSQL, MySQL behaviors; shows application fixes using optimistic force‑increment versioning, versionless optimistic checks, table splits to avoid false‑positive conflicts, and how these patterns catch lost updates in multi‑request web flows where DB transactions alone fail.
AI-written summary · about
chapters (24)
- 0:00Intro
- 2:25History
- 2:51Atomicity
- 4:38Consistency
- 6:09Durability
- 8:46Isolation
- 9:33Conflicts
- 11:48Locking
- 13:11Two Phase Locking
- 15:12MVCC
- 15:39MVCCC
- 17:11Delete
- 18:24Update
- 19:11Two types of isolation
- 21:22Isolation leverage
- 23:28Phantom rate
- 23:57Reads Q
- 28:53Lexical Standards
- 29:17Reality
- 33:24Version column
- 34:57Multiple columns
- 35:44Splitting tables
- 36:06Updating tables
- 36:27Hibernate
related talks
41mTransactions: myths, surprises and opportunities
28mUnderstanding Transaction Isolation for App Developers
50mExplaining the PG concurrency control mechanisms using rollercoasters
41mConcurrency, Scalability and Transactions -- Myths and Surprises
01h 11mMVCC unmasked
49mConcurrency Deep-Dive
58mLocking And Concurrency Control
39mAnatomy of Table-Level Locks in PostgreSQL