Início / dba / Perguntas / 80088
Accepted
GoodwinSQL
Asked: 2014-10-14 11:47:17 +0800 CST

Inserir apenas impasses

  • 772

Temos uma aplicação inserindo dados em uma tabela. Infelizmente, estamos obtendo impasses e os impasses vêm apenas de inserções. Estamos vendo as inserções obterem bloqueios de chave em uma ordem diferente em um índice não clusterizado que está causando o problema.

Por que os inserts estão se comportando dessa forma e o que devemos fazer para tentar amenizar os impasses? Qualquer ajuda ou insight é apreciado.

No exemplo abaixo, há apenas duas inserções envolvidas, mas tivemos até 4 inserções diferentes envolvidas em um impasse.

Aqui está o gráfico de impasse:

    <deadlock>
    <victim-list>
        <victimProcess id="process3ab355868" />
    </victim-list>
    <process-list>
        <process id="process3ab355868" taskpriority="0" logused="1184" waitresource="KEY: 5:72057594043629568 (6234ed5bf036)" waittime="7493" ownerId="92332106" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:43.060" XDES="0x123699668" lockMode="X" schedulerid="3" kpid="3540" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.333" lastbatchcompleted="2014-10-13T12:37:44.333" lastattention="1900-01-01T00:00:00.333" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92332106" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
        <process id="process14b38c928" taskpriority="0" logused="2564" waitresource="KEY: 5:72057594043629568 (275232b7b238)" waittime="7491" ownerId="92325909" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:39.567" XDES="0x16b38b988" lockMode="X" schedulerid="3" kpid="3668" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.337" lastbatchcompleted="2014-10-13T12:37:44.337" lastattention="1900-01-01T00:00:00.337" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92325909" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock17bc3a480" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process14b38c928" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process3ab355868" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock10735ce00" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process3ab355868" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process14b38c928" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Aqui está a tabela DDL:

CREATE TABLE [table1](
    [col0] [int] IDENTITY(1,1) NOT NULL,
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [decimal](15, 4) NULL,
    [col4] [datetime2](7) NOT NULL,
    [col5] [varchar](8) NOT NULL,
    [col6] [varchar](30) NOT NULL,
    [col7] [datetime2](7) NOT NULL,
    [col8] [varchar](8) NOT NULL,
    [col9] [varchar](30) NOT NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [col0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [unique_index] UNIQUE NONCLUSTERED 
(
    [col2] ASC,
    [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col4]
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col7]
GO
sql-server sql-server-2012

2 respostas

  1. Best Answer

    Estou respondendo minha própria pergunta aqui porque finalmente descobrimos o problema.

    Versão resumida: adicionamos uma terceira coluna ao índice não clusterizado. Os impasses desapareceram.

    Versão longa:

    Primeiro, verifique a postagem de dinamite do blog de James Rowland-Jones sobre colisão de hash de bloqueio (minha explicação não chegará nem perto da qualidade dele).

    Da postagem do blog:

    Quando o SQL Server precisa bloquear uma linha, ele cria um valor de hash baseado nos valores de chave da tabela. É esse valor de hash que é usado pelo gerenciador de bloqueio e significa que ele tem um único valor para verificar ao verificar se uma linha já está bloqueada.

    A colisão de hash de bloqueio ocorre quando valores de hash duplicados são gerados.

    Depois de fazer uma análise mais profunda de muitos gráficos de deadlock, notamos que muitos dos valores de hash da chave WAITRESOURCE (os valores entre parênteses) eram os mesmos. Comecei a fazer uma pequena lista para acompanhar:

    waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)

    Com certeza, estávamos obtendo muitos valores de hash duplicados de diferentes gráficos de impasse. Decidi examinar os dados nas duas colunas (col2 e col1) do índice unique_index (onde os impasses estavam ocorrendo). Toda a tabela DDL está acima na pergunta.

    A coluna col2 sempre terá um valor de 1-6 para um único valor na coluna col1. Então isso começou a fazer sentido. Havia uma variedade limitada de dados disponíveis para o SQL gerar valores de hash - o que explica por que estávamos obtendo valores de hash duplicados.

    Uma das correções que JRJ mencionou no blog foi adicionar uma coluna adicional ao índice. Isso adiciona alguma diversidade aos dados e oferece mais opções para o algoritmo de hash. Felizmente, conseguimos adicionar uma coluna create_timestamp ao índice e manter a mesma exclusividade que tínhamos com as duas colunas. ESTRONDO! Depois de adicionar a terceira coluna ao índice, os impasses desapareceram.

    Observação: um dos comentários no blog sugeriu desabilitar o bloqueio de linha no índice. Nós tentamos isso primeiro. Ele eliminou os impasses, mas levou a mais bloqueios e reduziu a taxa de transferência geral em cerca de 40 a 50%, portanto, não gostamos dessa opção para o nosso sistema. No entanto, em um banco de dados com carga de trabalho mais leve, isso pode funcionar bem.

    Espero que tudo isso faça sentido.

    • 7

  2. Não tenho certeza se isso poderia realmente ser a resposta, mas não posso postar isso como comentário, então colocar isso

    Se você vir o código do seu aplicativo, é como

    @P1 nvarchar(4000)
,@P2 nvarchar(4000),
@P3 datetime2,
@P4 nvarchar(4000),
@P5 nvarchar(4000),
@P6 decimal(38,1),
@P7 int,
@P8 int)
INSERT INTO OBC.MBL_CPU_POS_MSR_ATB (
CRT_S, 
CRT_PGM_C, 
CRT_UID, 
LST_UPD_S, 
LST_UPD_PGM_C,
 LST_UPD_UID, 
MSR_ATB_VAL, 
MBL_CPU_POS_I, 
POS_MSR_TYP_I) 
VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS

    Se você vir que declarou @p4,@p5...@P6como, nvarchar()mas se vir a definição da tabela

    CREATE TABLE [OBC].[MBL_CPU_POS_MSR_ATB](
    [MBL_CPU_POS_MSR_ATB_I] [int] IDENTITY(1,1) NOT NULL,
    [POS_MSR_TYP_I] [int] NOT NULL,
    [MBL_CPU_POS_I] [int] NOT NULL,
    [MSR_ATB_VAL] [decimal](15, 4) NULL,
    [CRT_S] [datetime2](7) NOT NULL,
    [CRT_UID] [varchar](8) NOT NULL,
    [CRT_PGM_C] [varchar](30) NOT NULL,
    [LST_UPD_S] [datetime2](7) NOT NULL,
    [LST_UPD_UID] [varchar](8) NOT NULL,
    [LST_UPD_PGM_C] [varchar](30) NOT NULL,

    A coluna em que estão inserindo valor é declarada como varchare o valor que você está passando é nvarchar. Nenhuma coluna da sua tabela tem nvarchartipo de dados. Se isso for verdade, a conversão implícita acontecerá e as varreduras de índice acontecerão em vez de buscas mantendo bloqueios (especialmente quando você tiver update ) por mais tempo. Eu acho que isso pode ser a possível causa do impasse.

    Há também trancount = 2, o que significa que há transações não confirmadas e, claro, bit se o ajuste de consulta for necessário.

    Ambas as transações que participaram do impasse estavam disputando o mesmo recursowaitresource="KEY: 5:72057594043629568 (6234ed5bf036)"

    • 0

relate perguntas