Gids voor impasses

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-database in Microsoft Fabric Preview

In dit artikel worden impasses in de database-engine uitgebreid besproken. Impasses worden veroorzaakt door concurrerende, gelijktijdige vergrendelingen in de database, vaak in transacties met meerdere stappen. Zie de gids voor transactievergrendeling en rijversiebeheer voor meer informatie over transacties en vergrendelingen.

Zie Impasses in Azure SQL Database en SQL Database in Fabric analyseren en voorkomen voor meer specifieke informatie over het identificeren en voorkomen van impasses in Azure SQL Database en SQL Database in Fabric.

Impasses begrijpen

Er treedt een impasse op wanneer twee of meer taken elkaar permanent blokkeren doordat elke taak een vergrendeling heeft op een resource die de andere taken proberen te vergrendelen. Voorbeeld:

• Transactie A verkrijgt een gedeelde vergrendeling op rij 1.

• Transactie B verkrijgt een gedeelde vergrendeling op rij 2.

• Transactie A vraagt nu een exclusieve vergrendeling op rij 2 aan en wordt geblokkeerd totdat transactie B is voltooid en de gedeelde vergrendeling op rij 2 vrijgeeft.

• Transactie B vraagt nu een exclusieve vergrendeling op rij 1 aan en wordt geblokkeerd totdat transactie A is voltooid en de gedeelde vergrendeling op rij 1 vrijgeeft.

Transactie A kan pas worden voltooid als transactie B is voltooid, maar transactie B wordt geblokkeerd door transactie A. Deze voorwaarde wordt ook wel een cyclische afhankelijkheid genoemd: Transactie A heeft een afhankelijkheid van transactie B en transactie B sluit de cirkel door een afhankelijkheid van transactie A te hebben.

Beide transacties in een impasse wachten voor altijd, tenzij de impasse wordt verbroken door een extern proces. De deadlock-monitor van de database-engine controleert periodiek of er taken in een deadlock zijn geraakt. Als de monitor een cyclische afhankelijkheid detecteert, kiest deze een van de taken als slachtoffer en beëindigt de transactie met een fout. Hierdoor kan de andere taak de transactie voltooien. De toepassing met de transactie die is beëindigd met een fout, kan de transactie opnieuw proberen, wat meestal wordt voltooid nadat de andere impassetransactie is voltooid.

Blokkeringen worden vaak verward met deadlocks. Wanneer een transactie een vergrendeling aanvraagt voor een resource die is vergrendeld door een andere transactie, wacht de aanvragende transactie totdat de vergrendeling wordt vrijgegeven. Standaard treedt er geen time-out op voor transacties in de database-engine, tenzij LOCK_TIMEOUT deze is ingesteld. De aanvragende transactie wordt geblokkeerd, maar loopt niet vast, omdat de aanvragende transactie niets heeft gedaan om de transactie die eigenaar is van de vergrendeling te blokkeren. Uiteindelijk voltooit en geeft de eigentransactie de vergrendeling vrij, waarna de aanvraagtransactie het slot krijgt en verdergaat. Impasses worden bijna onmiddellijk opgelost, terwijl blokkades in theorie onbeperkt kan voortduren. Deadlocks worden soms een dodelijke omarming genoemd.

Een impasse kan optreden op elk systeem met meerdere threads, niet alleen op een relationeel databasebeheersysteem, en kan zich voordoen voor andere resources dan vergrendelingen op databaseobjecten. Een thread in een multithreaded besturingssysteem kan bijvoorbeeld een of meer resources verkrijgen, zoals blokken geheugen. Als de resource die wordt verkregen momenteel eigendom is van een andere thread, moet de eerste thread mogelijk wachten tot de thread die eigenaar is van de doelresource vrijgeeft. De wachtende thread staat bekend als afhankelijk van de thread die de eigenaar is van die specifieke bron. In een instance van de database-engine kunnen sessies deadlocks ervaren bij het verkrijgen van niet-databronnen zoals geheugen of threads.

In de afbeelding heeft transactie T1 een afhankelijkheid van transactie T2 voor de Part tabelvergrendelingsresource. Op dezelfde manier heeft transactie T2 een afhankelijkheid van transactie T1 voor de Supplier tabelvergrendelingsresource. Omdat deze afhankelijkheden een cyclus vormen, is er een impasse tussen transacties T1 en T2.

Hier volgt een algemenere illustratie van een impasse:

• Taak T1 heeft een vergrendeling op resource R1 (aangegeven met de pijl van R1 naar T1) en heeft een vergrendeling aangevraagd voor resource R2 (aangegeven door de pijl van T1 naar R2).

• Taak T2 heeft een vergrendeling op resource R2 (aangegeven door de pijl van R2 naar T2) en heeft een vergrendeling aangevraagd voor resource R1 (aangeduid met de pijl van T2 naar R1).

• Omdat geen van beide taken kan doorgaan totdat een resource beschikbaar is en geen van beide resources kan worden vrijgegeven totdat een taak doorgaat, bestaat er een impassestatus.

Resources die een deadlock kunnen veroorzaken

Elke gebruikerssessie kan namens zichzelf één of meer taken uitvoeren, waarbij elke taak resources kan verkrijgen of wachten om deze te verkrijgen. De volgende typen resources kunnen blokkering veroorzaken die kan leiden tot een impasse.

• Locks. Wachten op het verkrijgen van vergrendelingen op resources, zoals objecten, pagina's, rijen, metagegevens en toepassingen, kan een impasse veroorzaken. Transactie T1 heeft bijvoorbeeld een gedeelde (S) vergrendeling op rij r1 en wacht op een exclusieve (X) vergrendeling op r2. Transactie T2 heeft een gedeelde (S) vergrendeling op r2 en wacht op een exclusieve (X) vergrendeling op rij r1. Dit resulteert in een vergrendelingscyclus waarin T1 en T2 wachten tot elkaar de vergrendelde resources vrijgeeft.

• Werkthreads. Een taak in de wachtrij die wacht op een beschikbare worker thread kan een deadlock veroorzaken. Wanneer de taak in de wachtrij resources beheert die alle werkdraad blokkeren, ontstaat er een impasse. Sessie S1 start bijvoorbeeld een transactie, verkrijgt een gedeelde (S) vergrendeling op rij r1 en gaat vervolgens in slaapstand. Actieve sessies die worden uitgevoerd op alle beschikbare worker threads proberen exclusieve (X) vergrendelingen op rij r1 te verkrijgen. Omdat sessie S1 geen worker thread kan verkrijgen, kan hij de transactie niet voltooien en de vergrendeling op rij r1 niet vrijgeven. Dit resulteert in een impasse.

• Memory. Wanneer gelijktijdige aanvragen wachten op geheugentoelagen die niet vervuld kunnen worden met het beschikbare geheugen, kan er een deadlock optreden. Twee gelijktijdige query's, Q1 en Q2, worden bijvoorbeeld uitgevoerd als door de gebruiker gedefinieerde functies die respectievelijk 10 MB en 20 MB geheugen verkrijgen. Als elke query 30 MB nodig heeft en het totale beschikbare geheugen 20 MB is, moeten Q1 en Q2 wachten tot elkaar geheugen vrijgeeft, wat resulteert in een impasse.

• Resources gerelateerd aan de uitvoering van parallelle query's. Coördinator-, producent- of consumententhreads die zijn gekoppeld aan een exchange-poort kunnen elkaar blokkeren, wat meestal een impasse veroorzaakt wanneer er ten minste één ander proces wordt opgenomen dat geen deel uitmaakt van de parallelle query. Wanneer een parallelle query wordt uitgevoerd, bepaalt de database-engine ook de mate van parallelle uitvoering en het aantal vereiste werkthreads op basis van de huidige workload. Als de systeemworkload onverwacht verandert, bijvoorbeeld wanneer nieuwe query's worden uitgevoerd op de server of het systeem geen werkrolthreads meer heeft, kan er een impasse optreden.

• Multiple Active Result Sets (MARS)-resources. Deze middelen worden gebruikt om het afwisselen van meerdere actieve verzoeken onder MARS te beheersen. Zie Using Multiple Active Result Sets (MARS) in SQL Server Native Clientvoor meer informatie.

  • Gebruikersbron. Wanneer een thread wacht op een resource die mogelijk wordt beheerd door een gebruikerstoepassing, wordt de resource beschouwd als een externe of gebruikersresource en wordt deze behandeld als een vergrendeling.

  • Sessie mutex. De taken die in één sessie worden uitgevoerd, zijn afgewisseld, wat betekent dat slechts één taak op een bepaald moment binnen de sessie kan worden uitgevoerd. Voordat de taak kan worden uitgevoerd, moet deze exclusieve toegang hebben tot de sessie-mutex.

  • Transactie mutex. Alle taken die in één transactie worden uitgevoerd, zijn verweven, wat betekent dat er op een bepaald moment slechts één taak binnen de transactie kan worden uitgevoerd. Voordat de taak kan worden uitgevoerd, moet deze exclusieve toegang hebben tot de transactiemutex.

    Om een taak onder MARS uit te voeren, moet deze de sessiemutex verkrijgen. Als de taak wordt uitgevoerd in het kader van een transactie, moet deze de transactiemutex verkrijgen. Dit garandeert dat slechts één taak tegelijk actief is in een bepaalde sessie en een bepaalde transactie. Zodra de vereiste mutexes zijn verkregen, kan de taak worden uitgevoerd. Wanneer de taak is voltooid of in het midden van de aanvraag resulteert, wordt eerst de transactie mutex, gevolgd door de sessie mutex, in omgekeerde volgorde van overname vrijgegeven. Er kunnen echter deadlocks optreden met deze hulpbronnen. In de volgende pseudocode worden twee taken, gebruikersaanvraag U1 en gebruikersaanvraag U2, uitgevoerd in dezelfde sessie.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");
    

    De opgeslagen procedure die wordt uitgevoerd op basis van de gebruikersaanvraag U1 heeft de mutex van de sessie verkregen. Als het lang duurt voordat de opgeslagen procedure wordt uitgevoerd, wordt ervan uitgegaan door de database-engine dat de opgeslagen procedure wacht op invoer van de gebruiker. Gebruikersaanvraag U2 wacht op de sessiemutex, terwijl de gebruiker wacht op de resultaatset van U2 en U1 wacht op een gebruikersresource. Dit is een impassestatus die logisch wordt geïllustreerd als:

    

Deadlocks kunnen ook optreden wanneer een tabel wordt gepartitioneerd en de LOCK_ESCALATION parameter ALTER TABLE is ingesteld op AUTO. Wanneer LOCK_ESCALATION op AUTO is ingesteld, neemt de gelijktijdigheid toe doordat de Database Engine tabelpartities kan vergrendelen op HoBT-niveau in plaats van op tabelniveau. Wanneer afzonderlijke transacties echter partitievergrendelingen in een tabel bevatten en ergens op de andere transactiepartitie een vergrendeling willen, veroorzaakt dit een impasse. Dit type impasse kan worden vermeden door in te stellen LOCK_ESCALATION op TABLE. Deze instelling vermindert echter gelijktijdigheid doordat grote updates naar een partitie moeten wachten op een tabelvergrendeling.

Detectie van impasses

Alle resources die worden vermeld in de sectie Resources die een impasse kunnen vormen, kunnen deelnemen aan het detectieschema voor impasses van de database-engine. Detectie van deadlocks wordt uitgevoerd door een lockmonitor-thread die periodiek een zoekopdracht initieert door alle taken in een exemplaar van de databasemotor. In de volgende punten wordt het zoekproces beschreven:

• Het standaardinterval is 5 seconden.

• Als de vergrendelingsmonitor-thread impasses vindt, daalt het detectie-interval voor impasses van 5 seconden tot 100 milliseconden, afhankelijk van de frequentie van impasses.

• Als de vergrendelingsmonitorthread stopt met het vinden van deadlocks, verhoogt de Database Engine de intervallen tussen zoekopdrachten naar 5 seconden.

• Als er een impasse wordt gedetecteerd, wordt ervan uitgegaan dat de nieuwe threads die moeten wachten op een vergrendeling de impassecyclus binnengaan. De eerste paar vergrendelingen wachten nadat een impasse is gedetecteerd, activeren onmiddellijk een impassezoekopdracht in plaats van te wachten op het volgende detectie-interval voor impasses. Als het huidige interval bijvoorbeeld 5 seconden is en er zojuist een deadlock is gedetecteerd, wordt de deadlockdetector direct geactiveerd bij de volgende vergrendelingswachttijd. Als deze vergrendeling deel uitmaakt van een impasse, wordt deze direct gedetecteerd in plaats van tijdens de volgende impassezoekopdracht.

De database-engine voert doorgaans alleen periodieke impassedetectie uit. Omdat het aantal impasses in het systeem meestal klein is, helpt periodieke impassedetectie de overhead van de detectie van impasses in het systeem te verminderen.

Wanneer de vergrendelingsmonitor een impassezoekopdracht voor een bepaalde thread start, wordt de resource geïdentificeerd waarop de thread wacht. De vergrendelingsmonitor vindt vervolgens de eigenaren voor die specifieke resource en gaat recursief verder met het zoeken naar deadlocks voor die threads totdat er een cyclus wordt gevonden. Een cyclus die op deze manier wordt geïdentificeerd, vormt een impasse.

Nadat een impasse is gedetecteerd, beëindigt de database-engine een impasse door een van de threads te kiezen als een impasseslachtoffer. De Database Engine beëindigt de huidige batch die wordt uitgevoerd voor de thread, rolt de transactie van het deadlockslachtoffer terug en retourneert fout 1205 naar de toepassing. Het terugdraaien van de transactie voor het deadlock-slachtoffer verwijdert alle vergrendelingen die door de transactie worden vastgehouden. Hierdoor kunnen de transacties van de andere threads worden gedeblokkeerd en doorgaan. De foutmelding 1205 (impassedoelwit) registreert informatie over het type resources dat bij een impasse betrokken is.

De database-engine kiest standaard de transactie die het minst duur is om terug te draaien als het deadlock-slachtoffer. Een gebruiker kan ook de prioriteit van sessies in een impassesituatie opgeven met behulp van de SET DEADLOCK_PRIORITY instructie. DEADLOCK_PRIORITY kan worden ingesteld op LOW, NORMALof HIGH, of kan ook worden ingesteld op een geheel getal in het bereik van -10 tot 10. In bepaalde gevallen kan de database-engine ervoor kiezen om de deadlockprioriteit voor een korte duur te wijzigen om betere gelijktijdigheid te bereiken.

De prioriteit van de impasse wordt standaard ingesteld op NORMAL, of 0. Als twee sessies verschillende impasseprioriteiten hebben, wordt de transactie op de sessie met de lagere prioriteit gekozen als het impasseslachtoffer. Als beide sessies dezelfde impasseprioriteit hebben, wordt de transactie die het duurst is om terug te draaien gekozen. Als sessies die betrokken zijn bij de impassecyclus dezelfde impasseprioriteit en dezelfde kosten hebben, wordt een slachtoffer willekeurig gekozen. Een taak die wordt teruggedraaid kan niet als een deadlockslachtoffer worden gekozen.

Wanneer u werkt met de Common Language Runtime (CLR), detecteert de deadlock monitor automatisch deadlocks voor synchronisatiebronnen (monitoren, lezer/schrijver-vergrendeling en thread-join) die worden benaderd in beheerde procedures. De impasse wordt echter opgelost door een uitzondering te genereren in de procedure die is geselecteerd als het impasseslachtoffer. Het is belangrijk om te begrijpen dat de uitzondering resources die momenteel eigendom zijn van het slachtoffer niet automatisch vrijgeeft; de resources moeten expliciet worden vrijgegeven. In overeenstemming met het uitzonderingsgedrag kan de uitzondering die wordt gebruikt om een impasseslachtoffer te identificeren, worden gevangen en genegeerd.

Hulpprogramma's voor deadlockinformatie

Voor het weergeven van deadlockgegevens biedt de database-engine monitoringtools in de vorm van de xml_deadlock_report uitgebreide gebeurtenis, twee traceringsvlaggen en de impassegrafiekgebeurtenis in SQL Profiler.

De xml_deadlock_report uitgebreide gebeurtenis is de aanbevolen methode voor het vastleggen van impassegegevens.

Uitgebreide gebeurtenis impasse

In SQL Server 2012 (11.x) en latere versies moet de xml_deadlock_report uitgebreide gebeurtenis worden gebruikt in plaats van de gebeurtenisklasse impassegrafiek in SQL Trace of SQL Profiler.

De system_health gebeurtenissessie legt standaard gebeurtenissen vast xml_deadlock_report . Deze gebeurtenissen bevatten de impassegrafiek. Omdat de system_health sessie standaard is ingeschakeld, hoeft u geen afzonderlijke gebeurtenissessie te configureren om impassegegevens vast te leggen.

De vastgelegde impassegrafiek heeft doorgaans drie afzonderlijke knooppunten:

• victim-list. De proces-ID van het deadlockslachtoffer.
• process-list. Informatie over alle processen die betrokken zijn bij de impasse.
• resource-list. Informatie over de resources die betrokken zijn bij de impasse.

U kunt de event_file doelgegevens van de system_health sessie bekijken in Management Studio. Als er xml_deadlock_report gebeurtenissen zijn opgetreden, geeft Management Studio een grafische weergave weer van de taken en resources die betrokken zijn bij een impasse, zoals te zien is in het volgende voorbeeld:

De volgende query kan alle deadlock-incidenten weergeven die zijn vastgelegd door het ring_buffer doel van de system_health sessie:

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       xdr.query('.') AS event_data
FROM (SELECT CAST ([target_data] AS XML) AS target_data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

Hier is het resultatenoverzicht.

In het volgende voorbeeld ziet u een voorbeeld van de uitvoer uit de event_data kolom:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Traceringsvlag 1204 en traceringsvlag 1222

Wanneer impasses optreden en traceringsvlag 1204 of traceringsvlag 1222 is ingeschakeld, worden impassedetails gerapporteerd in het foutenlogboek van SQL Server. Traceringsvlag 1204 rapporteert impassegegevens die zijn opgemaakt door elk knooppunt dat betrokken is bij de impasse. Traceringsvlag 1222 formatteert impassegegevens, eerst op processen en vervolgens op resources. Het is mogelijk om beide traceringsvlagmen in te schakelen om twee weergaven van dezelfde impasse-gebeurtenis te verkrijgen.

Naast het definiëren van de eigenschappen van traceringsvlagken 1204 en 1222, toont de volgende tabel ook de overeenkomsten en verschillen.

Voorbeeld van traceringsvlag 1204

In het volgende voorbeeld ziet u de uitvoer wanneer traceringsvlag 1204 is ingeschakeld. In dit geval is de tabel in Node 1 een heap zonder indexen en is de tabel in Node 2 een heap met een niet-geclusterde index. De indexsleutel in Node 2 wordt bijgewerkt wanneer de impasse optreedt.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Traceringsvlag 1222-voorbeeld

In het volgende voorbeeld ziet u de uitvoer wanneer traceringsvlag 1222 is ingeschakeld. In dit geval is één tabel een heap zonder indexen en de andere tabel is een heap met een niet-geclusterde index. In de tweede tabel wordt de indexsleutel bijgewerkt wanneer de impasse optreedt.

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
   transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
   sbid=0 ecid=0 priority=0 transcount=2
   lastbatchstarted=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-05T11:22:42.733
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310444 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380
   waitresource=KEY: 6:72057594057457664 (350007a4d329)
   waittime=5015 ownerId=310462 transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-05T11:22:44.077
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310462 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Gebeurtenis van profiler-impassegrafiek

SQL Profiler heeft een gebeurtenis die een grafische weergave weergeeft van de taken en resources die betrokken zijn bij een impasse. In het volgende voorbeeld ziet u de uitvoer van SQL Profiler wanneer de impassegrafiekgebeurtenis is ingeschakeld.

De functies SQL Profiler en SQL Trace worden afgeschaft en vervangen door uitgebreide gebeurtenissen. Uitgebreide gebeurtenissen hebben een kleinere overhead voor prestaties en kunnen beter worden geconfigureerd dan SQL Trace. Overweeg het gebruik van de gebeurtenis Extended Events deadlock in plaats van het traceren van impasses in SQL Profiler.

Zie Lock:Deadlock Event Class voor meer informatie over de impasse. Zie Deadlock Graphs (SQL Server Profiler) opslaan voor meer informatie over impassegrafieken van SQL Profiler.

Uitgebreide gebeurtenissen bieden equivalenten van SQL Trace-gebeurtenisklassen. Zie De Extended Events-equivalenten van SQL Trace gebeurtenisklassen voor meer informatie. Uitgebreide gebeurtenissen worden aanbevolen via SQL Trace.

Impasses afhandelen

Wanneer een instantie van de Database Engine een transactie kiest als slachtoffer van een deadlock, wordt de lopende batch beëindigd, de transactie teruggedraaid en fout 1205 geretourneerd aan de applicatie. Het geretourneerde bericht is als volgt gestructureerd:

Your transaction (process ID #...) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Omdat elke toepassing die Transact-SQL query's indient, kan worden gekozen als het impasseslachtoffer, moeten toepassingen een fouthandler hebben die fout 1205 kan verwerken. Als een toepassing de fout niet afhandelt, kan de toepassing zich niet bewust zijn van het terugdraaien van de transactie.

Door een fouthandler te implementeren die fout 1205 onderschept, kan een toepassing impasses afhandelen en herstelacties uitvoeren (bijvoorbeeld automatisch de query die bij de impasse betrokken was) opnieuw verzenden.

De toepassing moet kort worden onderbroken voordat de query opnieuw wordt ingediend. Dit geeft de andere transactie die betrokken is bij de impasse de kans om te voltooien en zijn vergrendelingen vrij te geven. Het willekeurig maken van de duur van de pauze minimaliseert de kans dat een impasse opnieuw optreedt wanneer de opnieuw verzonden query de benodigde vergrendelingen aanvraagt. De fouthandler kan bijvoorbeeld worden geprogrammeerd om voor een willekeurige duur tussen één en drie seconden te pauzeren.

Afhandelen met TRY...CATCH

U kunt TRY...CATCH gebruiken om deadlocks te behandelen. Fout 1205 kan door het CATCH blok worden opgevangen.

Voor meer informatie, zie Afhandeling van impasses.

Deadlocks minimaliseren

Hoewel impasses niet volledig kunnen worden vermeden, kan het volgen van bepaalde coderingsconventies de kans op genereren van een impasse minimaliseren. Het minimaliseren van impasses kan de transactiedoorvoer verhogen en de systeemoverhead verminderen omdat er minder transacties zijn:

• Teruggedraaid, het ongedaan maken van al het werk dat door deze transactie is uitgevoerd.
• Opnieuw worden verzonden door de toepassingen omdat ze zijn teruggedraaid tijdens een impasse.

Om impasses te minimaliseren:

• Toegang tot objecten in dezelfde volgorde.
• Vermijd gebruikersinteractie bij transacties.
• Houd transacties kort en in één batch.
• Vermijd hogere isolatieniveaus, zoals REPEATABLE READ en SERIALIZABLE wanneer dit niet nodig is.
• Gebruik een isolatieniveau op basis van rijversiebeheer.
  • Schakel de READ_COMMITTED_SNAPSHOT databaseoptie in om rijversiebeheer te gebruiken voor transacties met behulp van het READ COMMITTED isolatieniveau.
  • Gebruik isolatietransacties voor momentopnamen.
• Gebruik gebonden verbindingen.

Objecten in dezelfde volgorde openen

Als alle gelijktijdige transacties toegang krijgen tot objecten in dezelfde volgorde, zijn impasses minder waarschijnlijk. Als bijvoorbeeld twee gelijktijdige transacties een vergrendeling op de Supplier tabel verkrijgen en vervolgens in de Part tabel, wordt één transactie in de Supplier tabel geblokkeerd totdat de andere transactie is voltooid. Nadat de eerste transactie is doorgevoerd of teruggedraaid, gaat de tweede door en treedt er geen impasse op. Het gebruik van opgeslagen procedures voor alle gegevenswijzigingen kan de volgorde van toegang tot objecten standaardiseren.

Gebruikersinteractie in transacties voorkomen

Vermijd transacties met gebruikersinteractie, omdat de snelheid van batches die worden uitgevoerd zonder tussenkomst van de gebruiker veel sneller is dan de snelheid waarmee een gebruiker handmatig moet reageren op query's, zoals het beantwoorden van een prompt voor een parameter die door een toepassing is aangevraagd. Dat verslechtert de systeemdoorvoer omdat eventuele vergrendelingen die door de transactie worden gehandhaafd, alleen worden vrijgegeven wanneer de transactie wordt doorgevoerd of teruggedraaid. Zelfs als er geen impasse optreedt, worden andere transacties die toegang hebben tot dezelfde resources geblokkeerd terwijl wordt gewacht tot de transactie is voltooid.

Houd transacties kort en in één batch

Er treedt meestal een impasse op wanneer meerdere langlopende transacties gelijktijdig in dezelfde database worden uitgevoerd. Hoe langer de transactie duurt, hoe langer exclusieve of updatevergrendelingen actief blijven, waardoor andere activiteiten worden geblokkeerd en mogelijke deadlock-situaties ontstaan.

Door transacties in één batch te bewaren, worden netwerkrondes tijdens een transactie geminimaliseerd, waardoor mogelijke vertragingen bij het voltooien van de transactie door clientverwerking worden verminderd.

Hogere isolatieniveaus vermijden

Bepaal of een transactie op een lager isolatieniveau kan worden uitgevoerd. Met READ COMMITTED kan een transactie gegevens lezen die eerder door een andere transactie zijn gelezen (maar niet gewijzigd), zonder te wachten tot die transactie is voltooid. READ COMMITTED bevat gedeelde vergrendelingen voor een kortere duur dan een hoger isolatieniveau, zoals SERIALIZABLE. Dit vermindert vergrendelingsconflicten.

Isolatieniveau op basis van rijversies gebruiken

Wanneer de READ_COMMITTED_SNAPSHOT databaseoptie is ingesteld ON, gebruikt een transactie die onder het READ COMMITTED isolatieniveau wordt uitgevoerd rijversiebeheer in plaats van gedeelde vergrendelingen tijdens leesbewerkingen.

Isolatie van momentopnamen maakt ook gebruik van rijversiebeheer, waarbij geen gedeelde vergrendelingen worden gebruikt tijdens leesbewerkingen. Voordat een transactie kan worden uitgevoerd onder isolatie van momentopnamen, moet de ALLOW_SNAPSHOT_ISOLATION databaseoptie worden ingesteld ON.

Gebruik isolatieniveaus op basis van rijversies om deadlocks te minimaliseren die kunnen optreden tussen lees- en schrijfbewerkingen.

Afhankelijke verbindingen gebruiken

Met behulp van afhankelijke verbindingen kunnen twee of meer verbindingen die door dezelfde toepassing worden geopend, met elkaar samenwerken. Eventuele vergrendelingen die zijn verkregen door de secundaire verbindingen, worden bewaard alsof ze zijn verkregen door de primaire verbinding en omgekeerd. Daarom blokkeren ze elkaar niet.

Een impasse veroorzaken

Mogelijk moet u een impasse veroorzaken voor leer- of demonstratiedoeleinden.

Het volgende voorbeeld werkt in de AdventureWorksLT2019 voorbeelddatabase met het standaardschema en de standaardgegevens wanneer READ_COMMITTED_SNAPSHOT is ingeschakeld. Als u dit voorbeeld wilt downloaden, gaat u naar AdventureWorks-voorbeelddatabases.

Zie Geoptimaliseerde vergrendeling en deadlocks voor een voorbeeld dat een deadlock veroorzaakt wanneer geoptimaliseerde vergrendeling is ingeschakeld.

Als u een impasse wilt veroorzaken, moet u twee sessies verbinden met de AdventureWorksLT2019-database. We verwijzen naar deze sessies als sessie A en sessie B. U kunt deze twee sessies maken door twee queryvensters te maken in SQL Server Management Studio (SSMS).

Voer in sessie A de volgende batch uit. Deze code begint met een expliciete transactie en voert een instructie uit waarmee de SalesLT.Product tabel wordt bijgewerkt. Hiervoor verkrijgt de transactie een updatevergrendeling (U) voor de in aanmerking komende rijen in een tabel SalesLT.Product die vervolgens worden geconverteerd naar exclusieve (X) vergrendelingen. We laten de transactie openstaan.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Voer nu in sessie B de volgende batch uit. Deze code begint niet expliciet met een transactie. In plaats daarvan werkt het in autocommit transactiemodus. Met deze instructie wordt de tabel SalesLT.ProductDescription bijgewerkt. De update heeft een updatevergrendeling (U) voor de in aanmerking komende rijen in de SalesLT.ProductDescription tabel. De query wordt samengevoegd met andere tabellen, inclusief de SalesLT.Product tabel.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Om deze update te voltooien, heeft Sessie B gedeelde (S) vergrendelingen nodig voor rijen in de tabel SalesLT.Product, inclusief de rijen die zijn vergrendeld door Sessie A. Sessie B wordt geblokkeerd op SalesLT.Product.

Ga terug naar sessie A. Voer de volgende UPDATE instructie uit. Deze instructie wordt uitgevoerd als onderdeel van de eerder geopende transactie.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

De tweede update-instructie in Sessie A- wordt geblokkeerd door Sessie B- op de SalesLT.ProductDescription.

Sessie A en Sessie B blokkeren elkaar nu wederzijds. Geen van beide transacties kan doorgaan, omdat ze elk een resource nodig hebben die door de andere is vergrendeld.

Na een paar seconden identificeert de impassemonitor dat de transacties in sessie A en sessie B elkaar wederzijds blokkeren en dat geen van beide vooruitgang kan boeken. U ziet dat er een impasse optreedt, met Sessie A gekozen als het impasseslachtoffer. sessie B is succesvol voltooid. Er wordt een foutbericht weergegeven in het queryvenster van Sessie A met tekst die vergelijkbaar is met het volgende voorbeeld:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Als er geen impasse wordt gemeld, controleer of READ_COMMITTED_SNAPSHOT is ingeschakeld in uw voorbeelddatabase. Er kunnen deadlocks optreden in elke databaseconfiguratie, maar voor dit voorbeeld moet READ_COMMITTED_SNAPSHOT ingeschakeld zijn.

U kunt de details van de impasse bekijken in het ring_buffer doel van de system_health gebeurtenissessie, die standaard is ingeschakeld en actief is in SQL Server en Azure SQL Managed Instance. Houd rekening met de volgende query:

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

U kunt de XML in de Deadlock_XML kolom in SSMS weergeven door de cel te selecteren die als hyperlink wordt weergegeven. Sla deze uitvoer op als een .xdl bestand, sluit het en open het .xdl bestand vervolgens opnieuw in SSMS voor een visuele impassegrafiek. De impassegrafiek moet er ongeveer uitzien als in de volgende afbeelding.

Geoptimaliseerde vergrendelingen en deadlocks

Bij geoptimaliseerde vergrendeling worden pagina- en rijvergrendelingen niet vastgehouden tot het einde van de transactie. Ze worden vrijgegeven zodra een rij wordt bijgewerkt. Daarnaast, als READ_COMMITTED_SNAPSHOT is ingeschakeld, worden U updatevergrendelingen niet gebruikt. Als gevolg hiervan wordt de kans op impasses verminderd.

Het vorige voorbeeld veroorzaakt geen impasse wanneer geoptimaliseerde vergrendeling is ingeschakeld, omdat deze afhankelijk is van de updatevergrendelingen (U).

Het volgende voorbeeld kan worden gebruikt om een impasse te veroorzaken voor een database waarvoor geoptimaliseerde vergrendeling is ingeschakeld.

Maak eerst een voorbeeldtabel en voeg gegevens toe.

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);

De volgende T-SQL-batches, uitgevoerd in volgorde in twee afzonderlijke sessies, maken een impasse.

In sessie 1:

BEGIN TRANSACTION xactA;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

In sessie 2:

BEGIN TRANSACTION xactB;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

In sessie 1:

UPDATE t2
    SET b = b + 100
WHERE a = 2;

In sessie 2:

UPDATE t2
    SET b = b + 20
WHERE a = 1;

In dit geval bevat elke sessie een exclusieve (X) vergrendeling op zijn eigen TID-resource (transactie-id) en wacht op de gedeelde (S) vergrendeling op de andere TID, wat resulteert in een impasse.

Het volgende verkorte impasserapport bevat elementen en kenmerken die specifiek zijn voor geoptimaliseerde vergrendeling. Onder elke resource in het impasserapport <resource-list>rapporteert elk <xactlock> element de onderliggende resources en TID-vergrendelingsgegevens van elk lid van een impasse.

<deadlock>
 <victim-list>
  <victimProcess id="process12994344c58" />
 </victim-list>
 <process-list>
  <process id="process12994344c58" taskpriority="0" logused="272" waitresource="XACT: 23:2476:0 KEY: 23:72057594049593344 (8194443284a0)" waittime="447" ownerId="3234906" transactionname="xactA" lasttranstarted="2025-10-08T21:36:34.063" XDES="0x12984ba0480" lockMode="S" schedulerid="2" kpid="204928" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:40.857" lastbatchcompleted="2025-10-08T21:36:34.063" lastattention="2025-10-08T21:36:11.340" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234906" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 20
WHERE a = 1;
   </inputbuf>
  </process>
  <process id="process1299c969828" taskpriority="0" logused="272" waitresource="XACT: 23:2477:0 KEY: 23:72057594049593344 (61a06abd401c)" waittime="3083" ownerId="3234886" transactionname="xactB" lasttranstarted="2025-10-08T21:36:30.303" XDES="0x12995c84480" lockMode="S" schedulerid="2" kpid="63348" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:38.223" lastbatchcompleted="2025-10-08T21:36:30.303" lastattention="1900-01-01T00:00:00.303" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234886" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 100
WHERE a = 2;
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <xactlock xdesIdLow="2476" xdesIdHigh="0" dbid="23" id="lock1299fa06c00" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process1299c969828" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process12994344c58" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
  <xactlock xdesIdLow="2477" xdesIdHigh="0" dbid="23" id="lock129940b2380" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process12994344c58" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1299c969828" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
 </resource-list>
</deadlock>

Verwante inhoud

• Overzicht van uitgebreide gebeurtenissen
• sys.dm_tran_locks (Transact-SQL)
• Deadlock Graph-gebeurtenisklasse
• Impasses met leesbare isolatieniveau
• Impasseketen gebeurtenisklasse
• Vergrendeling: Deadlock-gebeurtenisklasse
• SET-DEADLOCK_PRIORITY (Transact-SQL)
• Impasses in Azure SQL Database en SQL Database in Fabric analyseren en voorkomen
• Een impassebestand openen, weergeven en afdrukken in SQL Server Management Studio (SSMS)