SQL Server Lock ve Lock Escalation

Lock Escalation: SQL Server’ın aşırı memory kullanımını önlemek için kullandığı lock yükseltme yöntemidir. Tek transaction tek bir tablo yada index üzerinde 5K+(key, page) lock koyarsa, lock’ı bir üste taşıyıp Ojbect’i lock’lamaktadır.

Not : ALTER TABLE SET LOCK_ESCALATION option is set to AUTO — ise 5K+ kuralı geçerlidir.

Hemen bir örnekle deneyelim, Index olmayan bir alan üzerinde UPDATE işlemi yapalım.

BEGIN TRAN
UPDATE Users SET DownVotes=14
WHERE DownVotes=14
ROLLBACK;

Daha sonra session_id ile beraber lock tipini kontrol ediyorum.

SELECT resource_type,request_mode,count(1) 
FROM sys.dm_tran_locks
WHERE request_session_id=67
GROUP BY resource_type,request_mode

Görüldüğü gibi, SQL Server 5K+ Page yada Row’a lock koymak yerine lock’ı bir üst seviyeye taşıdı yani escalation işlemi yaptı ve Obje üzerine lock konuldu.

SELECT TOP 10 Id,DownVotes
FROM Users 
WHERE DownVotes=100

Object üzerinde X lock olması sebebiyle SELECT sorgusu locklanıyor.

Çözüm için okunacak Page veya Row değerlerini düşürmek adına sorguyu yararlanacağı bir index atıyorum.

CREATE INDEX DownVotes ON Users (DownVotes);
BEGIN TRAN
UPDATE Users SET DownVotes=14
WHERE DownVotes=14
COMMIT

SELECT TOP 10 Id,DownVotes
FROM Users 
WHERE DownVotes=100

5000 sınırına dayanmadığı için Lock Escale edilmedi Page ve Row bazında devam etti, bu durumda da diğer sorgularımızdan lock konulan Page veya Row dışında erişimlerde herhangi bir block’lanma yaşanmayacaktır.

UPDATE ve DELETE Lock Tuning

• Koşul olması ve koşula ait index olması 
  • Sorgu hızını arttırır.
  • Page/Object lock yerine key lock oluşur.
  • Lock escalation önler.
• Güncellenecek veya silinecek kayıt fazla ise Batch yöntemi kullanılması gerekir.

Recap – Lock Tuning

• Lock Tuning with Indexes
  • Değişikliğin yapılacağı dataya kolayca ulaşabilecek bir index oluşturulmalıdır.
  • Kullanılmayan, az kullanılan ve duplicate index’ler kontrol edilmelidir.
• Lock Tuning for Lock Escalation
  • Değişiklikler Chunk’lara bölünerek yapılmalıdır, tek seferde yüklü işlemler yapmak yerine 10k olacak şekilde parçalanabilir.
• Lock Tuning for Transaction
  • Transaction sadece gerekli durumlarda kullanılmalıdır.
  • Index’lenmemiş ForeignKey’ler Lock uzamasına sebep olabilir.
  • sp_getapplock
  • Transaction içinde Linked Server, xp_cmdshell vs kullanılmamalıdır.

• Lock durumundaki Spool operatörleri (Hallowen Protection) temp table kullanılarak optimize edilebilir.
• Loop ve Merge not blocking operatörlerdir, Hash blocking operatördür.
• Read Lock: Okuma esnasında lock’lar satır satır işlerken Key Lookup olduğunda bu durum değişir. Bu sebeple Key Lookup operatörüyle birlikte gelen Sort lock’a sebebiyet verebilir. 

Monitor with XE 

CREATE EVENT SESSION [Lock_Escalator] ON SERVER ADD EVENT sqlserver.lock_escalation(SET collect_statement=(1)) 
ADD TARGET package0.event_file(SET filename=N'E:\Lock_Escalator.xel') 
WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=NO_EVENT_LOSS, MAX_DISPATCH_LATENCY=1 SECONDS, MAX_EVENT_SIZE=0KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
GO

XE kurulumuyla birlikte lock escalation’ları rahat bir şekilde izleyebiliyoruz. Demo’daki sorgu için index atıldığından lock escalation kalkıyor ve extend event üzerine yansıyan herhangi bir operasyon bulunmuyor.