Advanced SQL Server Locking

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.

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

One Response to “Advanced SQL Server Locking”

  1. While getting Rowlock for single row in Update stmt
    , why it is having IS/IX in page and table.
    It causes the other transaction in waiting mode if different row is trying to access.

    I need suggessions ASAP

    regards
    BJ

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |