Błąd zakleszczenia nie zwraca kodu zakleszczenia SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Ten błąd pojawia się losowo, gdy jedna z moich witryn jest zajęta. Wiem z grubsza, na których zestawach tabel się dzieje, ale z doświadczenia z innymi programami zwykle zwracam SQL w miejscu impasu. Czy jest jakaś flaga, którą powinienem włączyć, aby tak się stało?

Spróbuję sam debugować impas jako osobną kwestię, ponieważ na razie to moje główne pytanie.

Używam SQL Server 2008 Standard Edition.

sql-server-2008 deadlock

— webnoob
źródło

Czy możesz ponownie uruchomić usługę? Jeśli możesz odesłać usługę, możesz dodać flagę śledzenia 1204 do parametrów uruchamiania, aby zapisać szczegóły impasu w dzienniku programu SQL Server. > 1204: Zwraca zasoby i typy blokad uczestniczących w impasie, a także wpływa na bieżące polecenie. >> Zakres: tylko globalny

— Tevo D

Użyj menedżera konfiguracji. W obszarze SQL Server Services kliknij prawym przyciskiem myszy i otwórz właściwości. Przejdź do zakładki Zaawansowane, parametry uruchamiania. Będziesz miał wpisy dotyczące lokalizacji głównych plików bazy danych i tym podobne. Dodaj, ;-T1204aby flaga śledzenia zakończyła się i ponownie uruchomiła usługę.

— Tevo D

Po co ponownie uruchamiać usługę? DBCC TRACEON (1204, -1)

— Mark Storey-Smith

Od msdn.microsoft.com/en-us/library/ms188396.aspx : Zmiany w zachowaniu: W SQL Server 2000 wystarczy prosty DBCC TRACEON (1204), aby umożliwić raportowanie impasu w dzienniku błędów. W SQL Server 2008 należy włączyć flagę globalnie, ponieważ flaga na poziomie sesji nie jest widoczna dla wątku monitora zakleszczenia.

— Tevo D

@TevoD - -1parametr DBCC TRACEONoznaczający globalny.

— Martin Smith

Odpowiedzi:

Potrzebne dane są zapisywane w domyślnym śledzeniu zdarzeń rozszerzonych.

DECLARE @xml XML

SELECT @xml = target_data
FROM   sys.dm_xe_session_targets
       JOIN sys.dm_xe_sessions
         ON event_session_address = address
WHERE  name = 'system_health'
       AND target_name = 'ring_buffer'

SELECT   
             XEventData.XEvent.query('(data/value/deadlock)[1]')  AS DeadlockGraph,
             CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
              XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM   (SELECT @xml AS TargetData) AS Data
       CROSS APPLY 
       TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent) 
ORDER BY [DateTime] DESC

Chociaż już go nie będzie, jeśli zrestartowałeś usługę -eg, aby zastosować flagę śledzenia lub jeśli bufor był w międzyczasie cykliczny.

Możesz skonfigurować własne śledzenie zdarzeń rozszerzonych, które przechowuje wykres impasu w miejscu docelowym pliku w celu trwałego przechowywania nieulotnego. Przykładowy kod tutaj . Osobiście uważam, że XML impasu jest bardziej przyjazny niż wyjście flagi śledzenia.

Edytować

@MartinC wskazuje w komentarzach, że w instancjach SQL Server, które nie mają wszystkich aktualizacji, może występować problem z generowaniem niepoprawnego XML. Rozwiązaniem tego jest wyszukiwanie i zamiana oraz używanie CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraphna SELECTliście, jak opisano tutaj .
Wayne Sheffield opublikował użyteczny skrypt do rozdrabniania impasu wykres XML w formacie tabelarycznym tutaj .

— Martin Smith
źródło

Niestety EE nie wychwytuje wszystkich zakleszczeń i wydaje się być błędem: connect.microsoft.com/SQLServer/feedback/details/754115/…

— Matt

Odpowiedź Zaakceptowany nie działa dla mnie konsekwentnie. Bufor pierścieniowy najwyraźniej znany jest z upuszczania zdarzeń w pewnych okolicznościach.

ConnectItem

Problemy z buforem pierścieniowym

Pliki zdarzeń dziennika system_health można przeanalizować (z tej odpowiedzi ):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

Pole XdlFile można zapisać w pliku .xdl i wczytać do SSMS. Testowane w Sql Server 2012.

— Crokusek
źródło