Maximizing Database Performance With Isolation Levels

on YouTube ↗47m

" summary

Shows isolation levels on SQL Server and Azure SQL DB using a 41,437-row ZIP code demo, Extended Events and dbcc user options to prove behavior. WITH (NOLOCK) (read uncommitted) returns volatile, impossible states; default read committed produces correct counts only 60/1000 times in a moving-row test (371 undercounts, 569 overcounts). Enabling read committed snapshot via ALTER DATABASE fixes that test to 100% by using the tempdb version store. Serializable uses key-range locks; 3960 update conflict appears under snapshot on concurrent updates.

AI-written summary · about

" chapters (13)

1. 0:00Introduction to concurrency
2. 1:21Concurrency conflicts
3. 3:56Locking and deadlock
4. 7:05Pessimistic vs optimistic
5. 8:04SQL Server isolation levels
6. 13:54Read uncommitted isolation
7. 19:34Read committed isolation
8. 26:58Repeatable read isolation
9. 29:23Serializable isolation
10. 31:54Optimistic concurrency
11. 34:21Read committed snapshot
12. 37:29Snapshot isolation
13. 44:38Cloud considerations and Q&A

" related talks

• 47mMaximizing Database Performance With Isolation LevelsBob Pusateri · SQLBits 2022
• 47mMaximizing Database Performance With Isolation LevelsBob Pusateri · SQLBits 2019
• 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.Daniel Hutmacher · SQLBits 2019
• 52mMy application is fully concurrent and async. What about the database?Davide Mauri · SQLBits 2023
• 52mACID in SQL Server: what it is, how it works, and how to live more adventurously.Daniel Hutmacher · SQLBits 2022
• 51mNext Level Scalability on SQL Server 2022Ajay Jagannathan, David Pless · SQLBits 2023
• 50mAchieve peak performance and availability for your SQL Server and Azure SQL workloads with core engiAjay Jagannathan · SQLBits 2022
• 29mPutting Rails in a corner: Understanding database isolationEmil Ong · RailsConf 2018