在表的复合键中增加修订号 [英] Incrementing revision numbers in table's composite key

问题描述

我有一个表可以存储可扩展属性的值对于一个商业实体对象，在这种情况下，这三个表格如下所示：

  CREATE TABLE Widgets（
 WidgetId bigint IDENTITY（1,1），
 ... 
）
 
 CREATE TABLE WidgetProperties（
 PropertyId int IDENTITY（1,1），
名称nvarchar（50）
 Type int  -  0 = int，1 = string，2 = date，etc 
）
 
 CREATE TABLE WidgetPropertyValues（
 WidgetId bigint，
 PropertyId int，
修订int，
 DateTime datetimeoffset（7），
值varbinary（255）
 
 CONSTRAINT [PK_WidgetPropertyValues] PRIMARY KEY CLUSTERED（
 [WidgetId] ASC，
 [PropertyIdId] ASC，
 [修订] ASC 
）
）
 
 ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD CONSTRAINT FK_WidgetPropertyValues_WidgetProperties FOREIGN KEY（PropertyId）
参考dbo.WidgetProperties（PropertyId）
 
 ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD CONSTRAINT FK_WidgetPropertyValues_Widgets FOREIGN KEY（WidgetId）
参考dbo.Widgets WidgetId）
  

所以你看看如何 WidgetId，PropertyId，Revision 是一个复合键，该表存储了Value的整个历史记录（当前值通过获取每个<$ c $最大的修订版本号获得的行获得c> WidgetId + PropertyId 。

我想知道如何设置修订版列为每个 WidgetId + PropertyId 递增1。我想要这样的数据：

  WidgetId，PropertyId，Revision，DateTime，Value 
 ------- ----------------------------------------- 
 1 1 1 123 
 1 1 2 456 
 1 1 3 789 
 1 2 1 012 
  

IDENTITY 将无法正常工作，因为它对于表是全局的，同样适用于 SEQUENCE 对象。

更新我可以想到使用 INSTEAD OF INSERT 触发器的可能解决方案：

  CREATE TRIGGER WidgetPropertyValueInsertTrigger ON WidgetPropertyValues 
 INSTEAD OF INSERT 
 AS 
 BEGIN 
 DECLARE @maxRevision int 
 SELECT @maxRevision = ISNULL（MAX（Revision），0）FROM WidgetPropertyValues WHERE WidgetId = INSERTED.WidgetId AND PropertyId = INSERTED.PropertyId 
 
 INSERT INTO WidgetPropertyValues VALUES（
 INSERTED .WidgetId，
 INSERTED.PropertyId，
 @maxRevision + 1，
 INSERTED.DateTime，
 INSERTED.Value，
）
 END 
  

（对于uninitiated， INSTEAD OF INSERT 与 INSERT 之间的任何 INSERT 操作相比，在<$ c $ / c> INSERT 操作）

我认为这将是并发安全的，因为所有 INSERT 操作具有隐式事务，并且任何相关联的触发器在相同的事务上下文中执行，这应该意味着它是安全的。

解决方案

您的代码具有竞争条件 - 并发事务可能会选择并在您的SELECT和你的INSERT。这可能会导致并发环境中的偶然（主要）关键违规（强制您重试整个事务）。

而不是重试整个事务，更好的策略是重试只有INSERT。简单地把你的代码放在一个循环中，如果发生关键违规（和仅违规），请增加修订版，然后重试。

像这样（从我的头写）：

  DECLARE @maxRevision int =（
 SELECT 
 @maxRevision = ISNULL（MAX（Revision），0）
 FROM 
 WidgetPropertyValues 
 WHERE 
 WidgetId = INSERTED.WidgetId 
 AND PropertyId = INSERTED.PropertyId 
）; 
 
 WHILE 0 = 0 BEGIN 
 
 SET @maxRevision = @maxRevision + 1; 
 
 BEGIN TRY 
 
 INSERT INTO WidgetPropertyValues 
 VALUES（
 INSERTED.WidgetId，
 INSERTED.PropertyId，
 @maxRevision， 
 INSERTED.DateTime，
 INSERTED.Value，
）; 
 
 BREAK; 
 
 END TRY 
 BEGIN CATCH 
 
  - 错误与密钥违规有所不同，
  - 在这种情况下，我们只是将其传递给呼叫者。 
如果ERROR_NUMBER（）<> 2627 
 THROW; 
 
  - 否则，这是一个关键的违规，我们可以让循环
  - 进入下一个迭代（重试使用递增的值）。 
 
 END CATCH 
 
 END 
  

I'm running SQL Server 2014 locally for a database that will be deployed to an Azure SQL V12 database.

I have a table that stores values of extensible properties for a business-entity object, in this case the three tables look like this:

CREATE TABLE Widgets (
    WidgetId bigint IDENTITY(1,1),
    ...
)

CREATE TABLE WidgetProperties (
    PropertyId int IDENTITY(1,1),
    Name       nvarchar(50)
    Type       int -- 0 = int, 1 = string, 2 = date, etc
)

CREATE TABLE WidgetPropertyValues (
    WidgetId   bigint,
    PropertyId int,
    Revision   int,
    DateTime   datetimeoffset(7),
    Value      varbinary(255)

    CONSTRAINT [PK_WidgetPropertyValues] PRIMARY KEY CLUSTERED (
        [WidgetId] ASC,
        [PropertyIdId] ASC,
        [Revision] ASC
    )
)

ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD CONSTRAINT FK_WidgetPropertyValues_WidgetProperties FOREIGN KEY( PropertyId )
REFERENCES dbo.WidgetProperties ( PropertyId )

ALTER TABLE dbo.WidgetPropertyValues WITH CHECK ADD  CONSTRAINT FK_WidgetPropertyValues_Widgets FOREIGN KEY( WidgetId )
REFERENCES dbo.Widgets ( WidgetId )

So you see how WidgetId, PropertyId, Revision is a composite key and the table stores the entire history of Values (the current values are obtained by getting the rows with the biggest Revision number for each WidgetId + PropertyId.

I want to know how I can set-up the Revision column to increment by 1 for each WidgetId + PropertyId. I want data like this:

WidgetId, PropertyId, Revision, DateTime, Value
------------------------------------------------
       1           1         1               123
       1           1         2               456
       1           1         3               789
       1           2         1               012

IDENTITY wouldn't work because it's global to the table and the same applies with SEQUENCE objects.

Update I can think of a possible solution using an INSTEAD OF INSERT trigger:

CREATE TRIGGER WidgetPropertyValueInsertTrigger ON WidgetPropertyValues
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @maxRevision int
    SELECT @maxRevision = ISNULL( MAX( Revision ), 0 ) FROM WidgetPropertyValues WHERE WidgetId = INSERTED.WidgetId AND PropertyId = INSERTED.PropertyId

    INSERT INTO WidgetPropertyValues VALUES (
        INSERTED.WidgetId,
        INSERTED.PropertyId,
        @maxRevision + 1,
        INSERTED.DateTime,
        INSERTED.Value,
    )
END

(For the uninitiated, INSTEAD OF INSERT triggers run instead of any INSERT operation on the table, compared to a normal INSERT-trigger which runs before or after an INSERT operation)

I think this would be concurrency-safe because all INSERT operations have an implicit transaction, and any associated triggers are executed in the same transaction context, which should mean it's safe. Unless anyone can claim otherwise?

解决方案

You code has a race condition - a concurrent transaction might select and insert the same Revision between your SELECT and your INSERT. That could cause occasional (primary) key violations in concurrent environment (forcing you to retry the entire transaction).

Instead of retrying the whole transaction, a better strategy is to retry only the INSERT. Simply put your code in a loop, and if key violation (and only key violation) happens, increment the Revision and try again.

Something like this (writing from my head):

DECLARE @maxRevision int = (
    SELECT
        @maxRevision = ISNULL(MAX(Revision), 0)
    FROM
        WidgetPropertyValues
    WHERE
        WidgetId = INSERTED.WidgetId
        AND PropertyId = INSERTED.PropertyId
);

WHILE 0 = 0 BEGIN

    SET @maxRevision = @maxRevision + 1;

    BEGIN TRY

        INSERT INTO WidgetPropertyValues
        VALUES (
            INSERTED.WidgetId,
            INSERTED.PropertyId,
            @maxRevision,
            INSERTED.DateTime,
            INSERTED.Value,
        );

        BREAK;

    END TRY
    BEGIN CATCH

        -- The error was different from key violation,
        -- in which case we just pass it back to caller.
        IF ERROR_NUMBER() <> 2627
            THROW;

        -- Otherwise, this was a key violation, and we can let the loop 
        -- enter the next iteration (to retry with the incremented value).

    END CATCH

END

这篇关于在表的复合键中增加修订号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！