SQL Server – Understanding Transactions and Locks

lockingsql servertransaction

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?

Best Answer

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

Related Question