SQL Server Büyük Boyutlu Veri Silme ve Kilit Yükseltme (Lock Escalation)
Veritabanımızda yoğun verinin olduğu bir tablodan büyük boyutlarda veri silerken Lock Escalation’a sebep olabiliriz. Bu ise aynı tabloyu kullanan diğer uygulamaların siz işleminizi bitirmeden tabloya erişebilmesini engeller.
Size bahsetmek istediğim şey erişim seviyeleri yönetimi değil tablo kilitleme eşiğine ulaşmayan sorgular yazabilmek ile ilgili.
Öncesinde kısaca Lock Escalation nedir, açıklayalım:
Tablo üzerinde satır bazlı işlemler gerçekleştirirken (örn. DELETE, UPDATE vs. ) o satır bu işlem bitinceye kadar kilitlenir. Eğer çok fazla satır için işlem yaparsanız hepsi kilitlenir. Eğer bu kilitli satır sayısı bir seviyeyi geçince (örn: varsayılan sayı 5000) SQL Server şunu düşünür: bu istemci çok fazla satırda değişiklik yapıyorsa diğer işlemlerin selameti açısından bu tabloyu geçici olarak trafiğe kapatayım. Yani tablonun tamamını size tahsis ediyor. Başlangıçta satır bazlı kilitlemeler yapıyordunuz fakat sonra SQL Server sizin için tüm tabloyu kilitledi. İşte buna Lock Escalation (Kilit Yükseltme) denir.
Siz örneğin eski verileri silerken diğer uygulamalar yeni verilere erişsin ve bir başka uygulama da yeni kayıtlar atabilsin istiyorsanız aşağıdaki Şekil.1 görselindeki gibi işlem boyunca tabloyu meşgul etmek yerine Şekil.2’deki gibi araya girebilecekleri boşluklar bırakmalısınız.
DECLARE @DT DATE = (SELECT MIN(logtime) FROM templogs)
DELETE FROM templogs WHERE CAST(logtime AS DATE) = @DT
Sorgusunu çalıştırırsanız çok fazla sayıda veri silininceye kadar tablonun kilitlenmesine ve bu tabloyu kullanan diğer uygulamalarınızın geçici olarak cevap verememesine sebep olursunuz.
Oysa bu sorguyu şöyle yazmalıyız:
DECLARE @DT DATE = (SELECT MIN(logtime) FROM templogs)
SET ROWCOUNT 1000
delete_step:
DELETE FROM templogs WHERE CAST(logtimeAS DATE) = @DT
IF @@ROWCOUNT > 0 GOTO delete_step
SET ROWCOUNT 0
Her 1000 satırlık silme işleminden sonra lock olan satırları serbest bırakmak suretiyle kilit yükseltmesi seviyesine erişmeyi engellemiş oluyoruz.
(Aynı amaç için WAITFOR DELAY ’00:00:01’ de kullanabiliriz. Her 1000 silmeden sonra 1 saniye bekle)
Böylece eski verileri operatif tablodan silme işlemi sırasında diğer uygulamalarımız her daim isteklere cevap verebilecektir.
