Advanced SQL Server Locking
I thought I knew SQL Server pretty well. I’ve been using the product for more than 6 years now, and I like to know my tools from the inside out.
While teaching a SQL Server programming course, I noticed that the Microsoft material presented a lock compatibility table. The same table is presented at MSDN.
Looking at this table I wondered — is there no Intent Update lock? This led me to research locking. This article is the result of the research. I wrote this article with a specific reader in mind — someone who understands isolation levels, intent locks, deadlocks, and lock granularity. If you don’t have a good background in these areas, you might want to read up on these subjects before reading this article.
My hope is to expand your understanding of SQL Server locking, and maybe teach you a few tricks you can use when programming SQL Server.
I have to say that you can happily work with SQL Server for a long time, and never need to know how it locks its resources, and still produce high-quality code and database schemas. But, if you’re like me, and like to know the inside of things, or you work with a system that requires that little extra in performance, I might teach you something useful.
I’m sure you’ve heard of deadlocks where spid X holds a lock on resource A and waits to lock resource B, while spid Y holds the lock on B and is waiting for resource A to be freed. If not, surf over to MSDN and read more on the topic.
Well, guess what, there are more types of deadlocks. Consider this situation: Spid X has a shared lock on resource A, and so has spid Y. This is no problem, since two shared locks are compatible with each other. Now X wants to convert the shared lock to an exclusive lock to update the resource. X has to wait for Y to release the shared lock to do this, and while X waits, Y decides to do the same and convert its lock to an exclusive lock. X is waiting for Y, and Y is waiting for X. Deadlock. These kinds of deadlocks are called conversion deadlocks.
This is a fairly common situation, and to avoid it, update locks were introduced. Update locks allow a connection to read a resource, and at the same time, advertise its intention to lock it at some later point in time in order to edit the resource. An issue with update locks is that SQL Server doesn’t know in advance if a transaction will want to convert a shared lock to an exclusive lock, unless it’s a single SQL statement, like an UPDATE statement, that first has to read the table before it can update it. To use update locks in a SELECT-statement, you must explicitly specify that you want SQL Server to use them, and you do this with the lock hint UPDLOCK.
I like showing code examples of what I’m talking about, so here goes. In one window in QA, I run this batch:
Note that I open a transaction, but I don’t close it. This is to hold on to the locks inside the transaction.
If another connection would try to get another update lock on the same row, it would be put on hold until the first connection releases its lock — two update locks on the same resource are incompatible.
The result from SP_LOCK, showing only the relevant rows and columns and looks like this:
As expected, the key we are locking, OrderId, is locked with an Update Lock. The strange value in the Resource column (89003da47679) is the hashed value of our key, 10633. Internally, SQL Server uses a hash table as the lock table, and this is what we see here.
The page containing this row is, also as expected, locked with an Intent Update lock. The value in the resource column (1:242) tells us that the page is placed on the first file of the database, and is the page #242 in that file. And lastly, not as expected, SQL Server has taken an IX lock on the table level. SQL Server will never use U/IU locks on the table level, so we’ll only see X/IX locks on that level.
When you do an update with a where clause, SQL Server has to read the table and/or any indexes to determine which rows to change. Before it reads anything from the table/index, SQL Server first locks the object it’s accessing. Since SQL Server knows you’ve issued an update, it will choose an update lock, and not a shared lock. This is to prevent the kind of deadlock I talked about earlier –- a conversion deadlock.
When SQL Server has found the row/rows to change, it will convert the update lock to an exclusive lock on the row — on a RID if it’s a heap, or on the KEY on a clustered index table. This means that normally you won’t see the update locks ‘when executing an UPDATE-statement’–- they are converted almost immediately to exclusive locks.
But, of course, there are some exceptions to this rule. If SQL Server uses one index to find the rows, it will lock the index pages and the index keys with update locks. If the update doesn’t change any of the columns contained in the index, the update locks won’t be converted to exclusive locks. I’ll give you an example:
Region is a heap table with a non-clustered primary key unique index on RegionId. So, to fulfill this query, SQL Server will traverse the index on RegionId, locking index pages and index keys with update locks on the way. Finally, it will find the row to change. Since the update statement doesn’t change RegionId, it doesn’t have to take an exclusive lock on the index. The output from SP_LOCK looks like this:
We see an IX lock on the table, and an X lock on the RID we’re working on. The KEY lock we see is on the RegionId index. This is evident on the Indid column. As you can see, we still have an update lock on the index. This is one of the few times you’ll see an update lock live.
And, to finish it off, we have two page locks –- one on an index page (1:306), and one on the heap (1:300). How do I know this? Look at the Indid column. Index id 0 is the always the heap.