SQL Server, the misleading XLOCK & optimizations

Question

From some recent testing and reading I've done, it seems the "X" (exclusive) name part of XLOCK is misleading. It in fact doesn't lock any more than UPDLOCK. If it were exclusive, it would prevent external SELECTs, which it doesn't.

I cannot see either from reading or from testing and difference between the two.

The only time XLOCK creates an exclusive lock is when used with TABLOCK. My first question is "why only at this granularity?"

Further, I came across a blog that states the following:

However, watch out for XLOCK hint. SQL Server will effectively ignore XLOCK hint! There's an optimization where SQL Server check whether the data has changed since the oldest open transaction. If not, then an xlock is ignored. This makes xlock hints basically useless and should be avoided.

Has anyone run across this phenomenon?

Based on what I'm seeing, it seems this hint should be ignored.

Answer 1

Exclusivity of X locks vs U locks

In the lock compatibility matrix below it can be seen that the X lock is only compatible with the schema stability and Insert Range-Null lock types. U is compatible with the following additional shared lock types S/IS/RS-S/RI-S/RX-S

lock compatibility matrix http://i.msdn.microsoft.com/ms186396.LockConflictTable(en-us,SQL.105).gif

Granularity of X locks

These are taken out fine at all levels. The script and profiler trace below demonstrates them being successfully taken out at row level.

CREATE TABLE test_table (id int identity(1,1) primary key, col char(40))

INSERT INTO test_table
SELECT NEWID() FROM sys.objects

select * from test_table with (rowlock,XLOCK) where id=10

But rows can still be read!

It turns out that at read committed isolation level SQL Server will not always take out S locks, it will skip this step if there is no risk of reading uncommitted data without them. This means that there is no guarantee of a lock conflict ever occurring.

However if the initial select is with (paglock,XLOCK) then this will stop the reading transaction as the X lock on the page will block the IS page lock that will always be needed by the reader. This will of course have an impact on concurrency.

Other Caveats

Even if you lock the row/page this does not mean that you block all accesses to that row in the table. A lock on a row in the clustered index will not prevent queries reading data from the corresponding row in a covering non clustered index.