6

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?

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

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

  • Make sure you have a "unique" index on RegionID, and make sure the row actually exists. Without a unique index, the right lock can't be taken. Nor can it be taken if the rows doesn't exist. For example, if you have a unique index on region id, and rows 1 and 3 exist, you can start two separate transactions, and if one locks 1 and the other locks 3, they can do that at the same time. Likewise, you can simultaneously lock one non-existing row, such as 4. However, if you try to lock another row that does not exist, such as 5, it will fail because of 4. – Triynko Mar 19 at 17:03
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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