Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_getapplock useful? Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2005 1:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, 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
Post #208968
Posted Wednesday, August 10, 2005 2:08 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #208972
Posted Wednesday, August 10, 2005 4:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Tnx for the heads-up, Chris.


Greetz,
Hans Brouwer
Post #208999
Posted Wednesday, May 23, 2012 8:46 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #1305015
Posted Wednesday, May 23, 2012 9:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 23,000, Visits: 31,482
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.



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)
Post #1305034
Posted Wednesday, May 23, 2012 9:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1305076
Posted Thursday, May 24, 2012 10:49 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #1306001
Posted Friday, May 25, 2012 2:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,595, Visits: 4,585
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.
Post #1306839
Posted Friday, May 25, 2012 4:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 6,134, Visits: 7,182
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
Post #1306852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse