none
Dúvida DeadLock RRS feed

  • Pergunta

  • Boa tarde pessoal.

    Estava com alguns deadlocks, e este pela minha falta de experiência, não consigo resolver:

    04/01/2011 14:17:12,spid4s,Unknown,Deadlock encountered .... Printing deadlock information

    04/01/2011 14:17:12,spid4s,Unknown,Wait-for graph

    04/01/2011 14:17:12,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/01/2011 14:17:12,spid4s,Unknown,Node:1

    04/01/2011 14:17:12,spid4s,Unknown,PAGE: 18:1:627779              CleanCnt:2 Mode:IX Flags: 0x3

    04/01/2011 14:17:12,spid4s,Unknown,Grant List 1:

    04/01/2011 14:17:12,spid4s,Unknown,Owner:0x00000000EEEDF980 Mode: IX       Flg:0x40 Ref:0 Life:02000000 SPID:82 ECID:0 XactLockInfo: 0x00000000FECF4E10

    04/01/2011 14:17:12,spid4s,Unknown,SPID: 82 ECID: 0 Statement Type: UPDATE Line #: 221

    04/01/2011 14:17:12,spid4s,Unknown,Input Buf: Language Event: exec sp_040_batch_alt_shipment_nf3

    04/01/2011 14:17:12,spid4s,Unknown,Requested by:

    04/01/2011 14:17:12,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000107171300 Mode: S SPID:68 BatchID:0 ECID:9 TaskProxy:(0x00000001337A76F0) Value:0xc8ec0fc0 Cost:(0/0)

    04/01/2011 14:17:12,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/01/2011 14:17:12,spid4s,Unknown,Node:2

    04/01/2011 14:17:12,spid4s,Unknown,PAGE: 18:1:627778              CleanCnt:2 Mode:SIU Flags: 0x3

    04/01/2011 14:17:12,spid4s,Unknown,Grant List 1:

    04/01/2011 14:17:12,spid4s,Unknown,Grant List 3:

    04/01/2011 14:17:12,spid4s,Unknown,Owner:0x000000008CD5C240 Mode: S        Flg:0x40 Ref:1 Life:00000000 SPID:68 ECID:9 XactLockInfo: 0x0000000107171340

    04/01/2011 14:17:12,spid4s,Unknown,SPID: 68 ECID: 9 Statement Type: SELECT Line #: 7

    04/01/2011 14:17:12,spid4s,Unknown,Input Buf: No Event:

    04/01/2011 14:17:12,spid4s,Unknown,Requested by:

    04/01/2011 14:17:12,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000FECF4DD0 Mode: IX SPID:82 BatchID:0 ECID:0 TaskProxy:(0x000000009275A538) Value:0x9d5d7800 Cost:(0/2324)

    04/01/2011 14:17:12,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/01/2011 14:17:12,spid4s,Unknown,Victim Resource Owner:

    04/01/2011 14:17:12,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000107171300 Mode: S SPID:68 BatchID:0 ECID:9 TaskProxy:(0x00000001337A76F0) Value:0xc8ec0fc0 Cost:(0/0)

    Vejam que na linha "Input Buf: No Event:", não é especificado nem o objeto que esta envolvido. Porque isto acontece? Existe alguma forma de eu descobrir?
    Obrigado!

    • Movido Gustavo Maia Aguiar sexta-feira, 1 de abril de 2011 19:07 (De:SQL Server - Desenvolvimento Geral)
    sexta-feira, 1 de abril de 2011 18:39

Todas as Respostas

  • Clayton,

    Existe alguma aplicação que faz acesso a estes dados? Qual é o nível de isolamento de banco de dados que o SQL Server esta utilizando para este banco?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    sexta-feira, 1 de abril de 2011 19:02
    Moderador
  • Junior,

    Existe sim uma aplicação que faz acesso, ela esta no IIS.

    O nivel de isolamento do banco é Read committed.

     

    Na sp_040_batch_alt_shipment_nf3 é feito um update na tabela mais usada no sistema, e este update atualiza mais de um registro na tabela, após este update tenho um cursor, após o cursor, outro update e a procedure é finalizada. Ex:

    UPDATE tabela

    Open cursor

    .........

    .........

    Close cursor

    UPDATE tabela

    Fim da procedure

     

    A questão é a seguinte: 

    Os dados somente serão "commitados" quando a procedure terminar, isto faz com que ela fique um tempo considerável com registros em lock.

    Tem alguma possibilidade de mesmo sem abrir uma transação explicitamente, eu finalizar esta transação? Ex:

     

    UPDATE tabela

    COMMIT****

    Open cursor

    .........

    .........

    Close cursor

    UPDATE tabela

    Fim da procedure

     

    sexta-feira, 1 de abril de 2011 19:20
  • Clayton,

    Sugiro você colocar o profile para rodar, para ficar mais claro esse deadlock para você. Ah e nao esqueça se possivel nao abra a interface do profiler diretamente no servidor do banco de dados.

     

    Att,
    Freccia 

    sexta-feira, 1 de abril de 2011 20:43
  • Coloquei o profile, capiturei o deadlock, até mesmo o grafico, e ele não mostra o objeto que esta sendo usado em um dos nós, assim como no log.

     

     

    Clayton

    sábado, 2 de abril de 2011 16:13
  • Clayton,

    Ative o trace flag 1204 e 1222, vai te dar mais detalhes... (depois da solução vc pode remover o 1222 se quiser, pois vai gerar XML do trace flag...)

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    segunda-feira, 4 de abril de 2011 11:24
    Moderador
  • Bom dia Marcelo.


    Fiz o que pediu, e consegui capturar o seguinte:

    <deadlock-list>

     <deadlock victim="process50fb88">

      <process-list>

       <process id="process50fb88" taskpriority="0" logused="0" waitresource="KEY: 18:72057595776729088 

    (e50020e105a2)" waittime="1304" ownerId="2039513742" transactionname="UPDATE" 

    lasttranstarted="2011-04-04T10:42:05.730" XDES="0x135ec4e90" lockMode="U" schedulerid="1" 

    kpid="8180" status="suspended" spid="76" sbid="0" ecid="0" priority="0" trancount="2" 

    lastbatchstarted="2011-04-04T10:42:05.730" lastbatchcompleted="2011-04-04T10:42:04.990" 

    clientapp="Microsoft SQL Server Management Studio - Query" hostname="SIMP20031" hostpid="960" 

    loginname="SIMPRESS\install_signa4" isolationlevel="read committed (2)" xactid="2039513742" 

    currentdb="18" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

        <executionStack>

         <frame procname="adhoc" line="1" stmtend="758" 

    sqlhandle="0x02000000c30c9b13a8b40739fe1e45b708e505c8f703acf9">

    update NOTA_FISCAL

    set TAB_STATUS_TRACKING_ID = 155

    where DOCTO_TRANSPORTE_ID in (162173, 162225, 162188, 162189, 162190, 162191, 162192, 162193, 162194, 162222, 162196, 162197, 162198, 162199, 162200, 162201, 

    162202, 162203, 162204, 162205, 162206, 162207, 162208, 162209, 162210, 162211, 162212, 162213, 162214, 162215, 162216, 162217, 162218,162220 ,162223, 162224)     </frame>

        </executionStack>

        <inputbuf>

    update NOTA_FISCAL

    set TAB_STATUS_TRACKING_ID = 155

    where DOCTO_TRANSPORTE_ID in (162173, 162225, 162188, 162189, 162190, 162191, 162192, 162193, 162194, 162222, 162196, 162197, 162198, 162199, 162200, 162201, 

    162202, 162203, 162204, 162205, 162206, 162207, 162208, 162209, 162210, 162211, 162212, 162213, 162214, 162215, 162216, 162217, 162218,162220 ,162223, 162224)

     

    update NOTA_FISCAL

    set flag_integrado = &apos;P&apos;

    where DOCTO_TRANSPORTE_ID in (162173, 162225, 162188, 162189, 162190, 162191, 162192, 162193, 162194, 162222, 162196, 162197, 162198, 162199, 162200, 162201, 

    162202, 162203, 162204, 162205, 162206, 162207, 162208, 162209, 162210, 162211, 162212, 162213, 162214, 162215, 162216, 162217, 162218,162220 ,162223, 162224)

     

    update DOCTO_TRANSPORTE

    set TAB_STATUS_TRACKING_ID = 155

    where DOCTO_TRANSPORTE_ID in (162173, 162225, 162188, 162189, 162190, 162191, 162192, 162193, 162194, 162222, 162196, 162197, 162198, 162199, 162200, 162201, 

    162202, 162203, 162204, 162205, 162206, 162207, 162    </inputbuf>

       </process>

       <process id="processa03708" taskpriority="0" logused="3436" waitresource="PAGE: 18:1:2641325" 

    waittime="1067" ownerId="2039513641" transactionname="INSERT" 

    lasttranstarted="2011-04-04T10:42:05.687" XDES="0x8ec11d70" lockMode="U" 

    schedulerid="3" kpid="2660" status="suspended" spid="102" sbid="0" ecid="3" 

    priority="0" trancount="0" lastbatchstarted="2011-04-04T10:42:05.687" 

    lastbatchcompleted="2011-04-04T10:42:05.687" clientapp=".Net SqlClient Data Provider" 

    hostname="SIMP20031" hostpid="2280" isolationlevel="read uncommitted (1)" xactid="2039513641" 

    currentdb="18" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128024">

        <executionStack>

         <frame procname="ECARGO.dbo.Sp_040_atu_ult_status_nf02" line="137" 

    stmtstart="10744" stmtend="10974" sqlhandle="0x030012008f1e0171dd15e600b89e00000100000000000000">

    update nota_fiscal

    set tab_situacao_nf_id = @tab_situacao_nf_id

    where nota_fiscal_pai_id = @NOTA_FISCAL_ID     </frame>

         <frame procname="ECARGO.dbo.tr_040_gra_status_tracking_emb" line="274" stmtstart="17002" 

    stmtend="17214" sqlhandle="0x030012002b711e733a02e600b89e00000000000000000000">

    EXEC Sp_040_atu_ult_status_nf02 @tracking_ident, @nota_fiscal_id, @retorno OUTPUT, @msg_ret  OUTPUT     </frame>

         <frame procname="ECARGO.dbo.TRG_INS_TRACKING" line="15" stmtstart="580" stmtend="886" sqlhandle="0x03001200d794925175edf100bb9d00000000000000000000">

    UPDATE tracking SET tracking_id = ins.tracking_ident

    FROM tracking As trk

    INNER JOIN INSERTED As Ins ON trk.tracking_ident = Ins.tracking_ident     </frame>

         <frame procname="ECARGO.dbo.SP_040_WS_GRA_TRACKING4" line="16" stmtstart="1138" stmtend="2742" sqlhandle="0x0300120047e04d6f63799f00b09e00000100000000000000">

    INSERT

    INTO TRACKING (

    TRACKING_ID,

    EMPRESA_ID,

    TAB_STATUS_ID,

    TAB_TIPO_TRACKING_ID,

    DOCTO_TRANSPORTE_ID,

    PEDIDO_ID,

    data_tracking,

    USUARIO_INCL_ID,

    USUARIO_INCL,

    data_incl,

    hora_tracking,

    NOTA_FISCAL_ID,

    TAB_SITUACAO_NF_ID,

    MANIFESTO_ROD_iD

    ) VALUES (

    1, --incluído por trigger

    1,

    1,

    @TAB_TIPO_TRACKING_ID,

    @DOCTO_TRANSPORTE_ID,

    @PEDIDO_ID,

    GETDATE(),--@data_tracking, /*Alterado para pegar a data do banco*/

    @zUSUARIO_ID,

    &apos;SP_040_WS_GRA_TRACKING4&apos;,

    GETDATE(),

    CONVERT(VARCHAR(5),GETDATE(),108),

    @NOTA_FISCAL_ID,

    @TAB_SITUACAO_NF_ID,

    @MANIFESTO_ROD_iD

    )     </frame>

        </executionStack>

        <inputbuf>

        </inputbuf>

       </process>

       <process id="processf197f4c8" taskpriority="0" logused="10000" waittime="1064" schedulerid="3" kpid="9356" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-04T10:42:05.687" lastbatchcompleted="2011-04-04T10:42:05.687" lastattention="2011-04-04T10:28:30.540" clientapp=".Net SqlClient Data Provider" hostname="SIMP20031" hostpid="2280" loginname="SISTEMA" isolationlevel="read uncommitted (1)" xactid="2039513641" currentdb="18" lockTimeout="4294967295" clientoption1="539099168" clientoption2="128024">

        <executionStack>

         <frame procname="ECARGO.dbo.Sp_040_atu_ult_status_nf02" line="137" stmtstart="10744" stmtend="10974" sqlhandle="0x030012008f1e0171dd15e600b89e00000100000000000000">

    update nota_fiscal

    set tab_situacao_nf_id = @tab_situacao_nf_id

    where nota_fiscal_pai_id = @NOTA_FISCAL_ID     </frame>

         <frame procname="ECARGO.dbo.tr_040_gra_status_tracking_emb" line="274" stmtstart="17002" stmtend="17214" sqlhandle="0x030012002b711e733a02e600b89e00000000000000000000">

    EXEC Sp_040_atu_ult_status_nf02 @tracking_ident, @nota_fiscal_id, @retorno OUTPUT, @msg_ret  OUTPUT     </frame>

         <frame procname="ECARGO.dbo.TRG_INS_TRACKING" line="15" stmtstart="580" stmtend="886" sqlhandle="0x03001200d794925175edf100bb9d00000000000000000000">

    UPDATE tracking SET tracking_id = ins.tracking_ident

    FROM tracking As trk

    INNER JOIN INSERTED As Ins ON trk.tracking_ident = Ins.tracking_ident     </frame>

         <frame procname="ECARGO.dbo.SP_040_WS_GRA_TRACKING4" line="16" stmtstart="1138" stmtend="2742" sqlhandle="0x0300120047e04d6f63799f00b09e00000100000000000000">

    INSERT

    INTO TRACKING (

    TRACKING_ID,

    EMPRESA_ID,

    TAB_STATUS_ID,

    TAB_TIPO_TRACKING_ID,

    DOCTO_TRANSPORTE_ID,

    PEDIDO_ID,

    data_tracking,

    USUARIO_INCL_ID,

    USUARIO_INCL,

    data_incl,

    hora_tracking,

    NOTA_FISCAL_ID,

    TAB_SITUACAO_NF_ID,

    MANIFESTO_ROD_iD

    ) VALUES (

    1, --incluído por trigger

    1,

    1,

    @TAB_TIPO_TRACKING_ID,

    @DOCTO_TRANSPORTE_ID,

    @PEDIDO_ID,

    GETDATE(),--@data_tracking, /*Alterado para pegar a data do banco*/

    @zUSUARIO_ID,

    &apos;SP_040_WS_GRA_TRACKING4&apos;,

    GETDATE(),

    CONVERT(VARCHAR(5),GETDATE(),108),

    @NOTA_FISCAL_ID,

    @TAB_SITUACAO_NF_ID,

    @MANIFESTO_ROD_iD

    )     </frame>

        </executionStack>

        <inputbuf>

    Proc [Database Id = 18 Object Id = 1867374663]    </inputbuf>

       </process>

      </process-list>

      <resource-list>

       <keylock hobtid="72057595776729088" dbid="18" objectname="ECARGO.dbo.NOTA_FISCAL" 

    indexname="PK__NOTA_FISCAL__573DED66" id="lockf2b6ec80" mode="X" 

    associatedObjectId="72057595776729088">

        <owner-list>

         <owner id="processf197f4c8" mode="X"/>

        </owner-list>

        <waiter-list>

         <waiter id="process50fb88" mode="U" requestType="wait"/>

        </waiter-list>

       </keylock>

      <pagelock fileid="1" pageid="2641325" dbid="18" objectname="ECARGO.dbo.NOTA_FISCAL" 

    id="lockf1b3ef80" mode="IX" associatedObjectId="72057595776729088">

        <owner-list>

         <owner id="process50fb88" mode="IX"/>

        </owner-list>

        <waiter-list>

         <waiter id="processa03708" mode="U" requestType="wait"/>

        </waiter-list>

       </pagelock>

       <exchangeEvent id="Pipee27b4580" WaitType="e_waitPipeGetRow" nodeId="5">

        <owner-list>

         <owner id="processa03708"/>

        </owner-list>

        <waiter-list>

         <waiter id="processf197f4c8"/>

        </waiter-list>

       </exchangeEvent>

      </resource-list>

     </deadlock>

    </deadlock-list>

    Estou tentando entender a mensagem, poderia me ajudar? Se puder também me indicar alguma leitura que eu consiga aprender a traduzir a informação, serei grato.
    Clayton

     

     

    segunda-feira, 4 de abril de 2011 14:12
  • Pessoal, mudei a sequencia de updates dentro de uma trigger, e pelo que consegui capturar o problema foi resolvido, no entanto acredito que eu tenha criado outro problema com isto, o estranho é que as informações que estão aparecendo no log e no profiler não me detalham os objetos envolvidos, segue as mensagem:

     

    LOG:

    04/04/2011 16:20:38,spid4s,Unknown,Deadlock encountered .... Printing deadlock information

    04/04/2011 16:20:38,spid4s,Unknown,Wait-for graph

    04/04/2011 16:20:38,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/04/2011 16:20:38,spid4s,Unknown,Node:1

    04/04/2011 16:20:38,spid4s,Unknown,Port: 0x00000000FA81BDF0  Xid Slot: 7<c/> Wait Slot: 0<c/> Task: 0x000000008788B288<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1

    04/04/2011 16:20:38,spid4s,Unknown,ResType:ExchangeId Stype:'AND' SPID:61 BatchID:0 ECID:12 TaskProxy:(0x00000000E0A3A0E0) Value:0x8788b288 Cost:(20/0)

    04/04/2011 16:20:38,spid4s,Unknown,SPID: 61 ECID: 12 Statement Type: SELECT Line #: 76

    04/04/2011 16:20:38,spid4s,Unknown,Input Buf: No Event:

    04/04/2011 16:20:38,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/04/2011 16:20:38,spid4s,Unknown,Node:2

    04/04/2011 16:20:38,spid4s,Unknown,Port: 0x00000000DF6ED410  Xid Slot: 1<c/> Wait Slot: 0<c/> Task: 0x00000000FFAA2988<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1

    04/04/2011 16:20:38,spid4s,Unknown,ResType:ExchangeId Stype:'AND' SPID:61 BatchID:0 ECID:17 TaskProxy:(0x00000000E0A3A140) Value:0xffaa2988 Cost:(20/0)

    04/04/2011 16:20:38,spid4s,Unknown,SPID: 61 ECID: 17 Statement Type: SELECT Line #: 76

    04/04/2011 16:20:38,spid4s,Unknown,Input Buf: No Event:

    04/04/2011 16:20:38,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/04/2011 16:20:38,spid4s,Unknown,Node:3

    04/04/2011 16:20:38,spid4s,Unknown,Port: 0x00000000DF6EDF10  Xid Slot: 0<c/> Wait Slot: 2<c/> Task: 0x00000000009E9DC8<c/> (Coordinator)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

    04/04/2011 16:20:38,spid4s,Unknown,ResType:ExchangeId Stype:'AND' SPID:61 BatchID:0 ECID:0 TaskProxy:(0x00000000C8B64510) Value:0x9e9dc8 Cost:(0/10000)

    04/04/2011 16:20:38,spid4s,Unknown,SPID: 61 ECID: 0 Statement Type: SELECT Line #: 76

    04/04/2011 16:20:38,spid4s,Unknown,Input Buf: Language Event: <nl/>--exec stpListaContratoImpressoraTermica NULL<c/> 4174<nl/>--select * from tblPreContrato where cdContrato = 4174<nl/><nl/>CREATE TABLE #ENDERECOSCADASTRO (<nl/> CodigoEndereco int<c/><nl/> Logradouro Varchar(200)<c/><nl/> Numero VARCHAR(5)<c/><nl/> Complemento Varchar(200)<c/><nl/> Bairro V

    04/04/2011 16:20:38,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Unable to cast object of type 'System.DBNull' to type 'System.String'.. Content:

    04/04/2011 16:20:38,spid4s,Unknown,Node:4

    04/04/2011 16:20:38,spid4s,Unknown,Port: 0x00000000FA81B2E0  Xid Slot: 1<c/> Wait Slot: 7<c/> Task: 0x00000000D4B89948<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

    04/04/2011 16:20:38,spid4s,Unknown,ResType:ExchangeId Stype:'AND' SPID:61 BatchID:0 ECID:18 TaskProxy:(0x00000000E0A3A1A0) Value:0xd4b89948 Cost:(0/10000)

    04/04/2011 16:20:38,spid4s,Unknown,SPID: 61 ECID: 18 Statement Type: SELECT Line #: 76

    04/04/2011 16:20:38,spid4s,Unknown,Input Buf: No Event:

     

    PROFILER:

    Lock:Deadlock Chain 70965196 1 0X01 4 simp20112\desenv01 2011-04-04 16:20:38.283 Parallel query worker thread was involved in a deadlock 656886 0 102 - Resource type Exchange

    Lock:Deadlock Chain 70965197 1 0X01 4 simp20112\desenv01 2011-04-04 16:20:38.283 Parallel query worker thread was involved in a deadlock 656886 0 102 - Resource type Exchange

    Lock:Deadlock Chain 70965198 1 0X01 4 simp20112\desenv01 2011-04-04 16:20:38.283 Parallel query worker thread was involved in a deadlock 656886 0 102 - Resource type Exchange

    Lock:Deadlock Chain 70965199 1 0X01 4 simp20112\desenv01 2011-04-04 16:20:38.283 Parallel query worker thread was involved in a deadlock 656886 0 102 - Resource type Exchange

    Agradeço a atenção de todos.
    Clayton

    segunda-feira, 4 de abril de 2011 19:58
  • Clayton,

    Tem certeza que o Profiler não esta registrando nada? Você esta com Trace File ativo?

    Com as trace flags 1204 e 1222 o SQL Server é avisado da necessidade de se monitoramento todos os procedimentos executados, transacionados e comitados, gerando arquivos de monitoramento, se ambas estão ativas todo e qualquer processo de deadlock será registrado.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    segunda-feira, 4 de abril de 2011 23:08
    Moderador