CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008transactions
DooDoo
DooDoo

Reputation: 13447

With(XLock,RowLock) does not lock row exclusively

I have a table that has a column named "Is_Locked".

I open 2 SSMS and in every one create a new Query with this script:

BEGIN TRAN Nima1 
BEGIN TRY
    DECLARE @a INT

    SELECT @a=COUNT(*) 
    FROM   dbo.Siahe WITH(XLOCK,ROWLOCK)  
    WHERE  TedadDaryaii=8 
           AND Is_Locked=1

     IF @a = 0
     BEGIN
       UPDATE Siahe
       SET    Is_Locked = 1
       WHERE  ShMarja = 9999
     END

COMMIT TRAN Nima1
END TRY
BEGIN CATCH
     ROLLBACK TRAN Nima1
END CATCH

but if all Is_Lock field Is false then both query execute and Select Statement does not lock the rows exclusively.

Why?

Upvotes: 0

Views: 1022

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

If @a = 0 then there were 0 matching rows from your first query. All 0 of those rows are exclusively locked. I'm a bit confused by your different where conditions in your select and update statements. If the same where conditions were used in both, I'd suggest something like:

UPDATE Siahe
SET    Is_Locked = 1
WHERE
      Is_Locked = 0 and
      /* Other Conditions */

IF @@ROWCOUNT = 1
BEGIN
    PRINT 'We got the lock'
END
ELSE
BEGIN
    PRINT 'Someone else has the lock'
END

Upvotes: 1

Related Questions