Início / dba / Perguntas / 107927
Accepted
sebeid
Asked: 2015-07-24 10:41:11 +0800 CST

Como interpretar o impasse Trace Flag 1222

  • 772

por favor, me oriente como ler este gráfico para descobrir qual é a causa desse impasse. Procurei recursos online, mas não encontrei informações claras. é a primeira vez que vejo esse gráfico

deadlock-list
deadlock victim=process49b0a0558
process-list
process id=process49b0a0558 taskpriority=0 logused=644 waitresource=PAGE: 7:1:17238196  waittime=8444 ownerId=4210681187 transactionname=implicit_transaction lasttranstarted=2015-07-23T11:45:10.107 XDES=0x35f5611b8 lockMode=S schedulerid=1 kpid=37892 status=suspended spid=21656 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-07-23T11:45:10.263 lastbatchcompleted=2015-07-23T11:45:10.243 lastattention=1900-01-01T00:00:00.243 clientapp=Microsoft JDBC Driver for SQL Server hostname=intl.com hostpid=0 loginname=xxx isolationlevel=read committed (2) xactid=4210681187 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
 frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x020000003b05030e975dbade26c8bd0a4bd47787a162ef2a0000000000000000000000000000000000000000
select distinct collegeatt0_.InstanceId as col_0_0_, transcript2_.BarCode as col_1_0_ from dbo.CollegeAttended collegeatt0_ cross join dbo.CollegeLookup collegeloo1_ cross join dbo.Transcript transcript2_ where collegeloo1_.transcript_code=collegeatt0_.Code and transcript2_.CollegeAttendedId=collegeatt0_.Id and (collegeloo1_.school_code in ('444444') or collegeloo1_.school_state='FR' or collegeloo1_.isforeign=1 or collegeatt0_.Code like 'FR%') and transcript2_.ApplicantCASId=@P0     
 frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown     
inputbuf
(@P0 nvarchar(4000))select distinct collegeatt0_.InstanceId as col_0_0_, transcript2_.BarCode as col_1_0_ from dbo.CollegeAttended collegeatt0_ cross join dbo.CollegeLookup collegeloo1_ cross join dbo.Transcript transcript2_ where collegeloo1_.transcript_code=collegeatt0_.Code and transcript2_.CollegeAttendedId=collegeatt0_.Id and (collegeloo1_.school_code in ('444444') or collegeloo1_.school_state='FR' or collegeloo1_.isforeign=1 or collegeatt0_.Code like 'FR%') and transcript2_.ApplicantCASId=@P0            
  process id=process3b11f90c8 taskpriority=0 logused=748 waitresource=KEY: 7:72057594521583616 (0522e5133bfc) waittime=6368 ownerId=4210680811 transactionname=implicit_transaction lasttranstarted=2015-07-23T11:45:09.927 XDES=0x48516a3a8 lockMode=S schedulerid=2 kpid=62220 status=suspended spid=11876 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2015-07-23T11:45:12.380 lastbatchcompleted=2015-07-23T11:45:12.380 lastattention=1900-01-01T00:00:00.380 clientapp=Microsoft JDBC Driver for SQL Server hostname=intl.com hostpid=0 loginname=xxx isolationlevel=read committed (2) xactid=4210680811 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
 frame procname=adhoc line=1 stmtstart=40 sqlhandle=0x02000000dbccbf2e4211c587b6da054c320aafc625b87b730000000000000000000000000000000000000000
select vendortran0_.Id as Id1_17_, vendortran0_.ApplicantCASId as Applican2_17_, vendortran0_.barcode as barcode3_17_, vendortran0_.CreatedDate as CreatedD4_17_, vendortran0_.InstanceId as Instance5_17_, vendortran0_.ReceivedDate as Received6_17_, vendortran0_.TranscriptEvaluationType as Transcri7_17_, vendortran0_.TranscriptFileLocation as Transcri8_17_, vendortran0_.UpdatedDate as UpdatedD9_17_ from VendorTranscriptEvaluation vendortran0_ where vendortran0_.ApplicantCASId=@P0     
 frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown     
inputbuf
(@P0 nvarchar(4000))select vendortran0_.Id as Id1_17_, vendortran0_.ApplicantCASId as Applican2_17_, vendortran0_.barcode as barcode3_17_, vendortran0_.CreatedDate as CreatedD4_17_, vendortran0_.InstanceId as Instance5_17_, vendortran0_.ReceivedDate as Received6_17_, vendortran0_.TranscriptEvaluationType as Transcri7_17_, vendortran0_.TranscriptFileLocation as Transcri8_17_, vendortran0_.UpdatedDate as UpdatedD9_17_ from VendorTranscriptEvaluation vendortran0_ where vendortran0_.ApplicantCASId=@P0            
resource-list
 pagelock fileid=1 pageid=17238196 dbid=7 subresource=FULL objectname=unicas_ux.dbo.Transcript id=lock534943880 mode=IX associatedObjectId=72057594520010752
owner-list
 owner id=process3b11f90c8 mode=IX
waiter-list
 waiter id=process49b0a0558 mode=S requestType=wait
 keylock hobtid=72057594521583616 dbid=7 objectname=unicas_ux.dbo.VendorTranscriptEvaluation indexname=IX_VendorTranscriptEvaluation id=lock43924a180 mode=X associatedObjectId=72057594521583616
owner-list
 owner id=process49b0a0558 mode=X
waiter-list
 waiter id=process3b11f90c8 mode=S requestType=wait
sql-server

2 respostas

  1. Best Answer

    Existem algumas maneiras de exibir uma representação visual do xml do grafo de deadlock.

    Na minha opinião, a melhor ferramenta é o explorador de planos SQL Sentry, que tem uma avaliação gratuita.

    Dependendo da versão (parece que me lembro de 2012), o SQL Server Management Studio também oferece uma visão gráfica do impasse XML.

    Por favor, leia esta ótima explicação de Jonathan Kehayias para obter a essência de como investigar com ferramentas padrão SQL:

    • 3

  2. @Tom V mencionou quase as ótimas referências para solucionar impasses.

    Além deles, como você postou para a saída do sinalizador de rastreamento 1222 dos logs no impasse, você pode analisar isso com a ajuda do artigo de Bart Solução de problemas de impasse, Parte 1 e Solução de problemas de impasse, Parte 2

    Ele explica muito como analisar o log que o ajudará a analisar o impasse, o que deve ser um bom pontapé inicial.

    • 2

relate perguntas