Neu SQL zum Ermitteln von Deadlocks

[xadoX]

Wir haben immer mal wieder Abbrüche in der Wawi und ich würde mir gerne einmal die Deadlocks dazu anschauen.
Mit welchem SQL-Befehl kann ich mir die Deadlocks anschauen?

 

[mh1]

Eine "Suche nach Deadlocks" ist ja sehr allgemein formuliert

Grundsätzlich tritt ein Deadlock auf wenn ein Prozess Ressourcen anfordert, die von einem anderen Prozess gesperrt sind. Das ist also zunächst mal nichts Schlechtes und im Falle einer Datenbank absolut wichtig, um die Datenintegrität sicher zu stellen.

Habt ihr denn auf eurem SQL-Server viele offene Transaktionen, die immer offen bleiben und niemals commited werden (bzw. rollbacked)????
Wenn es das ist nach was du suchst, könntest du z.b. mit folgendem Query anfangen:
SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Vielleicht willst du dir Details über die Deadlocks anzeigen lassen,
Dazu müsstest du das Trace Flag 1204 setzen.
Bzw. mit dem Trace Flag 1222 Details im XML Format rausschreiben.

Vielleicht findest du, was du suchst, wenn du im SQL-Profiler eine Ablaufverfolgung mit der Vorlage "TSQL_Locks" startest 🤔

Vielleicht willst du aber auch einfach nur mitlesen, welche Abfragen an den SQL-Server geschickt werden, wenn du z.B. in der Wawi einen Artikel öffnest.
Das kannst du auch wiederum mit dem SQL-Profiler. Oder du nutzt das DBtool, das mit der Wawi installiert wird.

Wenn du also genauer beschreibst, wie das Ergebnis deiner Abfrage aussehen soll, bzw. was genau du analysieren willst, kann dir sicher jemand was dazu sagen.
Aber so ist deine Frage zu ungenau (jedenfalls für mich - vielleicht wissen aber andere hier sofort, nach was du suchst 😅)

 

[xadoX]

Vielen Dank für deine ausführliche Nachricht.

Ich habe die Deadlocks links im Baum im SSMS gesucht und mittlerweile gefunden.

 

[mh1]

An welchen Stellen entstehen denn diese Deadlocks bei euch?

Ich habe gerade mal spaßeshalber alle unsere Serverprotokolle durchsucht, denn der SQL-Server sollte ja ein Eriegnis mit der ID 1205 generieren, wenn er ein Deadlock erkennt und ein Rollback für den Opferprozess anstößt. Ich habe aber keine einzige Meldung bei uns gefunden.

Wieviele Anwender habt ihr? Welche Version?

 

[xadoX]

Wir sind ca. 50 Anwender, die parallel arbeiten. Wir sind noch auf der 1.5.55.0.
Es gab in der Vergangenheit Probleme mit dem Zusatztool Victor (Bestandsabgleich) und der Wawi. Aber aktuell haben wir im Schnitt 15 Deadlocks am Tag.

EDIT: Hier mal der Screenshot, der meine obige Frage beantwortet.

 

[recent.digital]

Man kann im SQL-Server selber noch die Read Commited Momentaufnahme und die Momentaufnahmen Isolation einschalten.
Das kann mit dem SQL Management Studio eingeschaltet werden.

Durch diese Einstellungen wird der Server stabiler und führt die Abfragen zuverlässiger aus, ohne Deadlocks.

 

[mh1]

@xadoX
Produziert also der Bestandsabgleich mit dem Zusatztool die Deadlocks?

Kann denn dein Tool zum Bestandsabgleich den Deadlock dann lösen, also prüft es auf die entsprechende Error ID und reagiert angemessen drauf, oder wie wie wirken sich die Deadlocks aus?

 

[xadoX]

Man kann im SQL-Server selber noch die Read Commited Momentaufnahme und die Momentaufnahmen Isolation einschalten.
Das kann mit dem SQL Management Studio eingeschaltet werden.

Durch diese Einstellungen wird der Server stabiler und führt die Abfragen zuverlässiger aus, ohne Deadlocks.

Wo findet man diese Einstellungen?

@xadoX
Produziert also der Bestandsabgleich mit dem Zusatztool die Deadlocks?

Kann denn dein Tool zum Bestandsabgleich den Deadlock dann lösen, also prüft es auf die entsprechende Error ID und reagiert angemessen drauf, oder wie wie wirken sich die Deadlocks aus?

Das Tool hatte in der Vergangenheit diese Probleme ausgelöst. Mittlerweile sind es aber eher SQL Jobs, die zum Teil alle X Minuten auf der DB ausgeführt werden, die auf einen Deadlock laufen.
So aktualisiere ich per SQL Job z.B. die Bearbeitungszeit bei gewissen Herstellern, je nachdem, ob das Produkt noch verfügbaren Eigenbestand hat oder nicht (Gibt diverse Gründe, warum nicht über die Lieferantenlieferzeit nutzen)

Hier mal ein konkretes Beispiel.

Mein SQL-Job macht folgendes:

--Jako kLieferant = 24
update tartikel set nBearbeitungszeit = 5 where nistvater = 0 and kartikel in(
    select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
)and nbearbeitungszeit <> 5 and kartikel in(
    select kartikel from tlagerbestand where kartikel in (
        select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
    )and (fLagerbestandEigen - finAuftraegen) > 1
) and kartikel not in (select kartikel from tartikelattribut where kattribut = 24)

Ein Deadlock von eben sieht so aus:

<deadlock>
 <victim-list>
  <victimProcess id="process28bd48c1088" />
 </victim-list>
 <process-list>
  <process id="process28bd48c1088" taskpriority="0" logused="4324" waitresource="OBJECT: 5:206779944:12 " waittime="4540" ownerId="107618166" transactionname="user_transaction" lasttranstarted="2023-01-12T09:15:13.437" XDES="0x28f83bd4428" lockMode="IS" schedulerid="13" kpid="1936" status="suspended" spid="173" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-01-12T09:15:13.433" lastbatchcompleted="2023-01-12T09:15:13.437" lastattention="1900-01-01T00:00:00.437" clientapp="JTL-Wawi C# - Sven Braun (16)" hostname="WIN-MOJH10SLCKB" hostpid="3980" loginname="sa" isolationlevel="read committed (2)" xactid="107618166" currentdb="5" currentdbname="eazybusiness" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="eazybusiness.FulfillmentNetwork.spStockUpdate" line="77" stmtstart="4678" stmtend="6616" sqlhandle="0x030005002c1edf50a9765a0175ae000001000000000000000000000000000000000000000000000000000000">
INSERT INTO FulfillmentNetwork.tStockRef
        (
            kArtikel,
             kBenutzer,
            cJfsku,
             kBuchungsart,
             kWarenLagerPlatz,
             kLieferantenBestellungPos,
            fAnzahl,
            dCreatedAt,
            nChangeState,
             cLieferscheinNr,
            cOwnerId,
            dMHD,
            cCharge,
            cKommentar,
             kWarenLagerEingang,
             kSessionId,
             cFulfillerStockChangeId,
             nType
        )
        SELECT kArtikel,
                kBenutzer,
                cJfsku,
                kBuchungsart,
                kWarenLagerPlatz,
                kLieferantenBestellungPos,
                fAnzahl,
                dCreatedAt,
                0 AS nChangeState,
                cLieferscheinNr,
                cOwnerId,
                dMHD,
                cCharge,
                cKommentar,
                    kWarenLagerEingang,
                    kSessionId,
                NEWID() AS cFulfillerStockChangeId,
                2 as nType
        FROM @stockUpdate    </frame>
    <frame procname="eazybusiness.dbo.spWarenlagerEingangSchreiben" line="302" stmtstart="25554" stmtend="25702" sqlhandle="0x030005004ca5636fd3765a0175ae000001000000000000000000000000000000000000000000000000000000">
EXEC FulfillmentNetwork.spStockUpdate @Daten = @fulfillmentStockUpdateDate    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 5 Object Id = 1868801356]   </inputbuf>
  </process>
  <process id="process286e33c4ca8" taskpriority="0" logused="34101044" waitresource="PAGE: 5:1:3546347 " waittime="1995" ownerId="107612946" transactionname="UPDATE" lasttranstarted="2023-01-12T09:15:11.850" XDES="0x28ddd458428" lockMode="U" schedulerid="44" kpid="10228" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-01-12T09:15:00.377" lastbatchcompleted="2023-01-12T09:15:00.377" lastattention="1900-01-01T00:00:00.377" clientapp="SQLAgent - TSQL JobStep (Job 0x1B99FADDFBEFF04388D047312F6A626D : Step 1)" hostname="WIN-MOJH10SLCKB" hostpid="8944" loginname="NT SERVICE\SQLAgent$JTLWAWI" isolationlevel="read committed (2)" xactid="0" currentdb="5" currentdbname="eazybusiness" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="eazybusiness.dbo.tgr_tArtikel_Sync_INSUP" line="254" stmtstart="27772" stmtend="28602" sqlhandle="0x03000500e28c25328e775a0175ae000000000000000000000000000000000000000000000000000000000000">
UPDATE tArtikelShop
            SET tArtikelShop.nAktion = tArtikelShop.nAktion | 1,
                tArtikelShop.cInet = 'Y',
                tArtikelShop.nInBearbeitung = 0
            FROM dbo.tArtikelShop
            JOIN #tgrtArtikelSync ON #tgrtArtikelSync.kArtikel = tArtikelShop.kArtikel
            WHERE #tgrtArtikelSync.nConnectorBlock = 1
                AND (tArtikelShop.cInet = 'N'
                OR tArtikelShop.nAktion IN (0,2,4,6,8)
                OR tArtikelShop.nInBearbeitung = 1    </frame>
    <frame procname="adhoc" line="389" stmtstart="42218" stmtend="43468" sqlhandle="0x02000000ba4a6022534e0705c9feedd7c82ae19ac1a56f080000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
SET QUOTED_IDENTIFIER ON

--Jako kLieferant = 24
update tartikel set nBearbeitungszeit = 5 where nistvater = 0 and kartikel in(
    select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
)and nbearbeitungszeit &lt;&gt; 5 and kartikel in(
    select kartikel from tlagerbestand where kartikel in (
        select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
    )and (fLagerbestandEigen - finAuftraegen) &lt; 1
) and kartikel not in (select kartikel from tartikelattribut where kattribut = 24)


--Jako Merchandise kLieferant = 75
update tartikel set nBearbeitungszeit = 5 where nistvater = 0 and kartikel in(
    select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 75
)and nbearbeitungszeit &lt;&gt; 5 and kartikel in(
    select kartikel from tlagerbestand where kartikel in (
        select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 75
    )and (fLagerbestandEigen - finAuftraegen) &lt; 1
) and kartikel not in (select kartikel from tartikelattribut where katt   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="12" objid="206779944" subresource="FULL" dbid="5" objectname="eazybusiness.dbo.tArtikel" id="lock28da7612380" mode="X" associatedObjectId="206779944">
   <owner-list>
    <owner id="process286e33c4ca8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process28bd48c1088" mode="IS" requestType="wait" />
   </waiter-list>
  </objectlock>
  <pagelock fileid="1" pageid="3546347" dbid="5" subresource="FULL" objectname="eazybusiness.dbo.tArtikelShop" id="lock28da68f5a80" mode="IX" associatedObjectId="72057707754422272">
   <owner-list>
    <owner id="process28bd48c1088" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process286e33c4ca8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

 

[mh1]

Zunächst mal muss ich vorrausschicken, dass ich das XML nicht allzu genau analysiert habe ... 😊

...aber ich glaub, ich würde das mal genauer anschauen:

...
<inputbuf>
SET QUOTED_IDENTIFIER ON

--Jako kLieferant = 24
update tartikel set nBearbeitungszeit = 5 where nistvater = 0 and kartikel in(
select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
)and nbearbeitungszeit &lt;&gt; 5 and kartikel in(
select kartikel from tlagerbestand where kartikel in (
select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 24
)and (fLagerbestandEigen - finAuftraegen) &lt; 1
) and kartikel not in (select kartikel from tartikelattribut where kattribut = 24)


--Jako Merchandise kLieferant = 75
update tartikel set nBearbeitungszeit = 5 where nistvater = 0 and kartikel in(
select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 75
)and nbearbeitungszeit &lt;&gt; 5 and kartikel in(
select kartikel from tlagerbestand where kartikel in (
select tartikel_kartikel from tliefartikel where tLieferant_kLieferant = 75
)and (fLagerbestandEigen - finAuftraegen) &lt; 1
) and kartikel not in (select kartikel from tartikelattribut where katt

</inputbuf>
....

Also ich habe dein Query wirklich nicht genauer angeschaut, es ist nur eher so "mein erstes Gefühl" und ich würde hier, wenn ich die Ursache der Deadlooks suchen müsste, diese Versschachtelungen mal genauer anschauen.
Prüfe mal, ob du sogenannte Bookmark Lookup's generierst, denn diese provozieren gerne Deadlocks.
Halte dich aber nicht zu sehr an dem Thema 'Bookmark Lookup' auf (weil wie gesagt nur so ein Schuss ins Blaue meinerseits).

Grundsätzlich gilt:
Schau dir bei komplexen Queries auch immer den Ausführungsplan an und wie auf die beteiligten Tabellen zugegriffen wird.
Wird ein TableScan Operator ausgeführt? Wird ein zusätzlicher Non-Clustered Index benötigt?...

 

[SebastianB]

Also, ein Deadlock ist beim MS SQL Server jetzt nicht so wahnsinnig ungewöhnlich. Eine Applikation sollte auf den Fehler reagieren und den Befehl nach einer gewissen Wartezeit nochmals ausführen. Das macht z.B: die JTL-Wawi in den meisten Fällen so...

Die Deadlocks werden vom SQL Server im system_health- Log protokolliert - aber gerade der RingPuffer läuft gerne komplett mit anderen Events voll, so dass man dort die Deadlocks dann nicht mehr findet.

Ich empfehle für die Deadlock eine eigene Event-Sitzung anzulegen, z.B. so

IF NOT EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'JTL_DeadlockCollector')
BEGIN   
    CREATE EVENT SESSION [JTL_DeadlockCollector] ON SERVER
    ADD EVENT sqlserver.xml_deadlock_report
    ADD TARGET package0.event_file(SET filename=N'JTL_DeadlockCollector',max_file_size=(200))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

    ALTER EVENT SESSION [JTL_DeadlockCollector] ON SERVER STATE = START;
END ELSE BEGIN
    ALTER EVENT SESSION [JTL_DeadlockCollector] ON SERVER STATE = STOP;
    DROP EVENT SESSION [JTL_DeadlockCollector] ON SERVER;
END"

(Das Ding legt die Sitzung an und wenn man es nochmal ausführt wird die wieder gelöscht)

Auslesen kann man die Deadlocks aus allen Quellen dann z.B: wie folgt:

;WITH XESessions AS (
    SELECT * FROM sys.dm_xe_sessions
    WHERE dm_xe_sessions.name IN ('JTL_DeadlockCollector', 'system_health')
),
RingBuffersTargetData AS (
    SELECT
        CAST(dm_xe_session_targets.target_data AS XML) AS TargetData
    FROM XESessions
    JOIN sys.dm_xe_session_targets ON dm_xe_session_targets.event_session_address = XESessions.address
    WHERE dm_xe_session_targets.target_name = 'ring_buffer'
),
RingBufferDeadlocks AS (
    SELECT XEvent.query('.').value('(event/@timestamp)[1]', 'datetime') AS [timestamp_utc],
    CAST(XEvent.query('.').query('(event/data/value/deadlock)') AS NVARCHAR(MAX)) AS [event_data]   
    FROM RingBuffersTargetData
    CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name=""xml_deadlock_report""]') AS XEventData(XEvent)
),
EventFileTargetData AS (
    SELECT
        CAST(dm_xe_session_targets.target_data AS XML) AS TargetData
    FROM XESessions
    JOIN sys.dm_xe_session_targets ON dm_xe_session_targets.event_session_address = XESessions.address
    WHERE dm_xe_session_targets.target_name = 'event_file'
),
EventFiles AS (
    SELECT XEvent.query('.').value('(File/@name)[1]', 'nvarchar(max)') AS [name]
    FROM EventFileTargetData
    CROSS APPLY TargetData.nodes('EventFileTarget/File') AS XEventData(XEvent)
),
AllEventFiles AS (
    SELECT DISTINCT IIF(CHARINDEX('\', REVERSE([name])) > 13, REVERSE(SUBSTRING(REVERSE([name]), CHARINDEX('\', REVERSE([name]))-13, 1000000)) + '*.xel', [name]) AS [name]
    FROM EventFiles
),
EventFilesDeadlocks AS (
    SELECT [timestamp_utc], CAST(CAST([event_data] AS XML).query('/event/data/value/deadlock') AS NVARCHAR(MAX)) AS [event_data]
    FROM AllEventFiles
    CROSS APPLY sys.fn_xe_file_target_read_file(AllEventFiles.[name], null, null, null)
    WHERE [object_name] = 'xml_deadlock_report'
),
Deadlocks AS (
    SELECT * FROM RingBufferDeadlocks
    UNION ALL
    SELECT * FROM EventFilesDeadlocks
),
OrderedDeadlocks AS (
    SELECT [timestamp_utc],
        [event_data],
        ROW_NUMBER() OVER (PARTITION BY [event_data] ORDER BY [timestamp_utc] DESC) AS RowNumber
    FROM Deadlocks
),
UniqueDeadlocks AS (
    SELECT TOP(100)
        [event_data] AS Content,
        [timestamp_utc] AS [Timestamp]
    FROM OrderedDeadlocks   
    WHERE RowNumber = 1
    ORDER BY [timestamp_utc] DESC
)
SELECT * FROM UniqueDeadlocks

Das JTL-Diag-Tool hat auch einen Reiter für das Auslesen der Deadlocks (das Ding kann auch die Event-Sitzung anlegen). Der gruppiert dann auch die Deadlocks (allerdings ist in der aktuellen Version die grafische Anzeige kaputt).

Um Deadlocks zu vermeiden gibt es auch mehrere Ansätze - am häufigsten sind die Parallelisierungseinstellungen falsch gesetzt: Maximaler Grad der Parallelität sollte auf (Anzahl der Cores / 2) und Kostenschwellenwert für Parallelität auf 50 und ggf. höher (ich kann tendenziell mit allem zwischen 30 und 100 leben). Danach müsste man dann schauen, ob man am Sever selber was optimieren kann oder ob man Indizes setzen muss...

hat man die Möglichkeit, direkt Einfluss auf die Abfragen zu nehmen, hat man an der Stelle schon Möglichkeiten einzugreifen...

 

[recent.digital]

Danke für die Fachinfo @SebastianB

 

[mh1]

@xadoX :

Wie @SebastianB schon geschrieben hat ist das Auftreten von Deadlocks an sich nichts Besonderes.
Eines der netten Features beim SQL-Server ist ja der Datenbank-Engine-Deadlockmonitor, der regelmäßig nach Aufgaben sucht, die sich in einem Deadlock befinden und diese werden dann auch meist zuverlässig aufgelöst.
Die spannende Frage ist doch, warum die Deadlocks bei dir zum Absturz der Wawi führen.
Und da die Wawi ja laut @SebastianB auch im Regelfall auf die Deadlocks reagiert, würde ich an deiner Stelle erstmal die selbsterstellten Transaktionen (update tartikel set nBearbeitungszeit usw.) genauer untersuchen.