1

I would like to lock some rows in a table with a SELECT statement in SP. I have transaction in my SP. I would like to lock all rows that I SELECT after BEGIN TRANSACTION.So, I would like to release those rows after COMMIT/ROLLBACK.

I have tried XLOCK,UPDLOCK,HOLDLOCK but none of them does what I expect.

Here is my sample code...

BEGIN TRANSACTION 
    -- I WANT TO LOCK EMPLOYEES LIVE IN ISTANBULL
    SELECT ID FROM EMPLOYEES WITH(XLOCK) WHERE CITY='ISTANBUL'
    ....
    ....
    ....
COMMIT
-- LOCKED ROWS SHOULD BE RELEASED AFTER COMMIT.

any suggestion?

Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189

3 Answers3

8

Your code should work fine.

Suppose, you are selecting rows with UPDLOCK/XLOCK.

First Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WITH(UPDLOCK, XLOCK) WHERE CITY='ISTANBUL'
--COMMIT TRAN (Stopping commit to keep the lock running)

Now try run following in another window.

Second Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WITH(UPDLOCK, XLOCK) WHERE CITY='ISTANBUL'
COMMIT TRAN

Your second transaction will not be able to select until you commit your first transaction. Because multiple UPDLOCK or XLOCK can't be applied together on a resource.

Now, if you read rows without locking with uncommitted first transaction then second transaction will not be prevented by the first.

Second Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
COMMIT TRAN

Because in second transaction no lock applied so it will not be prevented by first transactions UPDLOCK or XLOCK.

Now to prevent any read with another read you need to change your ISOLATION LEVEL TO SERIALIZABLE.

First Transaction

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
--COMMIT TRAN (Stopping commit to keep the lock running)

Second Transaction

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
COMMIT TRAN

Now second transaction will be blocked by first transaction. Though no lock applied during read but in transaction isolation level SERIALIZABLE a read transaction will block read of another transaction over same resource.

Now If you select with NOLOCK then there is no transaction lock or isolation level exists to block you.

Hope these helps :)

Esty
  • 1,882
  • 3
  • 17
  • 36
  • I have a huge system that has been active running for a long time. Unfortunately, all those transactions are written without UPDLOCK and XLOCK. And that is why I am having difficulty – Arif YILMAZ Oct 03 '16 at 05:58
  • 1
    @ayilmaz, I have also faced these problem few months ago. To stop read by another read you have two options either use lock to each and every places or use SERIALIZABLE isolation level. But I will suggest you to use UPDLOCK rather changing isolation level. It will increase your re-development cost but changing isolation level has huge impact over performance. And remove all NOLOCKs if used. – Esty Oct 03 '16 at 06:05
  • Also I would like to suggest to use UPDLOCK with select if you are going to update the same resource later inside same transaction. It will prevent common deadlock issue for update rows. – Esty Oct 03 '16 at 06:09
  • See http://stackoverflow.com/questions/32692831/concurrent-execution-in-sql-server/32980557#32980557 – Esty Oct 03 '16 at 06:09
  • Unfortunately, in our application, NOLOCK is used everywhere. – Arif YILMAZ Oct 03 '16 at 06:15
  • 1
    Then you have no other way my friend. Search them, review them and append UPDLOCK where required. – Esty Oct 03 '16 at 08:37
0
FROM TABLE_ITEM with (updlock, holdlock)
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17
-1

Add the holdlock hint to the xlock hint.

Eg. 

begin tran 
  select ... from ... with (xlock, holdlock) where ...
commit tran
ADyson
  • 57,178
  • 14
  • 51
  • 63
Huiyong Lau
  • 46
  • 3