Optimistic concurrency on multi-table complex entity

I have a complex entity (let's call it Thing) which is represented in SQL Server as many tables: one parent table dbo.Thing with several child tables dbo.ThingBodyPart, dbo.ThingThought, etc. We've implemented optimistic concurrency using a single rowversion column on dbo.Thing, using the UPDATE OUTPUT INTO technique. This has been working great, until we added a trigger to dbo.Thing. I'm looking for advice in choosing a different approach, because I'm fairly convinced that my current approach cannot be fixed.

我有一个复杂的实体(让我们称之为Thing),它在SQL Server中表示为多个表:一个父表dbo.Thing与几个子表dbo.ThingBodyPart,dbo.ThingThought等。我们使用单个实现了乐观并发dbo.Thing上的rowversion列,使用UPDATE OUTPUT INTO技术。这一直很有效,直到我们为dbo.Thing添加了一个触发器。我正在寻找选择不同方法的建议,因为我相信我目前的方法无法修复。

Here is our current code:

这是我们当前的代码:

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

This worked absolutely beautifully, until we added an INSTEAD OF UPDATE trigger to dbo.Thing. Now the stored procedure no longer returns the new @rowVersion value, but returns the old unmodified value. I'm at a loss. Are there other ways to approach optimistic concurrency that would be as effective and easy as the one above, but would also work with triggers?

这非常精彩,直到我们为dbo.Thing添加了一个INSTEAD OF UPDATE触发器。现在,存储过程不再返回新的@rowVersion值,而是返回旧的未修改值。我很茫然。是否存在其他方法来处理乐观并发,这种方法与上面的方法一样有效和简单,但是也适用于触发器?

To illustrate what exactly goes wrong with this code, consider this test code:

为了说明此代码究竟出现了什么问题,请考虑以下测试代码:

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.  :('

When the trigger is not present, this code correctly outputs:

当触发器不存在时,此代码正确输出:

@oldRowVersion = 0x0000000000016CDC

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

When the trigger is present, we do not receive the expected value:

当触发器出现时,我们没有收到预期的值:

@oldRowVersion = 0x0000000000016CDC

(1 row(s) affected)

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

Any ideas for a different approach?

对于不同方法的任何想法?

I was assuming that an UPDATE was an atomic operation, which it is, except when there are triggers, when apparently it's not. Am I wrong? This seems really bad, in my opinion, with potential concurrency bugs lurking behind every statement. If the trigger really is INSTEAD OF, shouldn't I get back the correct timestamp, as though the trigger's UPDATE was the one I actually executed? Is this a SQL Server bug?

我假设一个UPDATE是一个原子操作,它是,除非有触发器,显然它不是。我错了吗?在我看来,这似乎非常糟糕,潜在的并发错误隐藏在每个语句背后。如果触发器确实是INSTEAD OF,我不应该回到正确的时间戳,好像触发器的UPDATE是我实际执行的那个吗?这是一个SQL Server错误吗?

1 个解决方案

#1

One of my esteemed co-workers, Jonathan MacCollum, pointed me to this bit of documentation:

我尊敬的同事之一Jonathan MacCollum向我指出了一些文档:

INSERTED

Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

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

From this, I presume that I need to modify my stored procedure, splitting the one UPDATE into an UPDATE followed by a SELECT [RowVersion] ....

从这里,我假设我需要修改我的存储过程,将一个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;

I think I can still rest assured that my stored procedure is not accidentally overwriting anybody else's changes, but I should no longer assume that the data that the caller of the stored procedure holds is still up-to-date. There's a chance that the new @rowVersion value returned by the stored procedure is actually the result of someone else's update, not mine. So actually, there's no point in returning the @rowVersion at all. After executing this stored procedure, the caller should re-fetch the Thing and all of its child records in order to be sure its picture of the data is consistent.

我想我仍然可以放心,我的存储过程不会意外地覆盖任何其他人的更改,但我不应该再认为存储过程的调用者持有的数据仍然是最新的。存储过程返回的新@rowVersion值实际上可能是其他人更新的结果,而不是我的更新。实际上,根本没有返回@rowVersion。执行此存储过程后,调用者应重新获取Thing及其所有子记录,以确保其数据图像一致。

... which further leads me to conclude that rowversion columns are not the best choice for implementing optimistic locking, which sadly is their sole purpose. I would be much better off using a manually incremented int column, with a query like:

...这进一步让我得出结论,rowversion列不是实现乐观锁定的最佳选择,这可能是他们唯一的目的。我会更好地使用手动递增的int列,使用如下查询:

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

The Version column is checked and incremented in a single atomic operation, so there's no chance for other statements to slip in-between. I don't have to ask the database what the new value is, because I told it what the new value is. As long as the Version column contains the value I'm expecting (and assuming all other people updating this row are also playing by the rules - correctly incrementing Version), I can know that the Thing is still exactly as I left it. At least, I think...

Version列被检查并在单个原子操作中递增,因此其他语句不可能在中间插入。我不必询问数据库新值是什么,因为我告诉它新值是什么。只要版本列包含我期望的值(并假设所有其他人更新此行也按规则播放 - 正确递增版本),我可以知道Thing仍然与我离开它完全一样。至少,我认为......

#1