August 10, 2005 at 1:58 am
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
August 10, 2005 at 2:08 am
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.
August 10, 2005 at 4:08 am
Tnx for the heads-up, Chris.
Greetz,
Hans Brouwer
May 23, 2012 at 8:46 am
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
Jason
http://dbace.us
😛
May 23, 2012 at 9:17 am
jswong05 (5/23/2012)
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
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.
May 23, 2012 at 9:44 am
jswong05 (5/23/2012)
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
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
May 24, 2012 at 10:49 am
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
😛
May 25, 2012 at 2:58 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 25, 2012 at 4:45 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2014 at 11:26 am
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,
November 18, 2014 at 11:46 am
skroba (11/18/2014)
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,
You can't, not easily. It's like a lost transaction. You need DBA interference or you need to build callable scripts to force releases.
In a way, you don't want it to. Usually for something like this you need the DBA to reset other pieces too, and you need to figure out at what point the app hung. A catch-all fix for processes that would usually use this isn't in the cards, unless they're incredibly simplistic... in which case I'd be looking for alternate methods.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 6, 2015 at 10:48 am
The Upsert concurrency situation solved by lock:
My web method (many instances are running at once (load balanced)) gets a unique name and a price for it. It must upsert it in the database. It goes like this:
UpsertItems(Name, Cost)
{
ExecNonQuery("
exec sp_getapplock @Resource='Lck{0}', @LockOwner='Session';
if Exists (select 1 from tbl where Name='{0}') Update tbl set Price={1} where Name='{0}';
else insert Into tbl (Name,Price) Values ('{0}',{1});
exec sp_releaseapplock @Resource='Lck{0}', @LockOwner='Session';
", Name, Cost);
}
Well, this basic beautiful things simply doesn't work ! sp_getapplock is not working! It grant the lock all the time. I think I need @LockScope = DBSERVER since I have only one db server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply