Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:25

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer

up vote 3 down vote accepted

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)
share|improve this answer
1  
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 '11 at 5:25
1  
@Nima: Table scan takes an XLOCK per row. AND is OK. OR is often non-SARGable –  gbn May 31 '11 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 '11 at 5:52
    
@Nima: Same on SQL Server 2005 SP3 too x86 Enterprise –  gbn May 31 '11 at 6:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.