ACID in SQL Server: what it is, how it works, and how to live more adventurously.
summary
Covers how SQL Server implements ACID with concrete demos: isolation levels from read uncommitted to serializable, a banking transfer demo showing dirty reads and blocking, and how merge with HOLDLOCK causes unexpected shared range locks and deadlocks even for distinct keys — solved by switching to read committed. Explains deadlock detection picking a victim (error 1205) and retry strategy, snapshot/RCSI row-versioning and update conflicts, delayed durability boosting throughput at risk of data loss, and tempdb growth under versioning.
AI-written summary · about
chapters (13)
- 0:00ACID overview
- 1:12ACID in SQL Server
- 4:28Isolation levels
- 10:50Read committed isolation
- 11:33Repeatable read and phantoms
- 16:13Serializable isolation
- 24:06Isolation trade-offs
- 25:27Deadlocks in SQL Server
- 33:18Deadlocks with merge
- 41:47Snapshot isolation
- 47:04Durability and transaction log mechanics
- 49:10Delayed durability
- 50:36Key takeaways
related talks
52mACID in SQL Server: what it is, how it works, and how to live more adventurously.- 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.
52mTransactions: Myths, Surprises and Opportunities
38mThe I in ACID
41mThe I in ACID
01mDon't Drop ACID - Transactions in Distributed NoSQL
58mDon’t Drop ACID - Transactions in Distributed NoSQL
51mThe 10 things every developer must absolutely know about Azure SQL