Início / dba / Perguntas / 69699
Accepted
Leland Richardson
Asked: 2014-07-04 18:21:17 +0800 CST

Resolvendo deadlock de 2 tabelas relacionadas apenas por meio de exibição indexada

  • 772

Eu tenho uma situação em que estou tendo impasses e acho que reduzi os culpados, mas não tenho certeza do que posso fazer para corrigi-lo.

Isso ocorre em um ambiente de produção executando o SQL Server 2008 R2.

Para lhe dar uma visão um pouco simplificada da situação:

Tenho 3 tabelas conforme definido abaixo:

TABLE activity (
    id, -- PK
    ...
)

TABLE member_activity (
    member_id, -- PK col 1
    activity_id, -- PK col 2
    ...
)

TABLE follow (
    id, -- PK
    follower_id,
    member_id,
    ...
)

A member_activitytabela tem uma chave primária composta definida como member_id, activity_id, porque só preciso procurar dados nessa tabela dessa maneira.

Eu também tenho um índice não clusterizado em follow:

CREATE NONCLUSTERED INDEX [IX_follow_member_id_includes] 
ON follow ( member_id ASC ) INCLUDE ( follower_id )

Além disso, tenho uma visão vinculada ao esquema network_activityque é definida da seguinte forma:

CREATE VIEW network_activity
WITH SCHEMABINDING
AS

SELECT
    follow.follower_id as member_id,
    member_activity.activity_id as activity_id,
    COUNT_BIG(*) AS cb
FROM member_activity
INNER JOIN follow ON follow.member_id = member_activity.member_id
INNER JOIN activity ON activity.id = member_activity.activity_id
GROUP BY follow.follower_id, member_activity.activity_id

Que também possui um índice clusterizado exclusivo:

CREATE UNIQUE CLUSTERED INDEX [IX_network_activity_unique_member_id_activity_id] 
ON network_activity
(
    member_id ASC,
    activity_id ASC
)

Agora, tenho dois procedimentos armazenados em um impasse. Eles passam pelo seguinte processo:

-- SP1: insert activity
-----------------------
INSERT INTO activity (...)
SELECT ... FROM member_activity WHERE member_id = @a AND activity_id = @b
INSERT INTO member_activity (...)


-- SP2: insert follow
---------------------
SELECT follow WHERE member_id = @x AND follower_id = @y
INSERT INTO follow (...)

Esses dois procedimentos são executados em isolamento READ COMMITTED. Consegui consultar a saída de eventos estendidos 1222 e interpretei o seguinte em relação aos impasses:

O SP1 está aguardando um RangeS-Sbloqueio de chave no IX_follow_member_id_includesíndice enquanto o SP2 mantém um bloqueio conflitante (X)

O SP2 está aguardando um Sbloqueio de modo PK_member_activity enquanto o SP1 mantém um bloqueio conflitante (X)

O impasse parece estar acontecendo na última linha de cada consulta (as inserções). O que não está claro para mim é por que o SP1 está querendo um bloqueio no IX_follow-member_id_includesíndice. O único link, para mim, parece ser desta exibição indexada, e é por isso que o incluí.

Qual seria a melhor maneira de evitar que esses impasses aconteçam? Qualquer ajuda seria muito apreciada. Não tenho muita experiência em resolver problemas de impasse.

Por favor, deixe-me saber se há mais alguma informação que eu possa fornecer que possa ajudar!

Desde já, obrigado.

Editar 1: Adicionando mais algumas informações por solicitação.

Aqui está a saída 1222 deste impasse:

<deadlock>
    <victim-list>
        <victimProcess id="process4c6672748" />
    </victim-list>
    <process-list>
        <process id="process4c6672748" taskpriority="0" logused="332" waitresource="KEY: 8:72057594104905728 (25014f77eaba)" waittime="581" ownerId="474698706" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.287" XDES="0x298487970" lockMode="RangeS-S" schedulerid="1" kpid="972" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T10:25:00.283" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698706" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="" line="7" stmtstart="1194" stmtend="1434" sqlhandle="0x02000000a26bb72a2b220406876cad09c22242e5265c82e6" />
                <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 1 --> </inputbuf>
        </process>
        <process id="process6cddc5b88" taskpriority="0" logused="456" waitresource="KEY: 8:72057594098679808 (89013169fc76)" waittime="567" ownerId="474698698" transactionname="INSERT" lasttranstarted="2014-07-03T17:03:12.283" XDES="0x30c459970" lockMode="S" schedulerid="4" kpid="4204" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-03T17:03:12.283" lastbatchcompleted="2014-07-03T17:03:12.283" lastattention="2014-07-03T15:04:55.870" clientapp=".Net SqlClient Data Provider" hostname="WIN08CLYDESDALE" hostpid="4596" loginname="TechPro" isolationlevel="read committed (2)" xactid="474698698" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
                <frame procname="" line="18" stmtstart="942" stmtend="1250" sqlhandle="0x03000800ca458d315ee9130100a300000100000000000000" />
            </executionStack>
            <inputbuf> <!-- SP 2 --> </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594104905728" dbid="8" objectname="" indexname="" id="lock33299fc00" mode="X" associatedObjectId="72057594104905728">
            <owner-list>
                <owner id="process6cddc5b88" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process4c6672748" mode="RangeS-S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594098679808" dbid="8" objectname="" indexname="" id="lockb7e2ba80" mode="X" associatedObjectId="72057594098679808">
            <owner-list>
                <owner id="process4c6672748" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process6cddc5b88" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Nesse caso,

AssociateObjectId 72057594098679808 corresponde a member_activity, PK_member_activity

AssociateObjectId 72057594104905728 corresponde a follow, IX_follow_member_id_includes

Além disso, aqui está uma imagem mais precisa do que o SP1 e o SP2 estão fazendo

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m1 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m1, @activityId, @field1)

IF NOT EXISTS(
    SELECT TOP 1 member_id 
    FROM member_activity 
    WHERE member_id = @m2 AND activity_id = @activityId
)
    INSERT INTO member_activity (member_id, activity_id, field1)
    VALUES (@m2, @activityId, @field1)

também SP2:

-- SP2: insert follow
---------------------

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

Editar 2: depois de reler os comentários, pensei em adicionar algumas informações sobre quais colunas são chaves estrangeiras também ...

  • member_activity.member_idé uma chave estrangeira para uma membertabela
  • member_activity.activity_idé uma chave estrangeira para a activitytabela
  • follow.member_idé uma chave estrangeira para uma membertabela
  • follow.follower_idé uma chave estrangeira para uma membertabela

Atualização 1:

Fiz algumas alterações que pensei que poderiam ajudar a evitar o impasse, sem sorte.

As alterações que fiz foram as seguintes:

-- SP1: insert activity
-----------------------
DECLARE @activityId INT

INSERT INTO activity (field1, field2)
VALUES (@field1, @field2)

SET @activityId = SCOPE_IDENTITY();

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m1 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

MERGE member_activity WITH ( HOLDLOCK ) as target
USING (SELECT @m2 as member_id, @activityId as activity_id, @field1 as field1) as source
    ON target.member_id = source.member_id
    AND target.activity_id = source.activity_id
WHEN NOT MATCHED THEN
    INSERT (member_id, activity_id, field1)
    VALUES (source.member_id, source.activity_id, source.field1)
;

e com SP2:

-- SP2: insert follow
---------------------

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

IF NOT EXISTS(
    SELECT TOP 1 1 
    FROM follow WITH ( UPDLOCK )
    WHERE member_id = @memberId AND follower_id = @followerId
)
    INSERT INTO follow (member_id, follower_id)
    VALUES (@memberId, @followerId)

COMMIT

Com essas duas mudanças, parece que ainda estou tendo impasses.

Se houver mais alguma coisa que eu possa fornecer, por favor me avise. Obrigado.

sql-server deadlock

1 respostas

  1. Best Answer

    O conflito se resume a network_activityser uma exibição indexada que precisa ser mantida (internamente) nas instruções DML. Provavelmente, é por isso que o SP1 está querendo um bloqueio no IX_follow-member_id_includesíndice, pois provavelmente é usado pelo View (parece ser um índice de cobertura para o View).

    Duas opções possíveis:

    1. Considere descartar o índice agrupado na exibição para que não seja mais uma exibição indexada. O benefício de tê-lo supera o custo de manutenção? Você seleciona com frequência suficiente ou o ganho de desempenho de indexá-lo vale a pena? Se você executar esses processos com bastante frequência, talvez o custo seja maior do que o benefício?

    2. Se o benefício de ter a exibição indexada superar o custo, considere isolar as operações DML nas tabelas base dessa exibição. Isso pode ser feito por meio do uso de bloqueios de aplicativos (consulte sp_getapplock e sp_releaseapplock ). Os bloqueios de aplicativos permitem criar bloqueios em torno de conceitos arbitrários. Ou seja, você pode definir @Resourcecomo "network_activity" em ambos os seus Stored Procs, o que os forçará a esperar sua vez. Cada proc seguiria a mesma estrutura:

      BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'network_activity', @LockMode = 'Exclusive';
...current proc code...
EXEC sp_releaseapplock @Resource = 'network_activity';
COMMIT TRANSACTION;

      Você precisa gerenciar os erros / ROLLBACKvocê mesmo (conforme indicado na documentação vinculada do MSDN), portanto, coloque o arquivo TRY...CATCH. Mas, isso permite que você gerencie a situação.
      Observe: sp_getapplock / sp_releaseapplockdeve ser usado com moderação; Bloqueios de aplicativos podem definitivamente ser muito úteis (como em casos como este), mas devem ser usados ​​apenas quando for absolutamente necessário.

    • 6

relate perguntas