SQL Kilitlenmeleri (Blocking ve Deadlock) Nasıl Önlenir?

Ay sonu kapanışında ekranların donması, fatura kaydederken uygulamanın yanıt vermemesi, “kayıt başka kullanıcı tarafından kilitlendi” hatası… Bu tabloların büyük bölümünün altında kilit (lock) problemleri yatar.

Önce iki kavramı ayıralım, çünkü çözümleri farklıdır.

Blocking ile deadlock aynı şey değildir

Blocking (bekletme): Bir işlem, başka bir işlemin bıraktığı kilidi bekler. Bu normaldir; SQL Server’ın veri tutarlılığını sağlama biçimidir. Sorun, beklemenin uzaması ve zincirleme büyümesidir. İlk işlem bittiğinde bekleyen işlem devam eder.

Deadlock (ölümcül kilitlenme): İki işlem karşılıklı olarak birbirinin kilidini bekler ve hiçbiri ilerleyemez. SQL Server bu durumu tespit edip taraflardan birini “kurban” seçerek iptal eder (Hata 1205). Yani deadlock kendiliğinden çözülür ama bir işlem başarısız olur.

Kullanıcı şikâyetlerinin çoğu aslında blocking’dir; deadlock daha nadir fakat daha görünürdür.

Anlık durumu görmek

Şu an kimin kimi beklettiğini görmek için:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sorgu
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Deadlock’lar için ise Extended Events içindeki system_health oturumu, varsayılan olarak son deadlock grafiklerini saklar. Yani sorun yaşandıktan sonra bile geriye dönük inceleme yapılabilir.

Kilitlenmeleri önlemenin yolları

1. İşlemleri (transaction) kısa tutun

En sık görülen hata, transaction açıkken kullanıcıdan girdi beklemek veya uzun hesaplamalar yapmaktır. Transaction, veritabanına yazma anını kapsamalı; iş mantığı ve kullanıcı etkileşimi dışarıda kalmalıdır.

2. Nesnelere hep aynı sırayla erişin

Deadlock’ların klasik sebebi, iki farklı kod parçasının aynı iki tabloya ters sırayla dokunmasıdır. Uygulama genelinde tutarlı bir erişim sırası belirlemek, deadlock’ların önemli bölümünü ortadan kaldırır.

3. Doğru indeksleri oluşturun

Bu, en çok göz ardı edilen ama en etkili maddedir. Uygun indeks yoksa SQL Server tablonun tamamını tarar ve gereğinden çok satırı kilitler. İndeks eklemek, kilitlenen alanı daraltarak blocking’i doğrudan azaltır.

4. READ COMMITTED SNAPSHOT’ı değerlendirin

ALTER DATABASE [VeritabaniAdi] SET READ_COMMITTED_SNAPSHOT ON;

Bu ayarla okuma işlemleri, yazan işlemleri beklemek yerine verinin satır sürümünü okur. Okuyucu–yazıcı çakışmaları büyük ölçüde ortadan kalkar. Bedeli, tempdb üzerindeki ek yüktür; ayrıca uygulamanın davranışı değişebileceği için mutlaka test ortamında denenmelidir. Hazır bir ERP kullanıyorsanız üreticinin desteklediğinden emin olun.

5. NOLOCK’u çözüm sanmayın

WITH (NOLOCK) kilit beklemez, ancak henüz onaylanmamış (dirty) veriyi okuyabilir, aynı satırı iki kez veya hiç okumayabilir. Mali raporda yanlış rakam üretme riski taşır. Kilit sorununu çözmez, sadece görünmez kılar. Tutarlılığın önemli olduğu her yerde kaçınılmalıdır.

6. Raporlamayı işlem yükünden ayırın

Ağır raporlar, canlı veritabanında uzun süreli paylaşımlı kilitler oluşturur. Read-only replika veya ayrı bir raporlama/BI veritabanı, kilit sorunlarının önemli bir kaynağını ortadan kaldırır.

7. Toplu işlemleri parçalayın

Milyonlarca satırlık tek bir UPDATE, kilit yükseltmesine (lock escalation) yol açarak tüm tabloyu kilitleyebilir. Aynı işi paketler halinde yapmak, hem kilit süresini hem de log büyümesini kontrol altında tutar.

8. Uygulamada yeniden deneme (retry) mantığı kurun

Deadlock’ları sıfıra indirmek çoğu sistemde gerçekçi değildir. 1205 hatası alındığında işlemin kısa bir bekleme sonrası otomatik olarak yeniden denenmesi, kullanıcıya hata göstermeden sorunu çözer.

İzleme olmadan yönetilemez

Kilitlenmeler genellikle belirli saatlerde ve belirli işlemlerde yoğunlaşır. Uzun süreli blocking için uyarı kurmak, deadlock grafiklerini düzenli incelemek ve en sık tekrar eden desenleri kayıt altına almak, sorunu şikâyete dönüşmeden yakalamanızı sağlar.

ÇAP Teknoloji olarak SQL Server ortamlarında kilitlenme analizi yapıyor, deadlock grafiklerini çözümleyip uygulama ve indeks tarafında kalıcı düzeltmeler öneriyoruz. Kapanış dönemlerinde yaşadığınız donmalar için bize danışabilirsiniz.

Veritabanınızdaki kilitlenme ve performans sorunlarını kalıcı olarak çözmek için SQL danışmanlığı hizmetimiz hakkında bilgi alabilir veya ücretsiz teklif talep edebilirsiniz.