Friday, November 15, 2013

Use application lock to synchronize T-SQL code

Application lock can be used to synchronize T-SQL code, you can control if the session is able to run the t-sql code at same time, it is like the lock keyword in C#.

1. Get lock
you can get application lock with store procedure sp_getapplock, which must be run from within a transaction.

BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock @Resource = 'mylock', @LockMode = 'shared';
select @res

2. Release lock
EXEC sp_releaseapplock @Resource = 'mylock';

Here is example
1. Start a session and run the code below
--spid 52
BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock @Resource = 'mylock', @LockMode = 'exclusive';
select @res

2. Open another session and run the code below
--spid 54
BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock @Resource = 'mylock', @LockMode = 'shared';
select @res

the session spid 54 will be blocked, you can check the blocking status with "sp_lock" or DMV "sys.dm_tran_locks".





here, spid54 is blocked by spid52 and waiting for application lock "mylock". 
if you release lock in spid 52, spid54 can get the application share lock then. by this way you can synchronize the code execution between sp_getapplock and sp_releaseapplock among multi-thread.

like all other lock, application lock will cause deadlock issue as well, please note, if the sp_getapplock is failed because of deadlock victim, the transaction will not be rollback, sp_getapplock will return -3 instead. it will cause orphan transaction sometimes, so you need to rollback the transaction explicitly. 







6 comments:

  1. You can get application lock with store procedure sp_getapplock, which must be run from within a transaction. smart locks

    ReplyDelete
  2. Did you know that you can earn cash by locking special pages of your blog or site?
    Simply open an account with AdscendMedia and implement their Content Locking tool.

    ReplyDelete
  3. Thanks for sharing such a beautiful information with us. I hope you will share more info about

    it. Please keep sharing.Best Lenovo service center in Hyderbad
    Best Laptop service center in Hyderbad

    ReplyDelete
  4. Apa arti taruhan 1X2 ? Bagaimana cara hitung rumus main 1X2? Jenis Bet 1X2 Sbobet dalam taruhan bola umumnya juga dikenal dengan sebutan "fix-odds". Bila kita sebelumnya sudah pernah bermain bola taruhan handicap (Baca Selengkapnya Disini...)

    ReplyDelete
  5. Good Blog, well descrided, Thanks for sharing this information.
    Big Data and Hadoop Online Training

    ReplyDelete