3

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?
2

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;
  • 1
    Ah, yes, that's what I'm going for! For the insert, I can just insert into docs(cols, LockedBy) to keep from having someone else lock the newly-created document while the rest of the SP executes. – Kristen Hammack Jan 25 '17 at 17:56
  • 1
    Happy to help! @KristenHammack – SqlZim Jan 25 '17 at 17:57

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.