Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
3  
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
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 Jun 6 '13 at 20:18
show 5 more comments

1 Answer

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:

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

share|improve this answer
add comment

Your Answer

 
discard

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.