Go to content Go to navigation Go to search

Brokenwire.NET::SQL

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!

Comment [4] - Permalink -

Decrypting Sql 2005 SPs made easy
· 2009-03-25 15:05 by Thijs Kroesbergen for Brokenwire.NET

Today I ran across a problem where I desperately needed to peek inside a stored procedure used by Team Foundation Server. The only trouble was that the stored procedure was created with the “WITH ENCRYPTION” clause appended, so I couldn’t see the source.

To decrypt the stored procedure I used a stored procedure called dbo.sp_SpDeObfuscation, which can be found here and/or downloaded here.

Please keep in mind that you need to run the SP from an DAC (Dedicated Admin Connection)

Comment [1] - Permalink -

SQL Server 2008 - RTM
· 2008-08-06 20:06 by Thijs Kroesbergen for Brokenwire.NET

Another cool product I’ve been looking forward to has been released into the wild today: SQL Server 2008 .
It is available from the MSDN subscriber site right now! Dig in, enjoy!

Now we can finally use C# as a scripting language for SSIS packages. Besides that there are a lot more exciting new features in SSIS .

Comment [1] - Permalink -

SQL Server Service Broker: cleanup
· 2008-06-05 09:26 by Thijs Kroesbergen for Brokenwire.NET

Imagine that you've been playing with the SQL Server Service broker, and you've got thousands of conversations stuck in the queue. And then you discover that there is nothing like "truncate table" possible on the queue... You can cleanup each conversation in the queue with the "end conversation with cleanup" statement.

So, a bit of searching and this is the result:

WARNING: DO NOT USE THIS ON A PRODUCTION ENVIRONMENT: the messages in the queue are lost forever!

use [YOURDB] declare @handle uniqueidentifier declare conv cursor for select conversation_handle from sys.conversation_endpoints open conv fetch next from conv into @handle while @@FETCH_STATUS = 0 Begin END Conversation @handle with cleanup fetch next from conv into @handle End close conv deallocate conv

To see how many conversations there a left:

select count(*) from sys.transmission_queue

Have fun! (I know I did)
More about the Service Broker and ending conversations soon on this channel.

UPDATE:

The really really quick 'n dirty way:

ALTER DATABASE LogistiekeMeetpuntenAdministratie WITH NEW_BROKER

Comment - Permalink -

Microsoft Sync Framework
· 2008-03-05 14:10 by Thijs Kroesbergen for Brokenwire.NET

Yesterday evening I did a presentation about the Microsoft Sync Framework. The text on the slides is mostly in Dutch, but there are some pretty pictures to entertain you as well.

I’ve made a copy of the slides available, and the code for the demos shown is also downloadable. For the Ado sync demo you’ll need a copy of the Northwind database on your SQL 2005 instance, a database backup is included in the zip. The file sync only requires a source and a destination folder. Both projects will build in Visual Studio 2008, after you’ve installed the Sync Framework bits.

Please leave me a comment if you attended presentation (and tell me if you liked it)!

Comment - Permalink -