SQL Server’da WITH (HOLDLOCK), bir sorgu çalıştırıldığında alınan kilitlerin (lock), işlem (transaction) tamamlanana kadar bırakılmamasını sağlayan bir tablo ipucudur (table hint).
Normal şartlarda SQL Server, bir veriyi okuduğunda (SELECT) işlemi bitirir bitirmez kilidi bırakır.(Read Committed) Ancak HOLDLOCK kullandığınızda, o veriyi adeta “rezerve” etmiş olursunuz.
HOLDLOCK, işlemi Serializable izolasyon seviyesine çeker. Veriyi sadece okumakla kalmaz, okuduğu kaynağı işlem bitene kadar “rehin” alır.
HOLDLOCK temel olarak şu iki şeyi garanti eder:
- Veri Tutarlılığı: Çok yüksektir. Siz veriyi okurken başka kimse o veriyi değiştiremez veya araya yeni satır ekleyemez (Phantom Reads engellenir).
- Kilit Türü: Paylaşımlı (Shared – S) kilit koyar ve bu kilit işlem (transaction) tamamlanana kadar serbest bırakılmaz.
- Risk: “Deadlock” (ölümcül kilitlenme) riskini artırır ve sistemin yavaşlamasına neden olabilir çünkü diğer yazma işlemleri sizi beklemek zorunda kalır.
READ COMMITTED (standart seviye) ile HOLDLOCK arasındaki en büyük fark “aralık koruması” (range protection) dediğimiz kavramdır.
Neden READ COMMITTED seviyesinde INSERT yapılabilirken HOLDLOCK (yani SERIALIZABLE) seviyesinde yapılamadığını şu şekilde açıklayabiliriz.
READ COMMITTED Neden İzin Verir?
Standart sorgularda SQL Server sadece o an var olan satırları kontrol eder.
- Bir SELECT sorgusu çalıştırdığınızda, SQL Server satırı okur ve işi biter bitmez kilidi bırakır.
- Tabloya yeni bir veri eklenmesi (INSERT), o an okuduğunuz satırları fiziksel olarak değiştirmediği için SQL buna izin verir.
HOLDLOCK kullandığınızda SQL Server sadece mevcut satırları kilitlemekle kalmaz, sorguladığınız mantıksal aralığı da kilitler. Buna Key-Range Locking denir.
Diyelim ki şu sorguyu çalıştırdın:
SELECT * FROM Siparisler WITH (HOLDLOCK) WHERE Id BETWEEN 10 AND 20READ COMMITTED: Sadece 10 ile 20 arasındaki mevcut satırları okur. O sırada başka biri Id = 15 olan yeni bir sipariş ekleyebilir. Buna “Phantom Read” (Hayalet Okuma) denir. Okuduğu satırdaki kilit dışında tüm satırlarda INSERT,UPDATE ve DELETE işlemi yapılmaktadır. Sorgunun ne kadar büyük olduğu (ister 1 satır, ister 1 milyon satır okusun) READ COMMITTED seviyesinde prensibi değiştirmektedir.
Eğer bir SELECT sorgusu çok fazla satırı (genellikle 5.000’den fazla) kilitlemeye başlarsa, SQL Server performansı korumak için tek tek satırları kilitlemek yerine Tabloyu Kilitlemeye (Table Lock / Lock Escalation) karar verir.
- İşte o an: Eğer SQL Server “Ben bu tabloyu komple kilitliyorum” derse, o SELECT bitene kadar tabloya tek bir INSERT bile yapılamaz.
- SELECT yapılabilir. (Çünkü okuma kilidi, diğer okuma kilitlerini engellemez).
- Yani sorgu çok büyükse, SQL Server “satır satır uğraşamam” diyerek kapıyı tamamen kapatır. Gemini’nin “yapamazsınız” dediği senaryo tam olarak budur.
- Mevcut satırların değiştirilmesine izin verilmez.
- Satır silinmesine izin verilmez.
SQL Server başlangıçta naziktir; sadece okuduğu satırı kilitler (Row Lock). Ancak okunan satır sayısı arttıkça ve sistem kaynakları (bellek/CPU) zorlanmaya başladıkça bu “nazik” tavrından vazgeçer. Tablonun tamamına kilit koyar.
SQL Server bir SELECT yaparken satırları sırayla okur. Bir satırı okur, işi bitince o satır üzerindeki “okuma kilidini” (Shared Lock) hemen bırakır ve bir sonraki satıra geçer. Sen tablonun sonundaki satırları okurken, bir başkası tablonun başına veya arasına yeni bir satır (INSERT) ekleyebilir. Çünkü SQL Server okuyup geçtiği yerleri artık korumuyordur. Sql server tablonun tamamına lock koymuş yeni eklenecek insert bu locklanan aralığın dışındadır. Ancak SQL Server’ın “Tablo Kilidi” (Table Lock) mantığı, fiziksel satır sırasından ziyade “Kapıyı Komple Kapatmak” üzerinedir.
HOLDLOCK Neden İzin Veririlmez?
HOLDLOCK ile SQL Server der ki; “İşlem bitene kadar 10 ile 20 arasına kimse yeni bir şey ekleyemez (INSERT), var olanı silemez (DELETE) veya bu aralığa gelecek şekilde güncelleyemez (UPDATE).” Sen WITH (HOLDLOCK) dediğinde SQL Server sadece mevcut satırları kilitlemekle kalmaz, o sayıların arasındaki “boşlukları” (Gap) da kilitler. HOLDLOCK, SQL Server’a şu mesajı verir: “Bu tablodaki verileri okuyorum ve işlemim bitene kadar bu tablonun içeriği (satır sayısı ve değerler) asla değişmemeli.”
HOLDLOCK kilit senaryosu iki duruma göre farklılık göstermektedir. Eğer belirli bir aralığı okumak isterseniz aralık koyulan alanda index varsa sql server belirtilen aralığı hemen bulur. kilidi sadece bu aralığa koymaktadır. Tabloda aralık dışında diğer kolonlarda insert, update ve delete serbesttir.
Okumak istediğimiz alanda index yoksa sql server belirli aralığı bulmak için tüm tabloyu okumak isteyecek bu da kilit türünün tablo bazlı olmasını sağlayacaktır. Bu işlem sonucunda insert,update ve delete işlemleri yapılamaz. Sadece okuma işlem yapılmaktadır. Okuma işlemi bir birini engellemez.
Session 1 bir satıra Shared (S) lock koyuyor:
BEGIN TRANSACTION;
SELECT * FROM Musteriler WITH (HOLDLOCK); -- Shared Lock (S)Not: WITH (HOLDLOCK) = Bu sorgu sırasında aldığım paylaşım kilidini (shared lock), transaction bitene kadar bırakma demektir. Tablonun tamamına lock koymaktadır. Satır veya range bazlı değildir. Detaylı bilgi için sayfadaki makale okunabilir.
Session 2 aynı satıra Shared (S) lock koymaya çalışıyor:
BEGIN TRANSACTION;
SELECT * FROM Musteriler WITH (HOLDLOCK); -- Shared Lock (S)Yukarıdaki iki işlemleri ayrı sessionlarda çalıştırdıktan sonra sonuçlarımız dönmüş oldu. Dikkat ederseniz commit işlemi yapılmadı.
Session 3 aynı satırı güncellemeye çalışıyor (Exclusive X lock gerekiyor):
BEGIN TRANSACTION;
UPDATE Musteriler SET Bakiye = 5000 WHERE MusteriID = 1;
Başka makalede görüşmek dileğiyle..
Başkaları ile alay etmeyin. Hucurat-11
