主页 / dba / 问题 / 162199
Accepted
Kristen Hammack
Asked: 2017-01-26 07:29:24 +0800 CST

如果存在则锁定行，否则创建新记录

  • 772

我有一个包含“文档”元数据的表，我们称它为 DOCS。其他表指向元数据表的 ID，以构建文档的各个部分。

我想确保在我的应用程序中，我可以让一个人“签出”文档，以便他们是唯一可以编辑它的人。（当然，应用程序内置了覆盖机制）。

所以我的想法是在 DOCS 表中有一列 LockedBy，如果文档被锁定，它会保存一个用户 ID。

我希望应用程序只需要使用一个存储过程来进行插入和更新，所以我目前正在使用给定的 @DOCID 并执行“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

但是既然要加锁机制，就需要通过某种方式来检测doc是否存在，有没有被别人加锁，同时考虑到并发的可能性。

如果我做：

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. SELECT从事务到结束，该行是否会被锁定？如果我在最后一个ELSE子句中创建行，我是否需要做类似的事情INSERT INTO DOCS WITH(HOLDLOCK,ROWLOCK)？
  2. 有一个更好的方法吗？
sql-server locking

1 个回答

  1. Best Answer

    如果LockedBy未锁定时为空，则...

    使用select @CurrentLockedBy = isnull(LockedBy,0)让我们找出记录处于三种状态中的哪一种。如果是null，则记录不存在。如果是，0则它存在并已解锁。否则它存在并被某人（可能是调用者）锁定。

    我会使用这样的东西：

    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;
    • 2

相关问题