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.

Şikâyet Anında İzlenecek Adımlar

Kullanıcılar ekranın donduğunu bildirdiğinde, aceleyle oturum sonlandırmak yerine aşağıdaki sırayı izlemeniz hem sorunu çözer hem de kök nedeni bulmanızı sağlar.

Durumun blocking mi yoksa deadlock mu olduğunu belirleyin. Bekleyen oturumlar varsa blocking, 1205 hatası varsa deadlock söz konusudur.
Zincirin başındaki oturumu bulun. Bu oturum kimseyi beklemez, ama diğerleri onu bekler.
Bu oturumun hangi uygulamadan, hangi kullanıcıyla ve hangi sorguyla geldiğini kaydedin. Bu bilgi sonradan kök neden analizinde gerekir.
Oturum boşta görünüyor ama açık bir transaction tutuyorsa, uygulamada kapatılmamış bir işlem olabilir.
Sonlandırma kararını en sona bırakın. KILL komutu işlemi geri alır ve büyük işlemlerde geri alma da zaman alabilir.

Deadlock Grafiğini Okurken Bakılacak Noktalar

Deadlock grafiği ilk bakışta karmaşık görünür, ancak üç soruya odaklanmanız yeterlidir:

Hangi işlemler çakıştı? Her işlemin çalıştırdığı sorgu veya prosedür grafikte yer alır.
Hangi kaynak üzerinde? Tablo, indeks ve kilit türü bilgisi, sorunun hangi nesnede yoğunlaştığını gösterir.
Kim kurban seçildi? İptal edilen işlem, kullanıcıya yansıyan hatanın kaynağıdır.

Aynı iki prosedür tekrar tekrar karşınıza çıkıyorsa, çözüm büyük olasılıkla erişim sırasında veya eksik bir indekstedir. Grafikleri tek tek değil, tekrar eden desenlere göre gruplayarak inceleyin.

Blocking tarafında benzer bir kayıt için blocked process threshold ayarını tanımlayabilirsiniz. Böylece belirlediğiniz süreyi aşan beklemeler rapor olarak kaydedilir ve sonradan incelenebilir.

Geliştirme Ekibi İçin Kontrol Listesi

Kilitlenmelerin büyük kısmı kod yazılırken önlenebilir. Kod incelemelerinde şu soruları sorabilirsiniz:

Transaction içinde dış servis çağrısı veya dosya işlemi var mı?
Hata durumunda transaction her koşulda kapatılıyor mu?
Güncelleme sorgularındaki filtre alanları indeksli mi?
Yeni kod, tablolara mevcut kodla aynı sırada mı erişiyor?

Bu incelemeyi canlı ortam verileriyle birlikte yapmak isterseniz SQL danışmanlığı kapsamında destek alabilirsiniz.

Sıkça Sorulan Sorular

Bekleten oturumu KILL ile sonlandırmak güvenli mi?

KILL komutu oturumu sonlandırır ve açık işlemi geri alır; veri tutarlılığı korunur. Ancak kullanıcının yaptığı iş kaybolur ve büyük işlemlerde geri alma uzun sürebilir. Bu süre boyunca kilitler devam eder. Sonlandırmadan önce oturumun ne çalıştırdığını kaydetmeniz, aynı sorunun tekrarını önlemenize yardımcı olur.

Kilit zaman aşımı (lock timeout) tanımlamak sorunu çözer mi?

SET LOCK_TIMEOUT, bir sorgunun kilidi en fazla ne kadar bekleyeceğini belirler. Süre dolduğunda sorgu hata alır. Bu ayar beklemenin nedenini ortadan kaldırmaz, yalnızca kullanıcının süresiz beklemesini engeller. Uygulamanın bu hatayı yakalayıp anlamlı bir mesaj göstermesi veya işlemi yeniden denemesi gerekir.

Deadlock’ta hangi işlemin iptal edileceğini belirleyebilir miyiz?

Evet. SQL Server varsayılan olarak geri alınması en az maliyetli olan işlemi kurban seçer. SET DEADLOCK_PRIORITY ile bir oturuma düşük veya yüksek öncelik verebilirsiniz. Örneğin arka planda çalışan bir rapor sorgusuna düşük öncelik tanımlayarak, fatura kaydı gibi kritik işlemlerin iptal edilme olasılığını azaltabilirsiniz.

Sunucu donanımını güçlendirmek kilitlenmeleri azaltır mı?

Kısmen. Daha hızlı disk ve daha fazla bellek sorguların daha çabuk bitmesini sağlar, böylece kilitler daha kısa tutulur. Ancak uzun açık kalan işlemler, eksik indeksler veya ters erişim sırası gibi tasarım kaynaklı sorunlar donanımla çözülmez. Önce nedeni tespit etmek, gereksiz yatırımı önler.

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.

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