Locking And Concurrency Control
summary
Explains what actually happens inside MySQL 5.6: InnoDB MVCC keeps per-row transaction IDs and undo chains so readers see a single point-in-time view while writers proceed; table-level dumps lock everything. Demonstrates deadlocks, 50 second lock wait timeouts, and that deadlock detection kills the transaction that modified the fewest rows. Covers isolation levels (read uncommitted, read committed, repeatable read, serializable), row-based vs statement-based replication, binary logging on commit, and practical retry or error-handling strategies for applications.
AI-written summary · about
chapters (43)
- 0:00Introduction
- 1:15Upcoming features
- 2:26Agenda
- 2:59Table Locks
- 6:06Backups
- 7:40mysqldump
- 9:04backup
- 10:10multiversion concurrency
- 10:39demo
- 21:21answer
- 22:16shared locking
- 22:36interactive session
- 22:49deadlock
- 23:21lockweight
- 23:59deadlock detection
- 24:23potential solutions
- 24:41isolation lock step
- 26:24scalability
- 27:27optimistic vs pessimistic concurrency
- 28:13database errors
- 29:07deadlocking
- 29:26error handling
- 31:40Atomicity
- 32:31Consistency
- 33:10Isolation
- 36:36Buffers
- 37:38Summary
- 38:01Application Responsibilities
- 38:38Internal Locks
- 38:51Multiple Servers
- 41:15Serializable
- 43:09Raw Metadata
- 47:46Repeatable Reads
- 48:15Triggers
- 50:22Internal Locking
- 51:12Memory Locking
- 51:24Pages
- 51:35Thread deadlocks
- 52:02Mutex concurrency
- 52:48Hot locks
- 53:20Lock vs Mutex
- 54:34Diagnosis
- 56:43How do locks work
related talks
45mTransactions and Concurrency Control Patterns
01h 11mMVCC unmasked
50mExplaining the PG concurrency control mechanisms using rollercoasters
39mAnatomy of Table-Level Locks in PostgreSQL
28mUnderstanding Transaction Isolation for App Developers
50mWe have multiple concurrent versions of this title trying to understand MVCC
41mConcurrency, Scalability and Transactions -- Myths and Surprises
41mTransactions: myths, surprises and opportunities