asked
sp_getapplock to wrap a single DML statment
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.
It would deem that to be overly cautious unless the developer had foreseen something which he has not documented in his code. Ideally a DML done within a transaction would prevent other concurrent transactions from accessing the same resource unless and until they use Read Uncommitted Isolation Level. The only thing I can think of in favor of this solution is that if they wanted to prevent access to the entire table rather than just a row during the DML operation. Then the logic would make sense. But without much insight into the application/business logic, I wouldn't be able to shed more light on the same.
