SQL Server Deadlock: Nasıl Tespit Edilir, Analiz Edilir ve Önlenir?

Deadlock, iki session’ın her birinin diğerinin ihtiyaç duyduğu bir lock’u tutması ve bu yüzden ikisinin de asla ilerleyememesidir. SQL Server bu döngüyü, “deadlock victim” (kurban) olarak seçtiği session’ı iptal ederek kırar; o session 1205 hatasını alır. SSMS’te ya da uygulama loglarınızda şöyle görünür:

Yoğun bir veritabanında ara sıra görülen deadlock’lar normaldir. Sık görülenler ise uygulamada ya da veritabanında bir tasarım sorununa işaret eder; deneyimimize göre transaction’larını iyi yönetemeyen uygulamaların en yaygın yan etkilerinden biridir.

Bu rehberde deadlock’un ne olduğunu ve blocking’den farkını, SQL Server’ın onu nasıl tespit edip çözdüğünü, deadlock graph’larının nasıl yakalanıp okunacağını, deadlock’ların çoğunun arkasındaki kalıpları ve gerçekten işe yarayan çözümleri anlatıyoruz.

Deadlock ile Blocking Arasındaki Fark

İkisi sıkça karıştırılır, ama çok farklı davranırlar:

Uzun blocking zincirleri ayrı bir sorundur; teşhis için ilk adımları SQL Server kesinti runbook’umuzda anlattık.

SQL Server Deadlock’u Nasıl Tespit Eder ve Çözer?

Arka planda çalışan lock monitor, varsayılan olarak 5 saniyede bir deadlock döngülerini arar. Deadlock bulmaya başladığında daha sık, 100 milisaniyeye kadar kontrol eder; deadlock’lar bitince aralığı yeniden uzatır. Bir döngü bulduğunda SQL Server:

1. Bir deadlock kurbanı seçer: varsayılan olarak geri alınması en ucuz olan transaction.
2. Kurbanın mevcut batch’ini sonlandırır, transaction’ını geri alır ve lock’larını serbest bırakır.
3. Kurbanın uygulamasına 1205 hatasını döndürür. Diğer transaction devam eder.

Kurban seçimini SET DEADLOCK_PRIORITY ile (LOW, NORMAL, HIGH ya da -10 ile 10 arasında bir sayı) etkileyebilirsiniz. Session’ların öncelikleri farklıysa önceliği düşük olan kurban olur. Arka plan job’larına ve raporlara LOW vermek kullanıcı transaction’larını korur.

Deadlock’ları Yakalamak: system_health Zaten Kaydediyor

İncelemeye başlamak için hiçbir şey kurmanız gerekmez. Varsayılan olarak açık olan system_health Extended Events oturumu, her deadlock’u tam deadlock graph’ıyla birlikte bir xml_deadlock_report olayı olarak kaydeder.

SSMS’te: Management → Extended Events → Sessions → system_health → event_file‘ı açın ve xml_deadlock_report‘a göre filtreleyin. Bir olaya tıklamak grafik bir deadlock graph gösterir.

T-SQL ile bu sorgu, oturumun event dosyalarına kaydedilmiş deadlock’ları listeler:

SELECT x.ed.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time_utc,
       x.ed.query('(event/data/value/deadlock)[1]') AS deadlock_graph
FROM (SELECT CAST(event_data AS xml) AS ed
      FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
      WHERE object_name = N'xml_deadlock_report') AS x
ORDER BY deadlock_time_utc DESC;

Grafiği görmek için bir deadlock_graph değerini .xdl dosyası olarak kaydedip SSMS’te açın. system_health dosyaları döngüsel olarak yenilenir; yoğun bir sunucuda eski deadlock’lar kaybolur. Tekrarlayan bir sorunun peşindeyseniz, kendi dosya hedefi ve saklama süresi olan, xml_deadlock_report için ayrı bir Extended Events oturumu oluşturun.

Deadlock’ların zaman içinde ne sıklıkla yaşandığını görmek için izleme sisteminizde Number of Deadlocks/sec performans sayacını (SQLServer:Locks) takip edin.

Bir Deadlock Graph Nasıl Okunur?

Her deadlock graph aynı soruları cevaplar:

• Kimler dahildi? Process listesi her session’ı, login’ini, host’unu, uygulama adını, isolation level’ını ve çalıştırdığı komutu (inputbuf) gösterir. Biri kurban olarak işaretlenmiştir.
• Ne için çatıştılar? Resource listesi kilitlenen nesneleri gösterir: belirli bir indeks üzerindeki key lock’lar, page ya da row lock’lar, object lock’lar.
• Hangi sırayla? Her resource için hangi process’in hangi lock modunu (S, U, X) tuttuğu ve hangisinin onu beklediği. Owner ve waiter oklarını takip etmek döngüyü ortaya çıkarır.

İki ayrıntı özellikle işe yarar: key lock’lardaki indeks adı, çoğu zaman doğrudan eksik ya da yanlış seçilmiş bir indekse işaret eder; isolation level ise beklenmedik bir SERIALIZABLE ya da REPEATABLE READ (çoğu zaman bir framework’ün ya da sürücünün varsayılanı) sık görülen bir sebep olduğu için önemlidir.

Deadlock’ların Çoğunun Arkasındaki Kalıplar

1. Aynı tablolara ters sırayla erişmek

Klasik durum: bir transaction önce A tablosunu sonra B tablosunu günceller, diğeri önce B’yi sonra A’yı. İşte küçük bir örnek:

CREATE TABLE dbo.ProductSold      (ProductID int PRIMARY KEY, QuantitySold int);
CREATE TABLE dbo.UpdatedInventory (ProductID int PRIMARY KEY, QuantityInStock int);
INSERT dbo.ProductSold      VALUES (1, 10), (2, 20);
INSERT dbo.UpdatedInventory VALUES (1, 100), (2, 200);

-- Session 1                                    -- Session 2
BEGIN TRAN;                                     BEGIN TRAN;
UPDATE dbo.ProductSold                          UPDATE dbo.UpdatedInventory
   SET QuantitySold += 1                           SET QuantityInStock -= 10
 WHERE ProductID = 1;   -- A üzerinde X lock       WHERE ProductID = 1;   -- B üzerinde X lock
UPDATE dbo.UpdatedInventory                     UPDATE dbo.ProductSold
   SET QuantityInStock -= 1                        SET QuantitySold += 10
 WHERE ProductID = 1;   -- B'yi bekler            WHERE ProductID = 1;   -- A'yı bekler: deadlock

Çözüm: bu tablolara dokunan her kod yolu onlara aynı sırayla erişmelidir. Mantığı tek bir stored procedure’e koymak sıranın yanlış kurulmasını imkânsız hale getirir:

CREATE OR ALTER PROCEDURE dbo.RecordSale @ProductID int, @Quantity int
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRAN;
        UPDATE dbo.ProductSold      SET QuantitySold    += @Quantity WHERE ProductID = @ProductID;  -- her zaman önce
        UPDATE dbo.UpdatedInventory SET QuantityInStock -= @Quantity WHERE ProductID = @ProductID;  -- her zaman sonra
    COMMIT;
END;

2. Okuyanlarla yazanların birbirini bloklaması

Varsayılan, lock tabanlı READ COMMITTED isolation level’ında okuyanlar, yazanların exclusive lock’larıyla çakışan shared lock’lar alır. İki tabloyu okuyan bir rapor, aynı tabloları farklı sırayla güncelleyen bir transaction’la deadlock’a girebilir. Row versioning bu sınıfın tamamını ortadan kaldırır (aşağıya bakın).

3. İndeks ile tablo arasındaki lookup deadlock’ları

Nonclustered bir indekste seek yapıp kalan sütunları clustered indekste arayan (lookup) bir sorgu, önce indeksi sonra tabloyu kilitler. O indeksteki bir sütunu değiştiren bir update ise önce clustered indeksi, sonra nonclustered indeksi değiştirir. İkisi aynı satırı ters sırayla kilitler. Sorgunun ihtiyaç duyduğu sütunları içeren bir covering index lookup’ı ve genellikle deadlock’u ortadan kaldırır.

4. Uzun transaction’lar ve uygulama tasarımı

Bir transaction lock’ları ne kadar uzun tutarsa başka bir transaction’la çakışma olasılığı o kadar artar. Sahada kök neden çoğu zaman uygulamadır:

• Kullanıcı girdisini ya da harici bir servis çağrısını beklerken açık kalan transaction’lar.
• Çok sayıda komutun ya da bütün bir toplu aktarımın tek bir dev transaction içine alınması.
• İçinde hâlâ açık bir transaction varken havuza geri bırakılan connection’lar; session uyurken lock’ları tutmaya devam eder.
• Isolation level’ı sessizce SERIALIZABLE’a yükselten ORM ya da sürücü varsayılanları.

5. Paralellik

Nadiren tek bir paralel sorgu kendisiyle deadlock’a girer ya da paralel thread’ler başka bir session’la bir döngünün parçası olur. Graph’ta aynı session ID’si farklı execution context’lerle (ecid) birden fazla kez görünür. Sorguyu tune etmek, uygun bir indeks eklemek ya da o sorgu için MAXDOP‘u sınırlamak genellikle çözer.

Deadlock’lar Nasıl Önlenir?

1. Nesnelere her transaction’da ve her kod yolunda aynı sırayla erişin.
2. Transaction’ları kısa ve tek batch içinde tutun. Transaction içinde kullanıcı etkileşimi, harici çağrı ve gereksiz iş olmasın.
3. İlgili sorgular için indeksleyin. Daha iyi indeksler daha az kilitlenen satır, daha kısa lock süreleri ve daha az lookup demektir. Deadlock graph’taki indeks adları nereden başlayacağınızı gösterir.
4. Row versioning kullanın. READ_COMMITTED_SNAPSHOT açıkken okuyanlar artık shared lock almaz; satırın son commit edilmiş versiyonunu okurlar. Microsoft, bir uygulama lock tabanlı READ COMMITTED’ın bloklama davranışına dayanmıyorsa bütün uygulamalar için row versioning tabanlı READ COMMITTED’ı önerir. tempdb’de (ya da Accelerated Database Recovery ile veritabanının kendisinde) ek iş oluşturur; production’da açmadan önce test edin.
5. Doğru olan en düşük isolation level’ı kullanın. SERIALIZABLE ya da REPEATABLE READ’in kullanıldığı her yerde gerçekten gerekli olup olmadığını kontrol edin.
6. Arka plan job’ları ve raporlar için DEADLOCK_PRIORITY LOW ayarlayın; böylece kurban olarak kullanıcı transaction’ları seçilmez.
7. 1205 hatasını uygulamada yakalayın ve transaction’ı yeniden deneyin.

-- Deadlock kurbanları (1205 hatası) için yeniden deneme kalıbı
DECLARE @retry int = 3;
WHILE @retry > 0
BEGIN
    BEGIN TRY
        EXEC dbo.RecordSale @ProductID = 1, @Quantity = 1;
        SET @retry = 0;                       -- başarılı
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205 AND @retry > 1
        BEGIN
            SET @retry -= 1;
            WAITFOR DELAY '00:00:00.200';     -- yeniden denemeden önce kısa bir bekleme
        END
        ELSE
            THROW;                            -- diğer hatalar ya da denemeler tükendi
    END CATCH
END;

NOLOCK Deadlock Çözümü Değildir

Sık Sorulan Sorular