Ensuring release of pessimistic lock

Question

I'm considering implementing the pessimistic locking pattern in a WinForms insurance quoting application using SQL Server. Before a user starts working on a quote, a record will be added to the lock table; when they're done, the record will be deleted from the table.

My question is, how do I ensure that the lock is released in the event of a failure which is outside of my application's control? I'm thinking mostly of client side network connection errors or power failures, but there are endless possibilities.

Answer 1

Rather than a locking table, consider a session level application lock (https://msdn.microsoft.com/en-us/library/ms189823.aspx). The lock will be released when the SQL session is terminated for any reason, or when released explicitly.

--acquire lock
DECLARE
      @ReturnCode int
    , @ClientID nvarchar(255) = '12345';

EXEC  @ReturnCode = sp_getapplock
      @Resource = @ClientID
    , @LockMode = N'Exclusive' 
    , @LockOwner = 'Session'
    , @LockTimeout = 0;

IF @ReturnCode < 0
BEGIN
    RAISERROR('Lock for quote not be granted for ClientID %s. Return code=%d', 16, 1, @ClientID, @ReturnCode);
END;

--release lock
DECLARE
      @ReturnCode int
    , @ClientID nvarchar(255) = '12345';

EXEC  @ReturnCode = sp_releaseapplock
      @Resource = @ClientID
    , @LockOwner = 'Session';