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.
You can get application lock with store procedure sp_getapplock, which must be run from within a transaction. smart locks
ReplyDeleteDid you know that you can earn cash by locking special pages of your blog or site?
ReplyDeleteSimply open an account with AdscendMedia and implement their Content Locking tool.
Thanks for sharing such a beautiful information with us. I hope you will share more info about
ReplyDeleteit. Please keep sharing.Best Lenovo service center in Hyderbad
Best Laptop service center in Hyderbad
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...)
ReplyDeletewho provides Graphic services, web designing services, logo design services, graphic design
ReplyDeleteand all kind of website design,Graphic services.
Freelance Graphic Designing
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
Freelance Catalogue Designing in delhi
Freelance Catalogue Designing in gurgaon
Freelance Brochure Designing
Freelance Label Designing
Freelance Banner Designer
Freelance Poster Designer
graphic design services in delhi
graphic design services in gurgaon
graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance graphic designer in gurgaon
freelance logo designer in gurgaon
freelance logo designer in gurgaon
freelance web designer in gurgaon
Good Blog, well descrided, Thanks for sharing this information.
ReplyDeleteBig Data and Hadoop Online Training