I consider myself to be an experienced DBA, but have recently inherited a database that makes excessive use of application locks. I understand the benefits and the uses of sp_getapplock, but cannot see any point in using it to wrap a single DML statement.
sp_getapplock allows a database developer to utilise the sql server locking system to facilitate application resource locking. It essentially creates a lock on a named resource that is hypothetical. The lock only blocks other calls to sp_getapplock with the same resource name.
I've never found it that useful because a correct transaction isolation level will generally do the job. However, if you want to ensure that only one copy of some statements are running concurrently, sp_getapplock is what you would use.
A single statement will be wrapped in an implicit transaction. And in Read_Committed transaction isolation level, the transaction will adhere to the ACID principles.
My argument is not with sp_getapplock, but with using sp_getapplock to wrap a single DML statement. Why would anyone ever want to create an explicit transaction, acquire an application lock, perform a single DML statement, then comit the transaction.
asked Mar 31, 2011 at 01:48 PM in Default