Go to content Go to navigation Go to search

Brokenwire.NET::Programming

The madness of “exclusive” row locks
· 2009-06-24 15:19 by Thijs Kroesbergen for Brokenwire.NET

Yesterday I discovered some really weird behavior of SQL Server. I had a case where I could read a record that was exclusively locked by someone else. Considering the word “exclusive” you would expect that when one transaction has an exclusive rowlock an other transaction would be unable to read that same row. But there is one specific case where this isn’t true. In that case it is possible to read a record that is locked exclusively by someone else.

It took me (together with a colleague) a lot of time to finally find out what was happening.

To reproduce this behavior you need a test-table with some random data in it.

CREATE TABLE [MyTable]
 ([Col1] bigint PRIMARY KEY CLUSTERED, [Col2] bigint)
INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (1,10)
INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (2,20)
INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (3,30)
INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (4,40)
INSERT INTO [MyTable] ([Col1], [Col2]) VALUES (5,50)

You can put this table in any database, as long as it doesn’t have snapshot isolation turned on. The recovery model for your database doesn’t matter.

Now let’s run some queries and see what happens. To be able to test this properly you should run two different session against this table. To be able to hold (and see) the locks that used you need to start a transaction and run some statements, but don’t complete the transaction (yet).

First in the first window of the Query Analyzer (which I’ll refer to as session 1) select one row from the table and request an exclusive rowlock on it with the XLOCK and ROWLOCK table hints.

session 1:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT Col1 FROM [MyTable] WITH (XLOCK, ROWLOCK) WHERE [Col1] = 3

To verify the locks that where put into place you can run sp_locks to check the locks that are granted to the connection (spid) that you used to perform the actions for session 1.

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
56     21     69575286    1      PAG  1:41                             IX       GRANT
56     21     69575286    1      KEY  (030075275214)                   X        GRANT
56     21     69575286    0      TAB                                   IX       GRANT

As you can see there is an “X” (exclusive) lock on the first key of this table. (The other locks are “IX” (intentional)). Now let’s move to session 2 and see if we can we retrieve that record.

session 2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT Col1 FROM [MyTable] WHERE [Col1] = 3

I did expected this statement to just “hang” while waiting for the record to come available. But I was surprised to see that the record could be retrieved without a problem. Also, sp_locks doesn’t show any additional locks for this statement, not even a shared lock!

If you rollback session 2 (to clear everything that might have happened) and retry it with the HOLDLOCK table hint you do get the expected behavior, because the select in session 2 now will wait for the transaction in session 1 to complete.

To understand what is happening here you should remember one of the rules of read committed data access. This rule says that you can read any row as long as it’s in a committed state. The row we’re trying to read here is in a “clean” state (it’s not marked “dirty” by the system). In this case the optimizer decides that it doesn’t hurt to retrieve it via the index without checking for locks. So your table doesn’t even need a primary key, a long as you have an index containing the requested data the rowlocks may be skipped at will.

So if the locked record has not changed and the data for requested columns is stored in an index and you are working from an READ COMMITTED isolation level then the exclusive lock is possibly not honored.

One possible workaround is to add a “HOLDLOCK” table hint to the select in session 2. Alternatively you can actually update the record to have it exclusively locked (and marked “dirty”) in session 1. The last possibility is to lock an entire page instead of just one row by using the PAGLOCK hint. Exclusive page locks do prevent all other readers for the rows in that page.

There’s a thread on http://social.msdn.microsoft.com/ posted by someone who has observed the same weird locking behavior. A Microsoft employee responded with:

Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design.

Perhaps the worst thing about all this is that this behavior cannot be found easily in the Books Online. A small note somewhere in the section about table hints would have be nice. There are some hints in KB324417 (which applies to Sql Server 2000). Combine all that with the fact that the optimizer may choose to do this at will, you’ll have a very hard to find bug in your sql code.

Conclusion:

It took me a lot of time to find out what was going on here. So remember kids: a SELECT with XLOCK and ROWLOCK hints doesn’t mean that you are the only one who can read those rows!

Permalink -

  1. You can’t really tell that a read was fully successful until the transaction in which it is contained is commited. You might be able to read something until something that happens in another transaction would make things inconsistent. Say you have two open transactions (isolation level read committed) and both have read the same row. If one of the transactions now tries to update the row, this update will hang and will dependent on the fate of the other transaction. If the other transaction is rolled back, it will go through. If the other transaction commits without updating, the update will go through. If the other transaction also tries to update, a deadlock occurs and one of the transactions will be chosen as a deadlock victim and is rolled back. In effect, the read in the victimized transaction is undone and no inconsistent data will be visible.
    It is an important performance optimization to allow concurrent reads by default.
    If you do not want this, have both reads use the with (xlock, rowlock) locking hint. Now the second read will wait until the first transaction either commits or is rolled back.


    Erwyn van der Meer    2009-06-24 21:21    #
  2. You can compare this behavior with the lock statement in C#. It only works if all threads use the lock statement to protect the shared resource. Don’t take this analogy too far. Even by default SQL Server has a stronger consistency guarantee than C#.


    Erwyn van der Meer    2009-06-24 21:30    #
  3. I just got bitten by this one again today. Whey you have two SELECT FROM WITH (XLOCK, ROWLOCK) statements hitting the same table, but both via different indexes they will not block each other! (Both in different transactions) The lock is placed on the index, not on the table. Therefore the two selects won’t “see” each others locks.


    thijs    2010-04-08 16:54    #