ACID in SQL Server: what it is, how it works, and how to live more adventurously.

on YouTube ↗52m

" summary

Covers ACID in SQL Server with hands-on demos: shows read uncommitted (NOLOCK) producing dirty reads, contrasts read committed, repeatable read and serializable range locks with blocking and phantom examples, and explains snapshot vs RCSI and update-conflict behavior. Reproduces a surprising merge deadlock caused by shared-range lock upgrades and resolves it by removing HOLDLOCK (falling back to read committed). Ends with how transaction log hardening, delayed durability, and tempdb versioning affect durability and recovery.

AI-written summary · about

" chapters (13)

1. 0:00ACID overview
2. 1:12ACID in SQL Server
3. 4:28Isolation levels
4. 10:50Read committed isolation
5. 11:33Repeatable read and phantoms
6. 16:13Serializable isolation
7. 24:06Isolation trade-offs
8. 25:27Deadlocks in SQL Server
9. 33:18Deadlocks with merge
10. 41:47Snapshot isolation
11. 47:04Durability and transaction log mechanics
12. 49:10Delayed durability
13. 50:36Key takeaways

" related talks

• 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.Daniel Hutmacher · SQLBits 2022
• 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.Daniel Hutmacher · SQLBits 2019
• 38mThe I in ACIDLætitia Avrot · PGConf NYC 2022
• 52mTransactions: Myths, Surprises and OpportunitiesMartin Kleppmann · Code Mesh 2015
• 41mThe I in ACIDLaetitia Avrot · AFUP Day 2020 Lyon
• 47mMaximizing Database Performance With Isolation LevelsBob Pusateri · SQLBits 2022
• 01mDon't Drop ACID - Transactions in Distributed NoSQLMatthew D Groves · Data Love 2021
• 58mDon’t Drop ACID - Transactions in Distributed NoSQLMatthew Groves · NDC Oslo 2020