Freigeben über

Bazele troubleshooting-ului pentru deadlock - SQL 2005 Server

  • Artikel

Bazele troubleshooting-ului pentru deadlock - SQL 2005 Server

Deadlock-ul apare atunci cand doua sau mai multe SPID-uri se blocheaza unul pe altul si niciunul nu mai poate fi procesat. Cand mecanismul motorului SQL Server detecteaza un deadlock, unul dintre SPID-uri este ales ca victima si este anulat generand errori de tipul 1205. Erorile pot fi inregistrate in fisierul ErorLog.txt iar transactia SPID-ului anulat este supusa actiunii de roll-back.

Deadlocking-ul poate fi verificat prin urmatoarele elemente:

1. SQL Profiler - capturarea unui trace SQL Profiler dar care nu impacteaza performata masinii (evitarea capturii pe un share pe reatea, evitarea capturarii a unor evenimente cu frecventa mare ca Object:Opened, Lock:Acquired/Released, etc., utilizarea unui template Profiler: GeneralPerformance template, utilizarea de filtre in Profiler Trace pentru a reduce volumul capturii, etc)

 

2. Activarea traceFlagul-ui 1222 care permite vizualizarea detaliilor legate de blocking fisierele erorlog.txt.

Activarea se face prin executarea codului T_SQL :"DBCC TRACEON (1222, -1)".

3. Colectarea fisierelor Erorlog.txt dupa ce un deadlocking a fost reprodus.

Exemplu de deadlocking capturat in ErrorLog:

deadlock-list

 deadlock victim=processdceda8

  process-list

   process id=processdceda8 taskpriority=0 logused=0 waitresource=KEY: 2:72057594051493888 (0400a4427a09) waittime=5000 ownerId=24008914 transactionname=SELECT lasttranstarted=2006-09-08T15:54:22.327 XDES=0x8fd9a848 lockMode=S schedulerid=1 kpid=4404 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2006-09-08T15:54:22.293 lastbatchcompleted=2006-09-08T15:54:22.293 clientapp=OSQL-32 hostname=BARTD2 hostpid=3408 loginname=bartd isolationlevel=read committed (2) xactid=24008914 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

    executionStack

     frame procname=tempdb.dbo.p1 line=2 stmtstart=60 sqlhandle=0x03000200268be70bd

       SELECT c2, c3 FROM t1 WHERE c2 = @p1

     frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000008a4df52d3

       EXEC p1 3

    inputbuf

       EXEC p1 3

   process id=process3c54c58 taskpriority=0 logused=16952 waitresource=KEY: 2:72057594051559424 (0900fefcd2fe) waittime=5000 ownerId=24008903 transactionname=UPDATE lasttranstarted=2006-09-08T15:54:22.327 XDES=0x802ecdd0 lockMode=X schedulerid=2 kpid=4420 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2006-09-08T15:54:22.327 lastbatchcompleted=2006-09-08T15:54:22.310 clientapp=OSQL-32 hostname=BARTD2 hostpid=2728 loginname=bartd isolationlevel=read committed (2) xactid=24008903 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056

    executionStack

     frame procname=tempdb.dbo.p2 line=2 stmtstart=58 sqlhandle=0x030002005fafdb0c

       UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1

     frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000006f878816

       EXEC p2 3

    inputbuf

       EXEC p2 3

  resource-list

   keylock hobtid=72057594051559424 dbid=2 objectname=tempdb.dbo.t1 indexname=idx1 id=lock83642a00 mode=S associatedObjectId=72057594051559424

    owner-list

     owner id=processdceda8 mode=S

    waiter-list

     waiter id=process3c54c58 mode=X requestType=wait

   keylock hobtid=72057594051493888 dbid=2 objectname=tempdb.dbo.t1 indexname=cidx id=lock83643780 mode=X associatedObjectId=72057594051493888

    owner-list

     owner id=process3c54c58 mode=X

    waiter-list

     waiter id=processdceda8 mode=S requestType=wait

4. Un alt element important este Statistics Clone Copy a bazei de date.

Troubleshooting:

1. In SQL Profiler Trace se identifica query-urile care genereaza deadlocking.

Verificati daca deadlocking-ul nu se incadreaza in articolul KB: You may receive error message 8650 when you run a query that uses intra-query parallelism<https://support.microsoft.com/kb/837983>

2. Asigurati-va ca se foloseste minumul necesar de trasaction isolation level

3. Query-urile implicate in deadlocking trebuies supuse tool-urilor Index Tunning Advisor si Database tuning Advisor. Agaugati index-urile recomandate de aceste tool-uri . Mai mult de jumatate din cazurile de deadlocking sunt rezolvate cu Index Tuning Advisor.