4

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:
        commit
        show "Sorry, record already in use by ..."
    else
        update myTable set lockedBy = current_user() where id = recordId
        commit
        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.

10
  • 3
    What if the connection doesn't end, but the user has gone to lunch? Commented Jun 6, 2013 at 15:27
  • Is there a field that shows when a lock was obtained? Commented Jun 6, 2013 at 15:29
  • Have you considered adopting an architecture that uses sp_getapplock instead of your own table-based locking mechanism?
    – Jon Seigel
    Commented Jun 6, 2013 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. Commented Jun 6, 2013 at 19:06
  • 1
    @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
    Commented Jun 6, 2013 at 20:18

1 Answer 1

5

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:

APPLOCK_MODE
APPLOCK_TEST
sp_getapplock
sp_releaseapplock

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

Community Wiki answer generated from a question comment by @JonSeigel

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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