SQL Server – Lock Row if Exists, Create New Record Otherwise

lockingsql servertransactionupsert

I have a table that holds metadata for "documents", let's call it DOCS. Other tables point to the metadata table's ID, to build the parts of the document.

I want to make sure that, in my application, I can have one person "check out" a document so that they are the only person who can edit it. (With an override mechanism built into the app, of course).

So my idea is to have a column in the DOCS table, LockedBy, which holds a user ID if the document is locked.

I want the application to only have to use one stored procedure for inserts and updates, so I'm currently taking the given @DOCID and doing an "upsert":

UPDATE DOCS
SET column=@param
WHERE ID=@DOCID;
IF @@ROWCOUNT=0 --@@ROWCOUNT will be 0 if the update fails
BEGIN
    INSERT INTO DOCS(columnnames)
    VALUES(@newvalues);
    SET @DOCID=SCOPE_IDENTITY();
END

But now that I want to add the locking mechanism, I need some way to check if the doc exists, and it is not locked by someone else, taking into account the possibility of concurrency.

If I do:

BEGIN TRANSACTION TR1
IF EXISTS(
    SELECT * 
    FROM DOCS WITH(HOLDLOCK,ROWLOCK)
    WHERE ID=@DOCID)
BEGIN
    IF NOT EXISTS(SELECT * FROM DOCS WHERE ID=@DOCID AND LockedBy=@Editor)
    RETURN -1 --return "error" code to app
END
ELSE
--create row here

--finally do all the updates to secondary tables
COMMIT TRANSACTION TR1
  1. Will the row be locked from the SELECT to the end of the transaction? And if I create the row in the last ELSE clause, do I need to do something like INSERT INTO DOCS WITH(HOLDLOCK,ROWLOCK)?
  2. Is there a better way to do this?

Best Answer

If LockedBy is null when not locked then...

Using select @CurrentLockedBy = isnull(LockedBy,0) lets us find out which of the three states the record is in. If it is null, then the record doesn't exist. If it is 0 then it exists and is unlocked. Otherwise it exists and is locked by someone (possibly the caller).

I would use something like this: 

create procedure dbo.docs_upsert (
    @docid int output
  , @cols varchar(max)
  , @LockedBy int
  , @CallerUserId int
  ) as
begin
  set nocount on;
  set xact_abort on;
  declare @CurrentLockedBy int;
  begin try;
    begin tran;
      select @CurrentLockedBy = isnull(LockedBy,0)
        from docs d with (updlock, holdlock) -- lock for update/insert
        where d.id = @docid; 
    /*
     If @CurrentLockedBy >0 then Locked by someone
     If @CurrentLockedBy = 0, exists and not locked. 
     If @CurrentLockedBy is null, row doesn't exist.
     */
    /* Locked by another user*/
    if isnull(@CurrentLockedBy,@CallerUserId)!=@CallerUserId
    begin;
      return -1;
    end;
    /* Exists, not Locked */
    if @CurrentLockedBy = 0
    begin;
      update docs set 
          cols = @cols
        , LockedBy = @LockedBy
        where id = @docid;
    end;
    else 
    /* Doesn't exist, insert */
    begin;
      insert into docs(cols)
        values(@cols);
        set @docid=scope_identity();
    end;
    commit tran;
  end try
  begin catch;
    if @@trancount > 0 
      begin;
        rollback transaction;
        throw; -- or other error handling
      end;
  end catch;
end;
Related Question