sql - TSQL:通过自定义身份创建自定义身份吗？ (管理数据库修订)

Question

我想基于自定义身份创建一个自定义身份。或类似于身份的功能，如自动递增 key 。

例如，如果我具有工程图的主键，则希望其修订基于工程图编号。

示例

DRAWINGID    | REV   | INFO------+-------+------1     | 0     | "Draw1"2     | 0     | "Draw2"2     | 1     | "Draw2Edit"2     | 2     | "Draw2MoreEdit"3     | 0     | "Draw3"4     | 0     | "Draw4"

If I was to insert a few more records into my table such a:

INSERT INTO DRAWING (INFO) VALUES ("Draw5")
INSERT INTO DRAWING (ID,INFO) VALUES (3,"Draw3Edit")

我的 table 想要:

DRAWINGID    | REV   | INFO------+-------+------1     | 0     | "Draw1"2     | 0     | "Draw2"2     | 1     | "Draw2Edit"2     | 2     | "Draw2MoreEdit"3     | 0     | "Draw3"3     | 1     | "Draw3Edit"      --NEW ROW4     | 0     | "Draw4"5     | 0     | "Draw5"          --NEW ROW

T-SQL

CREATE TABLE DRAWING
(
    ID INT,
    REV INT,  
    INFO VARCHAR(50),
    PRIMARY KEY (ID,REV)
);

CREATE TABLE CURRENT_DRAWING
(
    ID INT IDENTITY (1,1),
    DRAWING_ID INT,
    DRAWING_REV INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (DRAWING_ID,DRAWING_REV) REFERENCES DRAWING (ID,REV)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

我正在使用 SQL Server Management Studio 2005 ，并在 SQL Server 2000 数据库上工作。

我还将接受可能的替代方法。主要目标是使ID对新工程图自动递增。 ID将保持不变，而REV将在新的工程图修订版中增加。

更新:

我想我已经接近我想要的:

DROP TABLE DRAW

GO

CREATE TABLE DRAW
(
    ID INT DEFAULT(0), 
    REV INT DEFAULT(-1), 
    INFO VARCHAR(10), 
    PRIMARY KEY(ID, REV)
)

GO

CREATE TRIGGER TRIG_DRAW ON DRAW
FOR INSERT
AS
BEGIN
    DECLARE @newId INT,
            @newRev INT,
            @insId INT,
            @insRev INT

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION

    SELECT @insId = ID FROM inserted
    SELECT @insRev = REV FROM inserted

    PRINT 'BEGIN TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev


    --IF ID=0 THEN IT IS A NEW ID
    IF @insId <=0
    BEGIN
        --NEW DRAWING ID=MAX+1 AND REV=0
        SELECT @newId = COALESCE(MAX(ID), 0) + 1 FROM DRAW
        SELECT @newRev = 0
    END
    ELSE
    --ELSE IT IS A NEW REV
    BEGIN
        --CHECK TO ENSURE ID EXISTS
        IF EXISTS(SELECT * FROM DRAW WHERE ID=@insId AND REV=0)
        BEGIN
            PRINT 'EXISTS'
            SELECT @newId = @insId
            SELECT @newRev = MAX(REV) + 1 FROM DRAW WHERE ID=@insID
        END
        ELSE
        --ID DOES NOT EXIST THEREFORE NO REVISION
        BEGIN
            RAISERROR 50000 'ID DOES NOT EXIST.'
            ROLLBACK TRANSACTION
            GOTO END_TRIG
        END
    END

    PRINT 'END TRIG'
    PRINT @insId
    PRINT @insRev
    PRINT @newId
    PRINT @newRev

    SELECT * FROM DRAW

    UPDATE DRAW SET ID=@newId, REV=@newRev WHERE ID=@insId



    COMMIT TRANSACTION
    END_TRIG:
END

GO


INSERT INTO DRAW (INFO) VALUES ('DRAW1')
INSERT INTO DRAW (INFO) VALUES ('DRAW2')
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT1') --PROBLEM HERE
INSERT INTO DRAW (ID,INFO) VALUES (2,'DRAW2EDIT2')
INSERT INTO DRAW (INFO) VALUES ('DRAW3')
INSERT INTO DRAW (INFO) VALUES ('DRAW4')

GO

--SHOULD THROW
INSERT INTO DRAW (ID,INFO) VALUES (9,'DRAW9')

GO

SELECT * FROM DRAW

GO

但是，我一直在获取 Violation of PRIMARY KEY constraint。

我已经放置了调试语句，并且似乎不太可能违反我的主键:

开始触发
0
-1

结束触发
0
-1
1个
0

(影响1行)

(影响1行)

(影响1行)
开始触发
0
-1

结束触发
0
-1
2个
0

(受影响的2行)

(影响1行)

(影响1行)
开始触发
2个
-1

存在
结束触发
2个
-1
2个
1个

(受影响的3行)
消息2627，第14级，状态1，过程TRIG_DRAW，第58行
违反主键约束'PK__DRAW__56D3D912'。无法在对象“DRAW”中插入重复的 key 。
该语句已终止。

它打印

ID | REV |信息
---- + -------- + ------------
1 | 0 |画1
2 | -1 | DRAW2EDIT1-此行正在更新为2 1
2 | 0 |画图2

就在它失败并且行2 -1被更新为2 1之前。它不应该违反我的主键。

Answer 1

我实际上会推荐一种替代数据设计。这种键序列模式很难在关系数据库中正确实现，并且弊端往往大于 yield 。

您有很多选择，但是最简单的选择是将表一分为二:

CREATE TABLE DRAWING
(
    ID INT IDENTITY(1, 1),
    PRIMARY KEY (ID)
);

CREATE TABLE DRAWING_REVISION
(    
    ID INT IDENTITY(1, 1),
    DRAWING_ID INT,
    INFO VARCHAR(50),
    PRIMARY KEY (ID),
    CONSTRAINT FK_DRAWING_REVISION_DRAWING FOREIGN KEY (DRAWING_ID) REFERENCES DRAWING(ID)
);

这样的好处是无需您付出额外的努力即可准确地表示数据并正确地工作。当您想向工程图添加新修订时，只需在 DRAWING_REVISION表中添加一行即可。因为主键使用 IDENTITY规范，所以您不必做寻找下一个 ID的工作。

显而易见的解决方案及其缺点

但是，如果您需要一个人类可读的修订号，而不是您的服务器唯一的 ID，则可以通过两种方式完成。它们都首先将 REV INT和 DRAWING_REVISION一起添加到 CONSTRAINT UK_DRAWING_REVISION_DRAWING_ID_REV UNIQUE (DRAWING_ID, REV)的数据定义中。然后，诀窍当然是找出给定图纸的下一个修订号。

如果您希望每个人只有很少数量的并发用户，则可以在应用程序代码或 SELECT MAX(REV) + 1 FROM DRAWING_REVISION WHERE DRAWING_ID = @DRAWING_ID触发器中简单地使用 INSTEAD OF INSERT。但是，由于并发性高或运气不好，用户最终可能会互相阻塞，因为他们可能尝试将 DRAWING_ID和 REV的相同组合插入 DRAWING_REVISION。

一些背景

尽管解释了为什么只有一个解决方案需要一点背景信息，但实际上只有一个解决方案。考虑以下代码:

BEGIN TRAN

INSERT DRAWING DEFAULT VALUES;
INSERT DRAWING DEFAULT VALUES;
SELECT ID FROM DRAWING; -- Output: 1, 2

ROLLBACK TRAN

BEGIN TRAN

INSERT DRAWING DEFAULT VALUES;
SELECT ID FROM DRAWING; -- Output: 3

ROLLBACK TRAN

当然，在后续执行中，输出将有所不同。在后台，SQL Server正在分发 IDENTITY值并增加计数器。如果您从未真正提交过该值，则服务器不会尝试“回填”序列中的孔-这些值仅在向前的基础上提供。

这是一个功能，而不是错误。 IDENTITY列设计为有序且唯一，但不必紧密包装。保证紧密打包的唯一方法是序列化所有传入的请求，确保每个请求在下一个请求开始之前完成或终止。否则，服务器可能会尝试回填半小时前发出的 IDENTITY值，只是为了使长时间运行的事务(即该 IDENTITY值的初始接收者)提交具有重复主键的行。

(值得一提的是，当我说“事务”时，虽然我建议使用它们，但不必引用TSQL TRANSACTION。它绝对可以是应用程序或SQL Server端上的任何过程，可能花费任何数量时间，即使该时间仅是 SELECT下一个修订版号，然后立即 INSERT新 DRAWING_REVISION所花费的时间。)

这种回填值的尝试只是变相而已，因为在同时存在两个 INSERT请求的情况下，它将惩罚第二个提交请求。这迫使最后一个来者再次尝试(可能多次尝试，直到碰巧没有冲突为止)。一次有一个成功的提交:序列化，尽管没有队列的好处。
SELECT MAX(REV) + 1方法具有相同的缺点。自然， MAX方法不会尝试回填值，但会强制每个并发请求争用相同的修订版号，并获得相同的结果。

为什么这样不好？数据库系统是为并行性和通用性而设计的:与平面文件格式相比，此功能是托管数据库的主要优点之一。

伪造正确

那么，经过漫长的阐述之后，您该怎么解决这个问题呢？您可以用手指交叉，希望您永远不会看到许多并发用户，但是为什么要反对广泛使用自己的应用程序呢？毕竟，您不希望成功是您的失败。

解决方案是使用 IDENTITY列执行SQL Server的操作:将它们分发出去，然后将它们扔掉。您可以使用类似以下SQL代码的代码，或使用等效的应用程序代码:

ALTER TABLE DRAWING ADD REV INT NOT NULL DEFAULT(0);

GO

CREATE PROCEDURE GET_REVISION_NUMBER (@DRAWING_ID INT) AS
BEGIN
    DECLARE @ATTEMPTS INT;
    SET @ATTEMPTS = 0;
    DECLARE @ATTEMPT_LIMIT INT;
    SET @ATTEMPT_LIMIT = 5;
    DECLARE @CURRENT_REV INT;
    LOOP:
        SET @CURRENT_REV = (SELECT REV FROM DRAWING WHERE DRAWING.ID = @DRAWING_ID);
        UPDATE DRAWING SET REV = @CURRENT_REV + 1 WHERE DRAWING.ID = @DRAWING_ID AND REV = @CURRENT_REV;
        SET @ATTEMPTS = @ATTEMPTS + 1;
        IF (@@ROWCOUNT = 0)
        BEGIN
            IF (@ATTEMPTS >= @ATTEMPT_LIMIT) RETURN NULL;
            GOTO LOOP;
        END
    RETURN @CURRENT_REV + 1;
END

@@ ROWCOUNT检查非常重要–这个过程必须是非事务性的，因为您不想隐藏并发请求中的冲突；您想解决它们。确保您确实进行了更新的唯一方法是检查是否更新了任何行。

当然，您可能已经猜到这种方法不是万无一失的。解决冲突的唯一方法是在放弃之前尝试几次。没有一个自制的解决方案会比硬编码到数据库服务器软件中的解决方案更好。但是它可以很接近!

存储过程不能消除冲突，但是可以大大缩短发生冲突的时间。您将收到最新的修订号并尽快更新静态计数器，而不是“保留”待处理的 INSERT事务的修订号，从而避免了对下一个 GET_REVISION_NUMBER的调用。 (可以肯定地说，这是序列化的，但是仅用于需要以串行方式执行的过程的一小部分；与许多其他方法不同，该算法的其余部分可以自由地并行执行。)

我的团队使用了与上述概述类似的解决方案，我们发现阻塞冲突的发生率下降了几个数量级。在其中一台机器陷入僵局之前，我们能够从本地网络上的六台机器提交成千上万的背对背请求。

卡住的计算机陷入了一个循环，从SQL Server请求一个新的数字，总是得到空结果。可以这么说，这简直是一言不发。这与 SELECT MAX情况下的冲突行为类似，但少得多。您可以将 SELECT MAX方法(及任何相关方法)的有保证的连续编号换成可扩展性提高1000倍。这种权衡或多或少是基本的:据我所知，没有任何可以保证连续的，非序列化的解决方案。

外卖

当然，所有这些条件都取决于对局部的，半连续的数字的需求。如果您可以使用对用户不太友好的修订版号，则只需公开 DRAWING_REVISION.ID即可。 (不过，如果您问我，以代理的方式公开代理键是不好的。)

真正的收获是，自定义标识列的实现比第一次出现时要难实现，并且可能有一天需要可伸缩性的任何应用程序都必须非常小心如何获取新的自定义标识值。