Locking and Blocking – Page 2

I talked about tools can be used for analyzing locking behaviors in prevous post. In this post, I am going to describe shared lock and intent shared locks and how to extract index key hash value and index from the returning value of Extended Event. Shared lock and Intent Shared lock are used while reading the data from tables. They called “shared” because if there are one record holding a S lock on an record, other sessions can hold the S lock on the same record. You may think this lock is useless. That’s true if SQL Server only supports those 2 type of locks. In my later post, more lock types will come. The S lock conflicts with Exclusive(X) locks. While an Shared lock is holding, an Exclusive lock will wait. Think about this question, will you allow others to drop a database you connected? No, because there is an Shared lock on the database when a connection is on it.

Continue reading “Locking and Blocking (2) – Shared Locks and Lock Resource” →

I am going to start a series of posts to talk about Locking and blocking in SQL Server, which is one of my favorate topics. As a SQL Server developer, many professionals are more focusing on programming languages, database designs, features (like Service Broker), writting SQLs to effectively manipulate data and process designs. They’re all correct. But one more thing is quite frequently getting neglected is locking. Especially after SQL Server 2005, Snapshot isolatioin level is used more frequent than others. It seems like SQL Server will never locked. Actually, that’s not the fact. Nothing has been changed from locking perspective before and after version 2005. After having a fully understanding of locking and blocking mechanism, you will find out you can easily write a high concurrency application, prevent excessive blocking, and design more sophisticated concurrent SQL process controls.

Continue reading “Locking and Blocking (1) – Tools” →