To satisfy this query, SQL Server can’t use the index — the index doesn’t cover the RegionDescription column. So SQL Server will use a full table scan to find the row to change. Once it finds the row, it will upgrade the update lock on that row to an exclusive lock. Just to make sure, run SP_LOCK in another Query Analyzer window. There should be an exclusive RID lock on the correct object. In the same window that you ran SP_LOCK, you can try issuing a select, like this:
Unless you’ve specified READPAST, or set the transaction isolation level to read uncommitted, you should be put “on hold” waiting for the first connection to release its lock. This is just as you would expect. But, here’s the trick:
This time, you won’t be kept waiting. If you’re like me, you like looking at execution plans, and the execution plan to this query explains why we don’t have to wait.
As you can see, SQL Server chooses an index scan to get the data it needs to answer your query. And since you asked for information that could be answered by reading an index, it doesn’t need to read the data on the heap. This kind of query is called a covering query.
Two criteria exist for this to happen. The first is that all the data needed by the query is contained in the index. Remember that if the table has a clustered index, all non-clustered indexes will have the index columns and the clustered index columns in it.
The second is that the original UPDATE mustn’t change any of the columns contained in the index. If the index is changed, it must be exclusively locked and this trick won’t work.
Extended Lock Capability Table
The tables currently available in BOL and MSDN aren’t very comprehensive when it comes to which locks are compatible with each other. Because of this, I have included a more comprehensive table below. I hope you find it useful.
Conclusion
I did find the elusive intent update lock, and a lot more while searching for it. Locks and locking behavior are poorly documented in BOL, which has made this research a lot harder than it had to be. I’ve learned a lot writing this article, and I hope I’ve shared a little of that knowledge with you.
BIO
Andrés Taylor tries to give equal amounts of attention to his favorite pastimes: SQL Server studying, Brazilian Jiu-Jitsu, and his beautiful wife Mimi. He holds 20 MCPs, is a MCT and works at Dotway as a .NET/SQL Server consultant. His blog is at: http://www.taylor.se/blog/
Published with the express written permission of the author. Copyright 2003.
]]>