SQL Server - 使用聚集索引时如何存储数据页

Question

James D

Asked: 2019-01-03 07:56:09 +0800 CST2019-01-03 07:56:09 +0800 CST 2019-01-03 07:56:09 +0800 CST

可序列化范围死锁

772

继承死锁问题需要帮助！

下面给出的代码似乎已经写了一段时间，试图通过首先在较小的 [ID_Stub ] 桌子。但是，持续死锁的存在表明此代码似乎导致的问题多于解决的问题。

我们经常在下表的 INSERT 语句周围遇到死锁（表和列名已被混淆）。该表没有触发器或外键依赖项，但有一个聚集索引和一个非聚集索引，如下所示。

CREATE TABLE dbo.ID_Stub (
ID int IDENTITY(1,1) NOT NULL,
IDReference nchar(25) NULL,
AdditionalID int NULL,
CreatedBy int NOT NULL,
CreatedOn datetime NOT NULL,
CONSTRAINT PK_ID_Stub PRIMARY KEY CLUSTERED (ID) WITH 
(
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90
)
);
GO

CREATE NONCLUSTERED INDEX idx_IDReference ON dbo.ID_Stub (IDReference) 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 = 70
);
GO

该表在任何时候平均包含大约 70,000 行（一个进程运行以每晚修剪记录数）。

默认的实例事务隔离级别是 READ COMMITTED，但是这被重写（在发生死锁的存储过程中）为 SERIALIZABLE，以及一个隐式事务。不幸的是，我们不能考虑在这个阶段转向优化锁定策略，例如 RCSI。

为了完整起见，下面给出了整个隐式事务，但死锁发生在 ID_Stub 上的最终 INSERT 语句附近。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

-- If a reference has been provided...
IF ISNULL(@IDReference, '') > ''
BEGIN
    IF @ID IS NULL
    BEGIN
        -- Attempt to locate record based on provided reference.
        SELECT @ID = MAX(ID)
        FROM dbo.IDs I
        WHERE I.IDReference = LTRIM(RTRIM(@IDReference))
        AND I.CreatedBy = @CreatedBy
        AND I.AdditionalID = @AdditionalID

        IF @ID IS NULL
        BEGIN
            -- If there is no corresponding record, the subsequent ID creation after the stub failed.
            SET @OriginallyCreated = 
            (
                SELECT MAX(CreatedOn) 
                FROM dbo.ID_Stub IDS 
                WHERE IDS.IDReference = LTRIM(RTRIM(@IDReference))
                AND IDS.CreatedBy = @CreatedBy
                AND IDS.AdditionalID = @AdditionalID
            );

            -- Delete the stub record if created more than 90 seconds ago.
            IF @OriginallyCreated IS NOT NULL
            BEGIN
                IF DATEDIFF(s, @OriginallyCreated, GETDATE()) < 90
                    SELECT @FailureMessage = 'The ID for reference ' + RTRIM(@IDReference) + ' is still being processed.  Please try later.';
                ELSE
                    DELETE dbo.ID_Stub
                    WHERE IDReference = LTRIM(RTRIM(@IDReference))
                    AND CreatedBy = @CreatedBy
                    AND AdditionalID = @AdditionalID;
            END
        END
    END
    ELSE
    BEGIN
        IF NOT EXISTS 
        (
            SELECT ID
            FROM dbo.IDs I
            WHERE I.ID = @ID
            AND I.CreatedBy = @CreatedBy
        )
            SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ')';
        ELSE
            IF NOT EXISTS 
            (
                SELECT ID
                FROM dbo.IDs I
                WHERE I.ID = @ID
                AND I.IDReference = @IDReference
                AND I.CreatedBy = @CreatedBy
            )
                SELECT @FailureMessage = 'The ID does not match the reference you supplied.';
        END
END
ELSE -- No ID Reference provided.
BEGIN
    IF @ID IS NOT NULL
    BEGIN
        IF NOT EXISTS 
        (
            SELECT ID
            FROM dbo.IDs I
            WHERE I.ID = @ID
            AND I.CreatedBy = @CreatedBy
        )
            SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ').';
    END
END

IF @FailureMessage <> ''
BEGIN
    ROLLBACK;
    RETURN 1;
END

-- If it's a new submission, create a stub for it.
IF @ID IS NULL
BEGIN
    INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)
        VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE());

    SELECT @ID = SCOPE_IDENTITY();
END

COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

XML 死锁报告如下。

<deadlock>
    <victim-list>
        <victimProcess id="process1e7fd5d8108"/>
    </victim-list>
    <process-list>
        <process id="process1e7fd5d8108" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373839" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1dfe81c9538" lockMode="RangeI-N" schedulerid="6" kpid="2084" status="suspended" spid="182" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.307" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373839" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000">  INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)       VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE();    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 30 Object Id = 125816106]   </inputbuf>
        </process>
        <process id="process1fd327c7c28" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373837" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1e76d97ebd8" lockMode="RangeI-N" schedulerid="3" kpid="9084" status="suspended" spid="208" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.300" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373837" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000">  INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)       VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE();    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 30 Object Id = 125816106]   </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
            <owner-list>
                <owner id="process1fd327c7c28" mode="RangeS-S"/>
                <owner id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
            <owner-list>
                <owner id="process1e7fd5d8108" mode="RangeS-S"/>
                <owner id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

第一条语句的查询计划可以在下面的链接中找到。

https://www.brentozar.com/pastetheplan/?id=BkVfC8qbN

任何关于如何最好地解决这些死锁的提示或建议将不胜感激。

2 个回答

Voted

David Browne - Microsoft · Answer 1 · 2019-01-03T08:58:38+08:00

Best Answer

David Browne - Microsoft

2019-01-03T08:58:38+08:002019-01-03T08:58:38+08:00

我们经常在下表中的 INSERT 语句周围遇到死锁......发生死锁的存储过程 [使用] SERIALIZABLE

是的。这是 SERIALIZABLE 隔离级别的预期行为。它没有被广泛理解，但死锁是 SERIALIZABLE 强制隔离级别的方式。它不会阻止并发会话尝试写入冲突的更改；如果两个会话读取数据，然后都尝试冲突更改，则会发生死锁，并且只有一个写入成功。

因此，如果您不想处理死锁，则说明您使用了错误的并发模型。

如果您只是想阻止此事务的并发执行，强制会话一次执行它，最简单的方法是使用应用程序锁：

BEGIN TRANSACTION
exec sp_getapplock @Resource = 'MyTransaction',  @LockMode = 'Exclusive';
. . .

9

Josh Darnell · Answer 2 · 2019-01-03T09:43:08+08:00

您对受死锁影响的表有两个查询，它们具有非常相似的 WHERE 子句。这个：

SELECT MAX(CreatedOn) 
FROM dbo.ID_Stub IDS 
WHERE IDS.IDReference = LTRIM(RTRIM(@IDReference))
AND IDS.CreatedBy = @CreatedBy
AND IDS.AdditionalID = @AdditionalID

和这个：

DELETE dbo.ID_Stub
WHERE IDReference = LTRIM(RTRIM(@IDReference))
AND CreatedBy = @CreatedBy
AND AdditionalID = @AdditionalID;

根据中的数据dbo.ID_Stub，您可以通过将其他两个字段添加到非聚集索引键中来减少正在进行的范围锁定的数量（这是由于 SERIALIZABLE 隔离级别，正如您所提到的）。

CREATE NONCLUSTERED INDEX idx_IDReference 
ON dbo.ID_Stub (IDReference, CreatedBy, AdditionalID) 
WITH 
(
    ...all your other options
);

如果您无法解决必须使用 SERIALIZABLE 隔离级别的潜在问题（David Browne对 getapplock 过程提出了很好的建议），那么这至少可以缓解问题。

您可以在测试之前和之后自行测试这些特定查询，检查使用sp_WhoIsActive @get_locks = 1;) 获取的锁，以查看是否使用更广泛的索引获取了不同的锁。

可序列化范围死锁

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

可序列化范围死锁

2 个回答

相关问题