4

I have a table, which has a "LockedBy" field, which can either be null (no one has locked this record for editing) or it can have a user ID.

The idea is to make it so that only one team member can "check out" the record (and all of the records in other tables that are tied to this record) at a time, which sounded easy in my head, until I realized I don't really know how locking works with transactions in SQL Server.

Currently, I am updating tables using transactions and save points in a stored procedure, but I don't know how to incorporate the "lock":

BEGIN TRANSACTION TR1
BEGIN TRY

    UPDATE LOCK_TABLE SET UpdatedDate=GETDATE() WHERE ID=@ID
    --======================================================================
    --only update this table if the lock field is null; make sure no one 
    --else can modify the lock field until the whole of TR1 is committed
    --or rolled back
    --======================================================================


    --Table 1   
    SAVE TRANSACTION TR2
    BEGIN TRY
        UPDATE TABLE1 SET somefield='something' WHERE LockTableID=@ID
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION TR2;
        PRINT ERROR_MESSAGE();
    END CATCH

    COMMIT TRANSACTION TR1;
    --======================================================================
    --release the lock
    --======================================================================
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TR1;
    SET @RETURN=-1;
    PRINT ERROR_MESSAGE();
END CATCH

I understand locks from a concurrent programming point of view, but every SQL Server introduction to locks seems to either be about preventing deadlocks or how to use different granularities of locks.

I think what I want is just "from here to here, don't let anyone else execute this stored procedure" (like a lock in a multi-threaded function), but maybe I'm coming at it from the wrong angle.

Does SQL Server do what I want automatically when I use transactions? Or is there something special I need to add so that I can lock out other calls to this stored procedure?

2

SQL Server has very extensive locking options. Most are automatic but you can force them if needed for specific cases. You can also implement non-default locking behaviour on various levels. What you are looking for (as far as I can tell) can best be handled by a table hint called rowlock. Depending on your isolation level, you might need additional hints to ensure its function. You can read more about it in the following link: https://msdn.microsoft.com/en-us/library/ms187373.aspx

| improve this answer | |
  • Is the lock automatically lifted at the end of the transaction? – Kristen Hammack Jan 24 '17 at 21:07
  • Yes it is lifted automatically. – MvdMunnik Jan 24 '17 at 21:24
  • Then yes, this is what I want. – Kristen Hammack Jan 24 '17 at 21:31
2

Roundabout idea, utilize permissions instead.

Perhaps the stored procedures you use will add or remove permissions of others based on checking in or out records.

Example: User A uses a stored procedure which checks out the record, when the record is checked out, it applies R/W privileges to this user. When the record is checked back in, it removes W and only applies R. (A trigger would fire writing to a side table showing who has Write permissions when checked out, and clears the record when no one has write permissions.)


Example 2: User B uses a stored procedure which checks out the record, it looks at a lookup table of users and selects the current user. The rest of the users in the mapping table get permissions to the table removed when the record is checked out. When the record is checked in, the procedure looks at the mapping table and adds permissions back to all users besides the current.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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