Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can I tell SQL Server to "do something" (e.g. release an application-level lock) when the connection is closed for whatever reason?

Background: I want to lock a record on the application level, not on the database level. However, I'm having trouble releasing the lock when the application crashes. Basically, I am implementing the following algorithm in my application:

function editRecord(recordId):
    begin transaction
    if (select lockedBy from myTable where id = recordId) is not empty:
        show "Sorry, record already in use by ..."
        update myTable set lockedBy = current_user() where id = recordId
        show UI window to let user edit and update record
        update myTablet set lockedBy = empty where id = recordId

I'm trying to find out if there is an easy way to get rid of the application-level lock when the connection crashes.

share|improve this question
What if the connection doesn't end, but the user has gone to lunch? – Aaron Bertrand Jun 6 '13 at 15:27
Is there a field that shows when a lock was obtained? – Thomas Rushton Jun 6 '13 at 15:29
Have you considered adopting an architecture that uses sp_getapplock instead of your own table-based locking mechanism? – Jon Seigel Jun 6 '13 at 17:19
Seems like a SO question. Wanting SQL Server to affect a change at the app layer also seems in reverse. The app needs to handle the app crash / app lock. – Eric Higgins Jun 6 '13 at 19:06
@JonSeigel: Thanks, I did not know about sp_getapplock. It looks like a combination of sp_getapplock and APPLOCK_TEST should solve this problem nicely. Might I convince you to put that into an answer? – Heinzi Jun 6 '13 at 20:18
up vote 5 down vote accepted

SQL Server provides a built-in mechanism to support application locking. The locks supported are Shared, Update, IntentShared, IntentExclusive, and Exclusive. Deadlock detection is also built-in, though application lock deadlocks do not terminate the active transaction - you need to check the result code returned from the system procedure call. Books Online documentation:


Locks associated with the session are automatically released when the session is logged out.

Community Wiki answer generated from a question comment by @JonSeigel

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.