SQL Server Locking, Blocking and Deadlocks for Developers and DBAs

Learn to find and fix locking issues, and to avoid them in the first place.

Course Summary

Are you grappling with issues related to SQL Server or Azure SQL Database? Do you find yourself dealing with what you think are blocking problems, but aren’t quite sure what’s actually going wrong?

Often, what customers refer to as blocking issues might be a misunderstanding of locks held for extended periods or even deadlocks. These challenges are common, but with the right knowledge, you can resolve them effectively.

In this course, you’ll gain a deep understanding of how SQL Server manages transactions and locks, and get clarity on isolation levels and their appropriate usage. Whether you’re looking to expand your foundational knowledge or address specific, persistent issues, this course provides:

Expert Insights: Learn from a seasoned professional who tackles these issues on customer sites regularly.
Practical Exercises: Optional hands-on activities to apply what you’ve learned and solidify your understanding.
Interactive Quizzes: Test your knowledge and ensure you’re on the right track.

If you’re ready to move beyond basic concepts and gain practical, actionable insights into locking, blocking, and deadlocks, this course is tailored for you. Enhance your skills, resolve issues efficiently, and become a more effective developer or DBA.

Enroll now for $195 USD !

Modules and Lessons

Module 0: Getting started

Who is this course for ?
Who is Greg ?
What will I learn in this course ?
Configuring your lab environment

Module 1: Core concepts

Module introduction
What is locking?
What is blocking?
What are deadlocks?
Why does SQL Server use locks?
What are latches?
Pessimistic concurrency
Optimistic concurrency
What are isolation levels?
Core concepts quiz

Module 2: Transactions

Module introduction
What are transactions?
ACID properties
Implementing transactions in SQL Server
Processing transactions efficiently
Avoid chained mode
Avoiding network latency in transactions
Avoiding lost updates
Rowversion data type
Transactions quiz
Database setup lab

Module 3: Locking

Module introduction
Lockable resources
Shared locks
Exclusive locks
Update locks
Intent locks
Lock escalation
Limiting lock duration
Lock-related query hints
Lock-related dynamic management views (DMVs)
Locking quiz
Locking lab

Module 4: Isolation levels

Module introduction
ANSI SQL isolation levels
SQL Server isolation levels
Read committed in SQL Server
Read uncommitted in SQL Server
Repeatable read in SQL Server
Serializable in SQL Server
Controlling the isolation level
The evil of NOLOCK
Isolation-related query hints
Isolation levels quiz
Isolation levels lab

Module 5: Row versioning

Module introduction
Requirements for row versioning
Enabling snapshot isolation
Snapshot isolation level in SQL Server
Update conflicts with snapshot isolation
Limitations of snapshot isolation
Read committed snapshot (RCSI)
Row versioning quiz
Row versioning lab

Module 6: Deadlocks

Module introduction
The deadly embrace
Life before automatic deadlock detection
Deadlock detection in SQL Server
Detecting deadlocks with SQL Profiler
Detecting deadlocks with Extended Events
Detecting deadlocks with trace flags
Reading deadlock graphs
Multi-deadlock graphs
Avoiding deadlocks
Controlling deadlock priority
Implementing retry logic
Deadlocks quiz
Deadlocks lab

Module 7: Troubleshooting locking issues

Module introduction
Fix the indexing first
Techniques for troubleshooting blocking
Lock-related wait statistics
Lock-related performance counters
System health extended events
Who is active?
All blocking transactions report
Blocked process threshold
Troubleshooting locking issues quiz
Troubleshooting locking issues lab

Module 8: Locking case studies

Module introduction
Case study 1: Long running query
Case study 2: RBAR client application
Case study 3: Uncommitted transaction
Case study 4: Transaction rollback
Case study 5: Session management
Case study 6: Procedure compilation
Case study 7: Lock escalation
Locking case studies quiz

Module 9: Advanced locking concepts

Module introduction
Controlling lock escalation
Optimized locking
Lock mode conversions
Deciphering locked resource IDs
Deciphering lock requests
Row version stores
Application locks
Specialized locks
Advanced locking concepts quiz
Advanced locking concepts lab

Module 10: Next steps

Summary and further steps

Enroll now for $195 USD !