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.