多表复杂实体的乐观并发控制

Question

我是一名有用的助手，可以为您翻译文字。以下是需要翻译的内容：

我有一个复杂的实体（让我们称之为Thing），在SQL Server中表示为多个表：一个父表dbo.Thing和几个子表dbo.ThingBodyPart、dbo.ThingThought等。我们使用单个rowversion列在dbo.Thing上实现了乐观并发，使用 UPDATE OUTPUT INTO 技术。这一直运作得很好，直到我们向dbo.Thing添加了一个触发器。我正在寻求选择不同方法的建议，因为我相当确信我的当前方法无法修复。

以下是我们目前的代码：

CREATE PROCEDURE dbo.UpdateThing
    @id uniqueidentifier,
    -- ...
    -- ... other parameters describing what to update...
    -- ...
    @rowVersion binary(8) OUTPUT
AS
BEGIN TRANSACTION;
BEGIN TRY

    -- ...
    -- ... update lots of Thing's child rows...
    -- ...

    DECLARE @t TABLE (
        [RowVersion] binary(8) NOT NULL
    );

    UPDATE dbo.Thing
    SET ModifiedUtc = sysutcdatetime()
    OUTPUT INSERTED.[RowVersion] INTO @t
    WHERE
        Id = @id
        AND [RowVersion] = @rowVersion;

    IF @@ROWCOUNT = 0 RAISERROR('Thing has been updated by another user.', 16, 1);

    COMMIT;

    SELECT @rowVersion = [RowVersion] FROM @t;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    EXEC usp_Rethrow_Error;
END CATCH

这个方法之前非常完美，直到我们给dbo.Thing添加了一个INSTEAD OF UPDATE触发器。现在，存储过程不再返回新的@rowVersion值，而是返回旧的未修改的值。我很无助。是否有其他方法来实现乐观并发控制，既像上面的方法一样有效且简单，又可以与触发器一起使用？

---

为了说明这段代码出了什么问题，考虑下面的测试代码：

DECLARE
    @id uniqueidentifier = 'b0442c71-dbcb-4e0c-a178-1a01b9efaf0f',
    @oldRowVersion binary(8),
    @newRowVersion binary(8),
    @expected binary(8);

SELECT @oldRowVersion = [RowVersion]
FROM dbo.Thing
WHERE Id = @id;

PRINT '@oldRowVersion = ' + convert(char(18), @oldRowVersion, 1);

DECLARE @t TABLE (
    [RowVersion] binary(8) NOT NULL
);

UPDATE dbo.Thing
SET ModifiedUtc = sysutcdatetime()
OUTPUT INSERTED.[RowVersion] INTO @t
WHERE
    Id = @id
    AND [RowVersion] = @oldRowVersion;

PRINT '@@ROWCOUNT = ' + convert(varchar(10), @@ROWCOUNT);

SELECT @newRowVersion = [RowVersion] FROM @t;

PRINT '@newRowVersion = ' + convert(char(18), @newRowVersion, 1);

SELECT @expected = [RowVersion]
FROM dbo.Thing
WHERE Id = @id;

PRINT '@expected = ' + convert(char(18), @expected, 1);

IF @newRowVersion = @expected PRINT 'Pass!'
ELSE PRINT 'Fail.  :('

当触发器不存在时，此代码正确输出：

@oldRowVersion = 0x0000000000016CDC

(1 row(s) affected)
@@ROWCOUNT = 1
@newRowVersion = 0x000000000004E9D1
@expected = 0x000000000004E9D1
Pass!

当触发器存在时，我们没有收到预期的值：

@oldRowVersion = 0x0000000000016CDC

(1 row(s) affected)

(1 row(s) affected)
@@ROWCOUNT = 1
@newRowVersion = 0x0000000000016CDC
@expected = 0x000000000004E9D1
Fail.  :(

---

有没有不同的方法？

我一直以为UPDATE是一个原子操作，除了触发器之外，但显然不是。我错了吗？在我看来，这似乎非常糟糕，可能会导致每个语句后面都潜藏着潜在的并发错误。如果触发器确实是INSTEAD OF，那么我应该得到正确的时间戳，就好像触发器的UPDATE是我实际执行的一样。这是SQL Server的一个漏洞吗？

- Daniel Schilling

Answer 1

我的尊敬的同事Jonathan MacCollum向我介绍了这篇文档：

INSERTED

是一个列前缀，指定插入或更新操作添加的值。 以 INSERTED 为前缀的列反映了 UPDATE、INSERT 或 MERGE 语句完成后但 触发器执行之前 的值。

由此，我推测需要修改储存过程，将一个 UPDATE 拆分为一个 UPDATE 加上一个 SELECT [RowVersion] ...。

UPDATE dbo.Thing
SET ModifiedUtc = sysutcdatetime()
WHERE
    Id = @id
    AND [RowVersion] = @rowVersion;

IF @@ROWCOUNT = 0 RAISERROR('Thing has been updated by another user.', 16, 1);

COMMIT;

SELECT @rowVersion = [RowVersion]
FROM dbo.Thing
WHERE Id = @id;

我认为我仍然可以放心地使用存储过程，以避免意外覆盖其他人的更改，但我不应该再假设调用存储过程的数据是最新的。有可能存储过程返回的新的@rowVersion实际上是别人的更新结果，而不是我的。因此，实际上没有必要返回@rowVersion。执行完这个存储过程后，调用者应该重新获取Thing及其所有子记录，以确保其数据的一致性。

...这进一步让我得出结论：rowversion列不是实现乐观锁定的最佳选择，这可悲地成为了它们唯一的目的。最好使用手动递增的int列，并使用像这样的查询：

UPDATE dbo.Thing
SET Version = @version + 1
WHERE
    Id = @id
    AND Version = @version;

“版本”列在单个原子操作中进行检查和递增，因此没有其他语句可以插入其中。我不需要询问数据库新值是什么，因为我已经告诉它新值是什么。只要“版本”列包含我期望的值（并且假设所有更新此行的其他人也遵守规则-正确递增“版本”），我就可以知道“Thing”仍然与我离开时完全一样。至少，我认为是这样...

- Daniel Schilling