Deadlock Event Capture in SQL Server
Fivi Panopoulou - Sotiris Karras
Σε αυτό το post θα δείξουμε τους τρόπους που μας δίνει ο SQL Server για να βρίσκουμε και να κάνουμε troubleshoot τα deadlocks όταν αυτά εμφανίζονται.
Αρχικά θα χρειαστεί να προκαλέσουμε ένα deadlock στον SQL Server. Για αυτό το σκοπό θα χρησιμοποιήσουμε την AdventureWorks και σε δύο διαφορετικά query windows θα προσπαθήσουμε τρέξουμε τα παρακάτω transactions ταυτόχρονα:
-- Transaction 1
BEGIN TRANSACTION
UPDATE Sales.SalesOrderDetail
SET OrderQty = 1
WHERE SalesOrderID IN (51739,51721,70,400);
UPDATE Sales.SalesOrderHeader
SET Comment = 'NOT AVAILABLE'
WHERE SalesOrderID IN (51739,51721,70,400);
-- Transaction 2
BEGIN TRANSACTION
UPDATE Sales.SalesOrderHeader
SET Comment = 'NOT AVAILABLE'
WHERE SalesOrderID IN (51739,51721,70,400);
UPDATE Sales.SalesOrderDetail
SET OrderQty = 1
WHERE SalesOrderID IN (51739,51721,70,400);
Τα transactions, θα προσπαθήσουν να πάρουν exclusive locks πάνω στους πίνακες SalesOrderHeader και SalesOrderDetail αλλά με αντίστροφη σειρά μεταξύ τους. Αυτό θα οδηγήσει το ένα να περιμένει το άλλο να αφήσει το lock του πάνω στο object που θέλει να γράψει οδηγώντας σε deadlock. O SQL Server, θα επιλέξει ένα από τα δύο και θα το σκοτώσει επιλύοντας έτσι το πρόβλημα. Το isolation level είναι το default του SQL Server (Read Committed).
Trace Flags 1204, 1222
Αν θέσουμε τα flags 1204,1222 σε ON και στην συνέχεια προκαλέσουμε το παραπάνω deadlock, θα μπορούμε να εκτελέσουμε την sp_readerrorlog του SQL Server για να διαβάσουμε τις πληροφορίες που μας δίνουν.
DBCC TRACEON (1204, -1);
DBCC TRACEON (1222, -1);
GO
SQL Server Profiler
Ένας πιο φιλικός προς τον χρήστη τρόπος να πάρουμε πληροφορίες για το deadlock, είναι η χρήση του SQL Server Profiler. Αν επιλέξουμε να κάνουμε capture στην κατηγορία Locks τα events:
• Deadlock Graph
• Lock: Deadlock Chain
• Lock: Deadlock
Θα πάρουμε ως αποτέλεσμα το παρακάτω διάγραμμα:

Σε αυτό μπορούμε να δούμε τα process id των δύο transaction, τα objects που συμμετέχουν στο deadlock, τα locks που έχει κάθε transaction, καθώς και αυτά τα οποία προσπαθεί να πάρει.
SQL Server Extended Events
Δεδομένου ότι ο SQL Server Profiler είναι deprecated feature του SQL Server, ο πιο up-to-date τρόπος να κάνουμε capture ένα deadlock είναι με την χρήση των Extended Events. Για να το κάνουμε αυτό, θα χρειαστεί να φτιάξουμε ένα καινούριο session το οποίο θα πρέπει να κάνει capture το xml_deadlock_report όπως φαίνεται παρακάτω:

To αποτέλεσμα θα είναι παρόμοιο με αυτό του profiler:
