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.

Answer 2

It's not a caveat, it's a misunderstanding on what happens in SELECT.

A mere SELECT does not asks for Shared locks if the pages do not contain dirty data, and thus is not blocked by XLOCK.

To be blocked by XLOCK, you need to run in REPEATABLE READ isolation level. Two things can trigger that:

1. Modifying data, through INSERT/UPDATE/DELETE. The table updated does not have to be the one the XLOCK is on.
2. Explicitly asking for REPEATABLE READ through transaction isolation level or table hint.

Answer 3

based on the comments in @Martin's answer, here is a little script (run the different parts in different SSMS windows to test the lock preventing a SELECT:

--
--how to lock/block a SELECT as well as UPDATE/DELETE on a particular row
--

--drop table MyTable
--set up table to test with
CREATE TABLE MyTable (RowID int primary key clustered
                     ,RowValue int unique nonclustered not null) 

--populate test data
;WITH InsertData AS
(
    SELECT 4321 AS Number
    UNION ALL
    SELECT Number+1
        FROM InsertData
        WHERE Number<9322
)
INSERT MyTable
        (RowID,RowValue)
    SELECT
        Number, 98765-Number
        FROM InsertData
        ORDER BY Number
    OPTION (MAXRECURSION 5001)

-----------------------------------------------------------------------------
-- #1
--OPEN A NEW SSMS window and run this
--
--create lock to block select/insert/update/delete
DECLARE @ID int

BEGIN TRANSACTION

SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822
PRINT @ID

--COMMIT  --<<<only run the commit when you want to release the lock
          --<<<adfter opening the other new windows and running the SQL in them



-----------------------------------------------------------------------------
-- #2
--OPEN A NEW SSMS window and run this
--
--shows how a select must wait for the lock to be released
--I couldn't get SSMS to output any text while in the trnasaction, even though
--it was completing those commands (possibly buffering them?) so look at the
--time to see that the statements were executing, and the SELECT...WHERE RowID=6822
--was what was where this script is blocked and waiting
SELECT GETDATE() AS [start of run]
SELECT '1 of 2, will select row',* FROM MyTable Where RowID=6822
go
DECLARE @SumValue int
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GETDATE() AS [before transaction, shouldn't be nuch difference]
BEGIN TRANSACTION
SELECT @SumValue=SUM(RowID) FROM MyTable WHERE ROWID<6000
SELECT GETDATE() AS [in transaction, shouldn't be much difference]
    , @SumValue AS SumValue
--everything to here will run immediately, but the select below will wait for the
-- lock to be removed
SELECT '2 of 2, will wait for lock',* FROM MyTable Where RowID=6822
SELECT GETDATE() AS [in transaction after lock was removed, should show a difference]
COMMIT


-----------------------------------------------------------------------------
-- #3
--OPEN A NEW SSMS window and run this
--
--show how an update must wait
UPDATE MyTable SET RowValue=1111 WHERE RowID=5000  --will run immediately
GO
UPDATE MyTable SET RowValue=1111 WHERE RowID=6822 --waits for the lock to be removed

-----------------------------------------------------------------------------
-- #4
--OPEN A NEW SSMS window and run this
--
--show how a delete must wait
DELETE MyTable WHERE RowID=5000 --will run immediately
go
DELETE MyTable WHERE RowID=6822  --waits for the lock to be removed