SQL Server deadlock: van foutmelding naar oplossing

Je applicatie werkt prima, totdat een gebruiker ineens een foutmelding krijgt. In de logging verschijnt een melding van SQL Server: ‘Transaction (Process ID 123) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.’

Je wordt geconfronteerd met een deadlock, wellicht ben hier al eens tegenaan gelopen. Vaak gebeurt dat onverwacht en is het probleem lastig te reproduceren. Maar het kan ervoor zorgen dat bewerkingen in je applicatie mislukken en gebruikers hier last van hebben.

Gelukkig biedt SQL Server verschillende mogelijkheden om te achterhalen wat er precies is gebeurd. Maar voordat je een deadlock kunt oplossen, leg ik je uit hoe deze ontstaat en welke transacties erbij betrokken zijn.

In dit artikel laat ik zien hoe deadlocks ontstaan, hoe je ze met de beschikbare hulpmiddelen van SQL Server analyseert en welke maatregelen je kunt nemen om ze te voorkomen.

Tekst: Sander van der Bom

Een deadlock ontstaat wanneer twee of meer transacties op elkaar wachten en geen van de transacties verder kan. Stel dat jouw applicatie twee tabellen gebruikt: Klanten en Bestellingen. Twee gebruikers voeren vrijwel tegelijkertijd een wijziging uit. Transactie A begint met het aanpassen van een klant en gaat daarna een bestelling wijzigen. Transactie B doet precies het tegenovergestelde: deze transactie wijzigt eerst een bestelling en daarna een klant.

Dat ziet er schematisch als volgt uit:

Transactie A:

1. Vergrendelt een record in de tabel Klanten.
2. Wil vervolgens een record in Bestellingen aanpassen.
3. Moet wachten, omdat transactie B dat record heeft vergrendeld.

Transactie B:

1. Vergrendelt een record in de tabel Bestellingen.
2. Wil vervolgens een record in Klanten aanpassen.
3. Moet wachten, omdat transactie A dat record heeft vergrendeld.

Beide transacties wachten nu op elkaar. Transactie A kan niet verder zonder dat B zijn vergrendeling vrijgeeft. Transactie B kan niet verder zonder dat A hetzelfde doet. Dit is dus een deadlock: een situatie waarin transacties elkaar wederzijds blokkeren.

SQL Server kan deze situatie herkennen, grijpt automatisch in en kiest één van de transacties als slachtoffer (deadlock victim). Deze transactie wordt afgebroken en de bijbehorende wijzigingen worden ongedaan gemaakt. Daardoor kan de andere transactie wel worden afgerond.

Wanneer een transactie wacht op een andere transactie die uiteindelijk verder kan gaan, dan spreken we niet over een deadlock, maar over gewone blokkering (blocking). Dit levert ook geen rechtstreekse foutmeldling op, maar de transactie kan langer duren en daardoor indirect wel resulteren in bijvoorbeeld een command timeout. Bij een deadlock kunnen de betrokken transacties niet zelfstandig verder.

Je weet nu wat een deadlock is, maar op welke manier kun je de daadwerkelijke oorzaak achterhalen?

De eerste stap is het verzamelen van informatie: je kunt niet zoveel wanneer je slechts de foutmelding uit je applicatie weet. Je wilt weten welke transacties betrokken waren, welke locks ze vasthielden en welke locks in de wachtstand stonden. Gelukkig heeft SQL Server hiervoor een handig hulpmiddel: Extended Events.

SQL Server registreert standaard deadlocks via het Extended Event xml_deadlock_report. Deze gebeurtenis wordt vastgelegd in de systeemmonitoring die bekendstaat als system_health.

Je hoeft hiervoor dus niet vooraf een uitgebreide monitoring op te zetten. Je kunt de bestaande gegevens opzoeken met behulp van SQL Server Management Studio (SSMS). Open in SSMS de sessie system_health onder Management > Extended Events > Sessions. Vervolgens bekijk je de gebeurtenissen en ga je op zoek naar xml_deadlock_report.

De deadlock graph is hierbij bijzonder waardevol. Dit XML-document bevat informatie over de betrokken processen, de gebruikte locks en de resources waarop de transacties wachten.In SSMS kun je dit XML-document openen in een grafische weergave. In die weergave zie je in één oogopslag welke transacties bij de deadlock betrokken waren.

 De grafiek bevat onder andere:

• Processen: de transacties die bij de deadlock betrokken zijn.
• Resources: de tabellen, indexen of records waarop locks zijn geplaatst.
• Wait-for-relaties: welke transactie op welke andere transactie wacht.
• Victim: de transactie die SQL Server heeft afgebroken.

Bekijk vooral welke statements de locks veroorzaken. Vaak zie je hierdoor al dat twee transacties dezelfde tabellen in een verschillende volgorde benaderen.

Let op: de system_health-sessie heeft een beperkte hoeveelheid schijfruimte beschikbaar voor het bewaren van historische informatie. Als een deadlock zich maar af en toe voordoet, kan het verstandig zijn om de gebeurtenissen ook op een andere manier vast te leggen. Je kunt de deadlock events bijvoorbeeld naar een eigen event file wegschrijven, zodat je zelf bepaalt hoeveel en hoe lang je historische informatie bewaart.

Als je de deadlock graph hebt gevonden, kun je gericht naar een oplossing zoeken. Er zijn verschillende mogelijkheden. Welke aanpak het beste werkt, hangt af van de oorzaak. Ik neem je mee in vier verschillende oplossingsrichtingen:

1. Gebruik een vaste volgorde voor bewerkingen

Dit is een van de eenvoudigste manieren om deadlocks te voorkomen. Als meerdere transacties dezelfde tabellen aanpassen, zorg er dan voor dat ze dat in dezelfde volgorde doen.

In ons eerdere voorbeeld passen beide transacties eerst de klant aan en daarna de bestelling. Daarmee voorkom je dat de ene transactie op een klant wacht terwijl de andere op een bestelling wacht. Dit principe geldt niet alleen voor tabellen, maar ook voor records en andere resources die transacties gelijktijdig benaderen.

2. Houd transacties zo kort mogelijk

Een transactie moet bij voorkeur alleen de databasebewerkingen bevatten die daadwerkelijk bij elkaar horen. Voorkom dat je binnen een transactie bijvoorbeeld externe API-aanroepen uitvoert, bestanden verwerkt of op invoer van de gebruiker wacht. Deze activiteiten zorgen voor een langere transactieduur en daardoor blijven locks langer actief. Open een transactie zo laat mogelijk en commit deze zodra de noodzakelijke bewerkingen zijn afgerond.

3. Controleer je indexen en execution plans

Een ontbrekende of ongeschikte index kan ervoor zorgen dat SQL Server veel meer records moet onderzoeken dan noodzakelijk. Hierdoor kan SQL Server ook meer gegevens vergrendelen dan eigenlijk nodig is. Dit noemen we het lockbereik: de hoeveelheid gegevens waarop SQL Server een lock plaatst tijdens het uitvoeren van een query. Afhankelijk van de situatie kan dat een afzonderlijk record zijn, een groter aantal records of zelfs een volledige tabel.

Een groter lockbereik vergroot de kans dat andere transacties moeten wachten of dat er een deadlock ontstaat. Controleer daarom het execution plan van de betrokken queries. Bekijk of SQL Server de juiste indexen gebruikt en of een Index Seek mogelijk is in plaats van een onnodig grote scan.

Een aanvullende index kan in sommige gevallen een groot verschil maken. Houd er wel rekening mee dat extra indexen ook onderhoud vragen en zelf weer locks kunnen veroorzaken bij wijzigingen.

4. Implementeer retry-logica

Zelfs wanneer je je queries en transacties optimaliseert, kun je niet iedere deadlock uitsluiten. Daarom is het verstandig om in je applicatie rekening te houden met foutnummer 1205. Dit is de SQL Server-foutcode die bij een deadlock wordt teruggegeven.

Je kunt de betreffende transactie opnieuw uitvoeren. Voer dit bij voorkeur slechts een beperkt aantal keren uit en houd een korte wachttijd aan tussen de pogingen. Belangrijk is dat je de volledige transactie opnieuw uitvoert en niet alleen het statement waarop de fout ontstond. SQL Server heeft de volledige transactie namelijk teruggedraaid. Controleer daarnaast of dat de bewerkingen opnieuw uitgevoerd kunnen worden. Dit kan bijvoorbeeld dubbele externe acties of dubbele berichten voorkomen.

Deadlocks zijn niet altijd volledig te voorkomen. Bij intensief gebruik van een database in een heel kort tijdsbestek kan er altijd een situatie ontstaan waarin transacties elkaar in de weg zitten. Dat betekent niet dat je ze moet accepteren zonder actie te ondernemen.

Zorg voor een vaste volgorde bij databasebewerkingen, houd transacties kort en controleer of je queries gebruikmaken van geschikte indexen. Gebruik Extended Events om deadlocks te analyseren en implementeer retry-logica om incidentele deadlocks op een eenvoudige wijze af te handelen.

De belangrijkste les: behandel een deadlock niet alleen als een foutmelding die je moet opvangen. Ga op zoek naar de onderliggende oorzaak. Op deze manier verander je een lastig te reproduceren databaseprobleem in een concreet verbeterpunt voor je applicatie.