SQL Server Locking, Blocking and Deadlocks for Developers and DBAs

Zenler Player

Your course is loading. Hang tight.

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

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 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 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 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 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 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 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 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 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

Summary and further steps