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?