sql-server – 在SQL Server中处理对密钥表的并发访问而不会出现

我有一个表,遗留应用程序使用它作为各种其他表中的IDENTITY字段的替代.

表中的每一行都存储IDName中指定的字段的最后使用的ID LastID.

偶尔存储的proc会出现死锁 – 我相信我已经构建了一个合适的错误处理程序;但我有兴趣看看这种方法是否像我认为的那样有效,或者我是否在这里咆哮错误的树.

我相当肯定应该有一种方法来访问这个表,没有任何死锁.

数据库本身配置为READ_COMMITTED_SNAPSHOT = 1.

首先,这是表格：

CREATE TABLE [dbo].[tblIDs](
    [IDListID] [int] NOT NULL 
        CONSTRAINT PK_tblIDs 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1),[IDName] [nvarchar](255) NULL,[LastID] [int] NULL,);

IDName字段上的非聚集索引：

CREATE NONCLUSTERED INDEX [IX_tblIDs_IDName] 
ON [dbo].[tblIDs]
(
    [IDName] ASC
) 
WITH (
    PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 80
);

GO

一些样本数据：

INSERT INTO tblIDs (IDName,LastID) 
    VALUES ('SomeTestID',1);
INSERT INTO tblIDs (IDName,LastID) 
    VALUES ('SomeOtherTestID',1);
GO

存储过程用于更新存储在表中的值,并返回下一个ID：

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs
        for a given IDName
        Author:         Max Vernon
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int,@ES int,@ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            SET @NewID = COALESCE((SELECT LastID 
                FROM tblIDs 
                WHERE IDName = @IDName),0)+1;
            IF (SELECT COUNT(IDName) 
                FROM tblIDs 
                WHERE IDName = @IDName) = 0 
                    INSERT INTO tblIDs (IDName,LastID) 
                    VALUES (@IDName,@NewID)
            ELSE
                UPDATE tblIDs 
                SET LastID = @NewID 
                WHERE IDName = @IDName;
            COMMIT TRANSACTION;
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
            ROLLBACK TRANSACTION;
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

存储过程的示例执行：

EXEC GetNextID 'SomeTestID';

NewID
2

EXEC GetNextID 'SomeTestID';

NewID
3

EXEC GetNextID 'SomeOtherTestID';

NewID
2

编辑：

我添加了一个新索引,因为SP没有使用现有索引IX_tblIDs_Name;我假设查询处理器正在使用聚簇索引,因为它需要存储在LastID中的值.无论如何,这个索引是由实际执行计划使用的：

CREATE NONCLUSTERED INDEX IX_tblIDs_IDName_LastID 
ON dbo.tblIDs
(
    IDName ASC
) 
INCLUDE
(
    LastID
)
WITH (FILLFACTOR = 100,ONLINE=ON,ALLOW_PAGE_LOCKS = ON);

编辑#2：

我已经接受了@AaronBertrand给出并略微修改它的建议.这里的一般想法是优化语句以消除不必要的锁定,并总体上使SP更有效.

下面的代码将上面的代码从BEGIN TRANSACTION替换为END TRANSACTION：

BEGIN TRANSACTION;
SET @NewID = COALESCE((SELECT LastID 
        FROM dbo.tblIDs 
        WHERE IDName = @IDName),0) + 1;

IF @NewID = 1
    INSERT INTO tblIDs (IDName,LastID) 
    VALUES (@IDName,@NewID);
ELSE
    UPDATE dbo.tblIDs 
    SET LastID = @NewID 
    WHERE IDName = @IDName;

COMMIT TRANSACTION;

由于我们的代码永远不会在LastID中为此表添加0记录,因此我们可以假设如果@NewID为1,则意图将新ID添加到列表中,否则我们将更新列表中的现有行.

解决方法

完全可以避免死锁.我的系统中根本没有死锁.有几种方法可以实现这一目标.我将展示如何使用sp_getapplock来消除死锁.我不知道这是否适合你,因为SQL Server是封闭源代码,所以我看不到源代码,因此我不知道我是否已经测试了所有可能的情况.

以下描述了适合我的方法.因人而异.

首先,让我们从一个总是会遇到大量死锁的场景开始.其次,我们将使用sp_getapplock消除它们.这里最重要的一点是对您的解决方案进行压力测试.您的解决方案可能有所不同,但您需要将其暴露给高并发性,我将在稍后进行演示.

先决条件

让我们建立一个包含一些测试数据的表：

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); 
GO 

INSERT INTO dbo.Numbers 
    ( n ) 
        VALUES  ( 1 ); 
GO 
DECLARE @i INT; 
    SET @i=0; 
WHILE @i<21  
    BEGIN 
    INSERT INTO dbo.Numbers 
        ( n ) 
        SELECT n + POWER(2,@i) 
        FROM dbo.Numbers; 
    SET @i = @i + 1; 
    END;  
GO

SELECT n AS ID,n AS Key1,n AS Key2,0 AS Counter1,0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO

（编辑：淮北站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!