A Little About Locking And Isolation Levels In SQL Server
In this video, I delve into the intricacies of locks, blocking, and isolation levels in SQL Server, addressing common questions that arise from my work on store procedures for resolving blocking and deadlocking issues. I discuss various types of locks-row identifier, key or row level, page, extent, heap or b-tree, object, application, metadata, allocation unit, and database level-and how they can impact query performance. Additionally, I explore the differences between optimistic isolation levels like read committed and snapshot isolation, highlighting their advantages and limitations in real-world scenarios. With a dash of humor and a touch of Canadian charm (or so people tell me), I aim to make this complex topic accessible and engaging for anyone looking to improve their SQL Server skills.
CHAPTERS
00:00:00 - Introduction
00:00:30 - Locks and Blocking Overview
00:01:00 - Optimistic vs Pessimistic Isolation Levels
00:01:24 - Snapshot Isolation Level
00:01:50 - Types of Locks in SQL Server
00:02:19 - Key and Row Locks
00:02:26 - Page, Extent, and Object Locks
00:03:06 - Extent Locking
00:03:24 - Metadata Contention in TempDB
00:04:02 - Common Metadata Locks
00:04:15 - Allocation Unit Locks
00:04:32 - Database Level Locks
00:05:06 - Query Level Locks and Hints
00:05:28 - Tab Lock Hint
00:05:37 - Lock Modes Overview
00:06:18 - Isolation Levels in SQL Server
00:07:00 - Impact of Indexes on Locking
00:09:00 - Summary and Conclusions
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━