Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

Question

I wrote this script without COMMIT in a new Query window (Query1):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3 

and in another query window I write this script without COMMIT (Query2):

BEGIN TRAN 
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 1 

Everything is OK, when I run Query1 and then Query2 I can select row with RegioID=1. But if I write the first query this way (Query3):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3 OR RegionID = 4

...and add RegionID = 4 in result, I can't run Query2. Why wheras Query2 result does not has intersect with Query3 I can't run it?

Answer 1

The 3rd query has an OR which most likely means that a scan is happening. The scan will be blocked by RegionID = 1

If you run this, it should run OK.

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 4

SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3

The alternative is that you still have query 1 transaction open...

Edit:

I can't reproduce it (SQL Server 2008 R2 x64 developer). I get an index seek on query 3.

However, in my first iteration I forgot a PRIMARY KEY on my table and query1 blocked query2 because it was a table scan. Which backs up my answer above mostly

Edit 2:

I can't get either Query 2 or Query 3 to block each other (no matter which runs first)

After comments:

• A scan places an XLOCK per scanned row
• OR is often non-SARGable: AND is OK. (OR has some optimisations) though)