Veritabanı Performansı & Eşzamanlılık Yönetimi 37 okunma

Database Deadlocks: Neden, Tespit ve Çözüm

Veritabanı kilitlenmelerinin (deadlock) oluşma nedenleri, SQL Server'da deadlock graph'ları ile tespit ve tekrar deneme veya kilit sırası düzenleme gibi çözümler ele alınır.

Database Deadlocks: Neden, Tespit ve Çözüm

Database Deadlocks: Neden Oluşur, Nasıl Tespit Edilir ve Nasıl Engellenir?

Bir veritabanı deadlock'u (kilitlenme), iki veya daha fazla işlemin (transaction) birbirlerinin kilitlediği kaynaklara erişmek için sonsuza kadar beklediği bir durumdur. Bu durum, uygulamanızın donmasına, kullanıcı deneyiminin bozulmasına ve sistem kaynaklarının israfına yol açar. Neyse ki SQL Server, deadlock'ları otomatik olarak tespit eder ve kurban (victim) olarak seçtiği bir işlemi geri alarak (rollback) bu kısır döngüyü kırar. Bu yazıda, deadlock'ların neden oluştuğunu, SQL Server'da nasıl tespit edileceğini ve uygulama/veritabanı seviyesinde nasıl önleneceğini veya yönetileceğini derinlemesine inceleyeceğiz.

1. Deadlock Nedir ve Nasıl Oluşur?

Bir deadlock'un oluşması için dört temel koşulun (Coffman koşulları) bir araya gelmesi gerekir:

  1. Karşılıklı Kilit (Mutual Exclusion): Kaynaklar (veri satırları, sayfalar, tablolar) aynı anda yalnızca bir işlem tarafından kullanılabilir.

  2. Kaynağı Bekle (Hold and Wait): Bir işlem, zaten bir kaynağı kilitlerken, başka bir kaynağı bekler.

  3. Kaynak Önceliği Yok (No Preemption): Bir işlem, başka bir işlemin elindeki kaynağı zorla alamaz.

  4. Döngüsel Bekleme (Circular Wait): İşlemler, bir döngü oluşturacak şekilde birbirlerinin kaynaklarını bekler.

Klasik Deadlock Senaryosu:

  • İşlem A: Table1'deki Row 1'i günceller (kilitler). Ardından Table2'deki Row 2'yi güncellemek ister.

  • İşlem B: Table2'deki Row 2'yi günceller (kilitler). Ardından Table1'deki Row 1'i güncellemek ister.

Sonuç: İşlem A, Row 2'yi beklerken Row 1'i tutar. İşlem B, Row 1'i beklerken Row 2'yi tutar. İkisi de sonsuza kadar bekler. Bu, özellikle uygulama kodunda tutarsız bir kilit sıralaması (lock ordering) olduğunda sıkça görülür.

2. Deadlock Nasıl Tespit Edilir? (SQL Server)

Deadlock'ları tespit etmek, çözümün ilk ve en önemli adımıdır. SQL Server, deadlock olaylarını yakalamak için çeşitli araçlar sunar.

A. Deadlock Graph (Ölü Kilit Grafiği)

SQL Server Management Studio (SSMS) ve SQL Server Profiler, deadlock'ları görselleştirmek için bir "deadlock wait-for graph" (bekleme grafiği) kullanır. Bu grafik, deadlock'a dahil olan süreçleri, kaynakları ve aralarındaki ilişkileri gösterir.

  • Süreç Düğümleri (Process Nodes): Deadlock'a dahil olan işlemleri (sorguları) temsil eder. Genellikle bir 'X' işareti, deadlock kurbanı (victim) olan işlemi belirtir.

  • Kaynak Düğümleri (Resource Nodes): Kilitlenen veritabanı nesnelerini (satır, sayfa, tablo) temsil eder.

  • Kenarlar (Edges): Bir sürecin hangi kaynağı beklediğini veya hangi kaynağa sahip olduğunu gösteren oklardır.

Deadlock Graph'ı Yakalama Yöntemleri:

  1. SQL Server Profiler: Bir izleme (trace) oluşturup "Deadlock graph" olay sınıfını ekleyerek deadlock'ları yakalayabilirsiniz. Bu olay sınıfı, deadlock ile ilgili XML verilerini TextData sütununa yazar.

  2. Extended Events (Genişletilmiş Olaylar): SQL Server 2012 ve sonraki sürümlerde, sqlserver.xml_deadlock_report genişletilmiş olayı, deadlock graph verilerini yakalamak için önerilen yöntemdir.

  3. Hata Günlüğü (Error Log): Deadlock gerçekleştiğinde, SQL Server hata günlüğüne de deadlock ile ilgili bilgiler yazabilir (trace flag 1222 veya 1204 ile detaylandırılabilir).

  4. Query Store: Azure SQL Database ve SQL Server 2016+ ile birlikte gelen Query Store, deadlock'ları izlemek ve analiz etmek için kullanılabilir.

B. Deadlock Graph'ı Okuma

Bir deadlock graph'ında (XML veya görsel) aranması gereken kritik bilgiler:

  • Victim (Kurban) İşlem: Hangi işlemin geri alındığı (rollback). Bu işlem genellikle en az maliyetli olanıdır.

  • Sorgu Metinleri: Deadlock'a dahil olan işlemlerin çalıştırdığı SQL sorguları veya stored procedure'ler.

  • Kilitlenen Kaynaklar: Hangi tablolar, sayfalar veya satırların kilitlendiği.

  • Kilit Türleri: Paylaşımlı kilit (Shared Lock - S), Güncelleme kilidi (Update Lock - U), Özel kilit (Exclusive Lock - X) vb.

3. Deadlock Nasıl Engellenir veya Çözülür?

Deadlock'ları tamamen ortadan kaldırmak zor olsa da, sıklıklarını azaltmak ve etkilerini en aza indirmek mümkündür.

A. Veritabanı Seviyesinde Önlemler

  1. Kilit Sırasını Standartlaştırın (Lock Ordering):
    Bu, deadlock'ları önlemenin en etkili yöntemidir. Tüm işlemlerin kaynaklara aynı sırayla erişmesini sağlayın. Örneğin, her zaman önce Table1'i, sonra Table2'yi güncelleyin. Eğer bu mümkün değilse, SQL Server'da SET DEADLOCK_PRIORITY ile bir işlemi kurban olmaya daha yatkın hale getirebilirsiniz.

  2. Transaction Süresini Kısaltın:
    İşlemler ne kadar kısa sürerse, deadlock olasılığı o kadar azalır. Gereksiz yere uzun transaction'lar kullanmaktan kaçının.

  3. İndeksleri Optimize Edin (Indexing):
    Yetersiz indeksler, tablo taramalarına (table scan) neden olur ve bu da daha fazla kilit alanına yol açar. Sorgularınızı hızlandırmak için uygun indeksler oluşturun.

  4. İzolasyon Seviyesini Düşürün (Isolation Level):
    READ COMMITTED SNAPSHOT veya SNAPSHOT ISOLATION gibi izolasyon seviyeleri, okuma işlemlerinin kilit almasını engelleyerek deadlock riskini azaltır. Ancak bu seviyelerin tempdb üzerinde ek yük oluşturabileceğini unutmayın.

  5. Gereksiz Kilitlerden Kaçının:
    NOLOCK ipucu (READ UNCOMMITTED) veya WITH (NOLOCK) kullanarak okuma işlemlerinin kilit almasını engelleyebilirsiniz. Ancak bu, kirli veri (dirty read) okuma riskini beraberinde getirir.

  6. Sorguları Optimize Edin (Query Optimization):
    Karmaşık ve yavaş sorgular, daha uzun süre kilit tutar. Sorgu planlarını inceleyerek (Execution Plan) darboğazları tespit edin ve sorguları optimize edin.

B. Uygulama Seviyesinde Çözümler: Retry Logic (Yeniden Deneme Mantığı)

Deadlock'lar tamamen engellenemez. Bu nedenle, uygulamanızda deadlock durumlarını yakalayıp işlemi yeniden deneyecek bir mekanizma (retry logic) bulunmalıdır.

SQL Server Deadlock Hata Kodu: 1205

Bir deadlock kurbanı olan işlem, SQL Server tarafından geri alındığında, uygulamaya bir SqlException fırlatılır. Bu exception'ın Number özelliği 1205'tir.

.NET (C#) ile Retry Logic Örneği:

csharp

public async Task ExecuteWithRetryAsync(Func<Task> action, int maxRetryCount = 3)
{
    int retryCount = 0;
    while (retryCount < maxRetryCount)
    {
        try
        {
            await action();
            return; // Başarılı, döngüden çık.
        }
        catch (SqlException ex) when (ex.Number == 1205) // Deadlock hatası[reference:17]
        {
            retryCount++;
            if (retryCount >= maxRetryCount)
                throw; // Son denemede de başarısız oldu.

            // Üstel geri çekilme (Exponential Backoff) ile bekle
            int waitTime = (int)Math.Pow(2, retryCount) * 100; // 200ms, 400ms, 800ms...
            await Task.Delay(waitTime);
        }
        // Diğer hataları yakalama ve yeniden fırlatma...
    }
}

// Kullanım:
await ExecuteWithRetryAsync(async () =>
{
    using var transaction = await context.Database.BeginTransactionAsync();
    // Veritabanı işlemlerini yap...
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
});

Önemli Noktalar:

  • Idempotency (Yinelenebilirlik): İşleminizin birden fazla kez çalıştırıldığında zararsız olması (idempotent) çok önemlidir. Aynı veriyi iki kez eklememek için kontrol mekanizmaları ekleyin.

  • Sonsuz Döngüden Kaçının: Maksimum yeniden deneme sayısı belirleyin ve bu sayıya ulaşıldığında hatayı fırlatın.

  • Bekleme Süresi: Yeniden denemeler arasında üstel geri çekilme (exponential backoff) kullanarak veritabanı üzerindeki baskıyı azaltın.

4. Sık Yapılan Hatalar ve İpuçları

Hata / Tuzak Çözüm / Öneri
Deadlock'ları sadece veritabanı sorunu olarak görmek Çoğu deadlock, uygulama kodundaki tutarsız kilit sıralamasından kaynaklanır.
Retry logic'i uygulamamak Deadlock'lar kaçınılmazdır. Uygulamanızda mutlaka bir retry mekanizması olmalıdır.
Çok uzun transaction'lar kullanmak Transaction'ları olabildiğince kısa tutun. Gereksiz yere transaction içinde işlem yapmayın.
İndeksleri ihmal etmek Yetersiz indeksler, tablo taramalarına ve dolayısıyla daha fazla kilitlenmeye yol açar.
İzolasyon seviyesini göz ardı etmek READ COMMITTED SNAPSHOT gibi seçenekler deadlock riskini önemli ölçüde azaltabilir.

Sonuç:

Veritabanı deadlock'ları, eşzamanlılık (concurrency) yönetiminin doğal bir sonucudur. Bunları tamamen ortadan kaldırmak mümkün olmasa da, kilit sıralamasını standartlaştırarak, transaction sürelerini kısaltarak, indeksleri optimize ederek ve uygulama seviyesinde sağlam bir retry logic uygulayarak etkilerini en aza indirebilirsiniz.

Deadlock'ları tespit etmek için Deadlock Graph'ları (XML veya görsel) kullanmak, sorunun kaynağını anlamanın en etkili yoludur. Unutmayın, bir deadlock oluştuğunda panik yapmayın; SQL Server zaten bir kurban seçip işlemi geri alacaktır. Önemli olan, uygulamanızın bu durumu düzgün bir şekilde yakalayıp işlemi yeniden deneyebilmesidir. Deadlock'lar birer "hata" değil, yönetilmesi gereken bir "durum"dur.

Tüm yazılar