Bazele troubleshooting-ului pentru deadlock - SQL 2005 Server
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.