SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 8. Locking, Blocking, and Concurrency

SQL Server uses locks to support transaction consistency and data isolation requirements. Locks prevent multiple active transactions from updating the same data; they reduce, or even eliminate, data consistency phenomena such as dirty, nonrepeatable, and phantom reads (more on these later).

SQL Server’s concurrency model may look confusing on the surface, but it’s quite logical and easy to understand after you grasp the basics. This chapter provides an overview of how the model works internally and gives guidelines on how to troubleshoot blocking and deadlocks.

I will start with an overview of SQL Server’s major lock types and how they behave at different transaction isolation levels. I will explain why blocking and deadlocks occur and how to deal with them. Next, I will cover other common aspects of locking, such as lock escalation and optimistic concurrency. Finally, I will discuss locking-related wait types and typical troubleshooting strategies.

Before I begin, though, a word of caution: this chapter focuses on locking behavior in classic disk-based B-Tree tables. It does not touch on updatable columnstore indexes, where locking behaves slightly differently due to their more complex internal structure. Nor does it cover In-Memory OLTP, where the concurrency model behaves entirely differently. Very little of this chapter’s content can be applied to memory-optimized tables.

Indeed, understanding and troubleshooting SQL Server locking ...