Início / dba / Perguntas / 226174
Accepted
James D
Asked: 2019-01-03 07:56:09 +0800 CST

Deadlocks de intervalo serializável

  • 772

Ajuda necessária em um problema de impasse herdado!

O código abaixo parece ter sido escrito há algum tempo, em uma tentativa de resolver problemas de contenção em torno da atribuição de novos valores de ID em uma tabela pai [IDs] muito maior/mais gorda, primeiro criando os valores de identidade em um [ID_Stub menor ] tabela. No entanto, a presença de deadlocks contínuos indica que esse código parece estar causando mais problemas do que resolvendo.

Frequentemente, enfrentamos deadlocks em torno de uma instrução INSERT na tabela a seguir (os nomes de tabela e coluna foram ofuscados). A tabela não tem gatilhos ou dependências de chave estrangeira, mas tem um índice clusterizado e um não clusterizado conforme a seguir.

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

A tabela contém uma média de cerca de 70.000 linhas a qualquer momento (um processo é executado para reduzir o número de registros todas as noites).

O nível de isolamento de transação de instância padrão é READ COMMITTED, no entanto, isso é substituído (no procedimento armazenado em que os deadlocks ocorrem) para SERIALIZABLE, juntamente com uma transação implícita. Infelizmente, não podemos considerar a mudança para uma estratégia de bloqueio otimizada, como RCSI, neste estágio.

Toda a transação implícita foi fornecida abaixo para completar, mas os deadlocks estão ocorrendo em torno da instrução INSERT final no ID_Stub no final.

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;

O relatório de deadlock XML é o seguinte.

<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>

O plano de consulta para a primeira instrução pode ser encontrado no link abaixo.

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

Quaisquer dicas ou conselhos sobre a melhor forma de lidar com esses impasses seriam muito apreciados.

sql-server deadlock

2 respostas

  1. Best Answer

    Frequentemente, enfrentamos deadlocks em torno de uma instrução INSERT na tabela a seguir... o procedimento armazenado em que os deadlocks ocorrem [usa] SERIALIZABLE

    Sim. Esse é o comportamento esperado do nível de isolamento SERIALIZABLE. Não é amplamente compreendido, mas os deadlocks são como SERIALIZABLE impõe o nível de isolamento. Ele não impede que sessões simultâneas tentem gravar alterações conflitantes; se duas sessões lerem dados e ambas tentarem alterações conflitantes, ocorrerá um deadlock e somente uma gravação será bem-sucedida.

    Então, se você não quer lidar com impasses, você está usando o modelo de simultaneidade errado.

    Se você simplesmente deseja evitar a execução simultânea dessa transação, forçando as sessões a executá-la uma de cada vez, a maneira mais simples é usar um bloqueio de aplicativo :

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

  2. Você tem duas consultas na tabela afetada por deadlock que possuem cláusulas WHERE muito semelhantes. Este:

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

    E este:

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

    Dependendo dos dados em dbo.ID_Stub, você poderá reduzir a quantidade de bloqueio de intervalo em andamento (que existe devido ao nível de isolamento SERIALIZABLE, como você mencionou) adicionando esses outros dois campos à sua chave de índice não clusterizada.

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

    Se você não puder resolver o problema subjacente de ter que usar o nível de isolamento SERIALIZABLE (David Browne fez uma ótima sugestão com o procedimento getapplock), isso pode mitigar o problema pelo menos.

    Você pode fazer antes e depois de testar com essas consultas específicas por conta própria, verificando os bloqueios obtidos com sp_WhoIsActive @get_locks = 1;) para ver se obtém bloqueios diferentes com o índice mais amplo.

    • 2

relate perguntas