go backsqlschool blogs list

Deadlock Event Capture in SQL Server

by 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:

Ημερομηνία: 09 February 2016 21:33
Αξιολόγηση: ( 1 )
Tags:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS