Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK?

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).

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).

Thanks. Dies it means that "OR" , "AND" operand cause table scan? Every where in every query?

Why Table scan cause table lock? Is it NORMAL? – Nima May 31 at 5:25 1 @Nima: Table scan takes an XLOCK per row.

AND is OK. OR is often non-SARGable – gbn May 31 at 5:42 1 I wonder why your sql server does not block Query2 after running Query3. Do you use sepecial settings or version?

Can you test on another version? Thanks – Nima May 31 at 5:52 @Nima: Same on SQL Server 2005 SP3 too x86 Enterprise – gbn May 31 at 6:46.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.