I'd like to use a lock table for pessimistic offline locking to prevent the case when multiple users work on one work item. I'm using a lock table for storing the active locks and two stored procedures for locking and unlocking.

My lock table's structure is the following: CaseId (PK) | UserId | LockValidTo.

For locking and unlocking I wrote two stored procedures, one for locking and one for unlocking.

This is my stored procedure for locking:

BEGIN
    BEGIN TRY
        INSERT INTO scs.dbo.caselocks VALUES (@caseId, @agentId, dateadd(n, @lockTime, getdate()) )
        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT 1
    END CATCH
END

And this is for removing locks:

BEGIN
    BEGIN TRY
        DELETE FROM scs.dbo.caselocks
        WHERE caseid = @caseid
        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT 1
    END CATCH
END

My question is that, is this a right approach for the handling locks? (Sorry if this should have been posted to Code Review instead of SO.)

• sql-server
• sql-server-2008
• tsql

For the delete SP, will you allow any agent to delete the lock? Also, why are you returning a ResultSet from the SP with "SELECT"? You could just use the RETURN statement.

I see that you are using the getdate() function to persist a time in the local time zone. This is often a cause of issues when you deal with multiple time zones, switches to/from Daylight Savings Time, etc. The best practice is to always use UTC times whenever you persist a date/time and only convert to local time when the time is displayed to a user.