x

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.
more ▼

asked Mar 31, 2011 at 01:48 PM in Default

ryanitpro gravatar image

ryanitpro
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
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.
more ▼

answered Apr 01, 2011 at 03:01 AM

TroubleshootingSQL gravatar image

TroubleshootingSQL
31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x39
x26

asked: Mar 31, 2011 at 01:48 PM

Seen: 1392 times

Last Updated: Mar 31, 2011 at 01:48 PM