SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_getapplock useful?


sp_getapplock useful?

Author
Message
FreeHansje
FreeHansje
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2631 Visits: 810

Hi there,

I'm managing a database where I noticed lock type APP. Never heard of it, could not find anything in BOL, googled the internet and found something about sp_getapplock and sp_releaseapplock. Looked up where these procedures are used in the database, and they are used a lot.

Question: is there any reason why one would use this locktype over the standard locks in SQL Sever? I understand you can lock a specific resource this way. As far as I ca tell no database resources are locked in my situation, but I wonder what would happen if something went amiss and the locked resource is not released.

The database(s) where this situation occurs are BizTalk databases, it is very likely that these locks are part of the BizTalk apl. OS is W2000 + latest sp, DBMS is SQL Server SE + sp3a.



Greetz,
Hans Brouwer
Chris Hedgate
Chris Hedgate
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5977 Visits: 7
Application locks are just a simple implementation of semaphores that use SQL Server's built-in support for locks. They are used by an application calling sp_getapplock to aquire some resource. What resource is not really interesting, it's just the fact that as long as there is a lock on that resource noone else can aquire a lock on that resource. So if another application, or another client/thread/proc/whatever in the same application, tries to use sp_getapplock to aquire a lock on that same resource they will now have to wait until the first process releases the lock.

So they don't really have anything with SQL Server to do, they are just a convenient way for applications to implement serial access to some resource.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
FreeHansje
FreeHansje
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2631 Visits: 810
Tnx for the heads-up, Chris.

Greetz,
Hans Brouwer
jswong05
jswong05
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 476
You can find the documentation on SQLServer2008R2 BOL.
This is one of the stupid design.
Imagine old days, you go to library asking for a book for a page you need. It is checked out (locked), so you cannot have it except wait.
Modern days, the librarian runs a digital replica of that page for you. It is called multi-versioning. (digital hash can be replicated) You got what you asked for as the moment it was.
Locking is not the solution (maybe it was back then at stone age)
Certain things are required to be placed under exclusive locks. That is why ACID was designed/discussed. The lock should be at a minimal unit. That is why data is at best under row level lock.

so what is the design background for "sp_getapplock" to be useful?
BOL--it says: Places a lock on an application resource.
Why do you want to do that? (just because there is no better way known doing it so chosen a bad way doing it)

Jason
http://dbace.us

Jason
http://dbace.us
:-P
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95695 Visits: 38968
jswong05 (5/23/2012)
You can find the documentation on SQLServer2008R2 BOL.
This is one of the stupid design.
Imagine old days, you go to library asking for a book for a page you need. It is checked out (locked), so you cannot have it except wait.
Modern days, the librarian runs a digital replica of that page for you. It is called multi-versioning. (digital hash can be replicated) You got what you asked for as the moment it was.
Locking is not the solution (maybe it was back then at stone age)
Certain things are required to be placed under exclusive locks. That is why ACID was designed/discussed. The lock should be at a minimal unit. That is why data is at best under row level lock.

so what is the design background for "sp_getapplock" to be useful?
BOL--it says: Places a lock on an application resource.
Why do you want to do that? (just because there is no better way known doing it so chosen a bad way doing it)

Jason
http://dbace.us


Without knowing the application it really is difficult to critisize why it is doing something. Perhaps there is a better way to accomplish a specific task rather than using sp_getapplock, but we really aren't in a position to say that something is a stupid design based on the information provided. I don't know, seems a bit unprofessional in my humble opinion.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24254 Visits: 5314
jswong05 (5/23/2012)
You can find the documentation on SQLServer2008R2 BOL.
This is one of the stupid design.
Imagine old days, you go to library asking for a book for a page you need. It is checked out (locked), so you cannot have it except wait.
Modern days, the librarian runs a digital replica of that page for you. It is called multi-versioning. (digital hash can be replicated) You got what you asked for as the moment it was.
Locking is not the solution (maybe it was back then at stone age)
Certain things are required to be placed under exclusive locks. That is why ACID was designed/discussed. The lock should be at a minimal unit. That is why data is at best under row level lock.

so what is the design background for "sp_getapplock" to be useful?
BOL--it says: Places a lock on an application resource.
Why do you want to do that? (just because there is no better way known doing it so chosen a bad way doing it)

Jason
http://dbace.us

I'm thinking that without a deeper understanding of the application that its hard to decide whether the design is good or bad.

Also keep in mind that these are not REAL locks, they are semaphores that are honored by the application. For example if you have one piece of code that uses this and one that doesn't the one that doesn't won't be prevented from working if there are "locks".

Now with all that said, I am not a fan of the sp_getapplock methodology. And I have, in the past, written processes that use semaphore based locking. This is something I try and avoid.

CEWII
jswong05
jswong05
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 476
For example, a specific code case, procedure sys.sp_MSacquireSlotLock,
this is a procedure used in merge replication to limit the number of concurrent merge process. However, when the exclusive lock cannot be obtained, it is blocking due to waitfor because the resource obtained so far was never released. It actually creates blocking problem.

exec @retcode=sys.sp_getapplock @Resource=@slot_name,@LockMode=N'Exclusive',
@LockOwner='Session',@LockTimeout=0,@DbPrincipal=@DbPrincipal

I believe this is implemented because the developers were asked to come up a way to implement "the limit of maximum concurrent merge process". They fixed the leg but broke the toe.

The blocked users apps would not know what is going on and just complain it does not work.
sys.sp_getapplock in share lock mode makes no sense at all, if you want to share, why bother to lock.

Next time you intend to do "sys.sp_getapplock exclusive mode", think first what should happen to all the blocked waiting processes. If you can provide a good error handling, the damage is a lot less; otherwise you could have lots users complaining system is not working.

You could argue there is use for it. My counter-argument is why does rarely anybody put database into
SERIALIZABLE
isolation (because it is not practical). Maybe we change the word "stupid design" to "stupid implementation" (be more professional). The merge processes that exceeding the limit should not be started, not to be started and waited on concurrency blocking with no error handling.

In my view, allowing people to charge on credit card for the money they don't have is a stupid design. (good for bankers, not for commoners). Allowing sub-prime mortgage is a stupid design. (have people sign up the mortgage they have no potential to pay back). JPMorgan designed this risk-management scheme, they themselves pulled out later. That is a stupid design (for people who did not see it until later everybody did) There is no way to zero-out risk, they sweep into the carpet and convince someone else buy into it.

Jason
http://dbace.us
:-P
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29202 Visits: 11517
I've actually never leveraged sp_getapplock nor even encountered it, and I've seen a lot of databases code. However, I have seen situations where developers have implemented their own concurrent "locking" mechanism that basically does the same thing. The buildin sp_getapplock procedure probably does it better. There are occasions where sp_getapplock would come in handy. For example, if you have a dashboard reporting application for a data warehouse, it would be useful to block the application from accessing the tables while they are being bulk loaded or updated. That would be preferable to relying on SQL Server's row/page/table locking mechanism, especially if developers have implemented NOLOCK hints on all their queries.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20823 Visits: 7660
sp_getapplock is a simple way to make sure external calls don't end up stepping on each other during a shared usage.

Under most circumstances I've used to to make sure something that business 'GUARANTEED!' would never be ran simultaneously by two users literally couldn't be ran simutaneously by two users.

An example. Let's say you've got a user-initiated process to go and bulk load data into a staging table from user-delivered files to your FTP site. Users want to initiate it themselves because sometimes they want to human-QA them before dumping them to the system.

Without something like this in place, two users could theoretically step on each other running the same process. With it, you can make sure that one runs to completion (and thus not mucking up staging data) while the other waits... or gets denied based on another user's usage.

sp_applock has its uses, particularly for user-initiated events that require dedicated processing during its existance. It's not used to block reads or the like (as jswong seems to be intimating), it's used to help isolate resources to a specific instance of a process. However, it definately has its downsides. Crash, failure to complete, etc keeps the applock in existance until a DBA becomes involved to cleanup the leftovers and release the applock.

Sometimes, that's a GOOD thing... and another reason I've used it, to make sure a 10 minute repetitive process that failed for some reason during the last process literally CANNOT fire up again until manual intervention.

applock is nothing more than iteration/multi-instance defense from your users in your data-systems. Any time you look at something you've built and decide "If this occurs more than once simultaneously we're hosed" and you can't figure out how to re-design it to NOT have that problem, sp_applock is your new best friend.

It's like any other tool. If you misuse your screwdriver as a wrench it won't work right.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
skroba
skroba
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 3
Sounds very reasonable and understandable but one question is left...
How to release orphaned Application, Exclusive, Session lock without dba interaction and without killing session itself? How release the lock from other session?
Any ideas, if it really possible...?

Thanks,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search