Bu makalede mssql server lock çeşitlerini detaylı bir şekilde görmüş olacağız. Veritabanındaki kaynaklara eşzamanlı erişimi kontrol etmek ve veri tutarlılığını sağlamak için kullanılır.
MSSQL Server Lock Türleri ve Örnekler
1- Shared (S) Lock
Okuma (SELECT) işlemleri için kullanılan kilittir. Birden fazla kullanıcı aynı anda aynı satırı okuyabilir. Çünkü shared lock birbirini engellemez ama insert-update-delete işlemlerini yapamaz. İlgili güncellemelerin yapılması için exclusive lock koyulması gerekmektedir. Veri okuma bittiğinde lock bırakılır. Pessimistic concurrency control yapısını kullanmaktadır. Shared lock olan diğer satırlar dışındaki satırlara başka kullanıcılar INSERT, UPDATE, DELETE yapabilir. Sadece ilgili satır, bölüm veya page’e bir müdahalede bulunamazlar.
Şimdi Örnek üzerinden yukarıdaki ifadelerin ne olduğunu gözlemleyelim
Şimdi bir shared lock oluşturup tablomuzun üzerinde işlemleri gözlemleyelim. Kullanıcı sadece belirli bir satırdaki veriyi okuyacak. Diğer satılarda update insert delete yapıldığını görmüş olacağız. Resimde dikkat ederseniz 1. ve 2. veri satırı üzerinde işlem yapılmaktadır.
3. satıra update işlemi olduğunda güncellendiğini görmekteyiz. İkinci resimde insert cümlesini görmekteyiz.
UPDATE Musteriler SET Bakiye = 0 WHERE MusteriID =3;
Kısacası select çekilen row-page-aralık dışındaki veriler üzerinde güncelleme veya başka bir işlem yapılabilmektedir.
SELECT * FROM Customers WITH (NOLOCK); → Bu komut, Shared Lock kullanmaz, okuma sırasında başka işlemler engellenmez. İlgili satırda başka kullanıcı insert/update/delete yapabilir. Read uncommitted isolation level seviyesini desteklemektedir. Dirty page non reaptable read ve phantom read okumalarına sebebiyet vermektedir.
Aşağıdaki komutta kullanıcı Shared (S) Lock ile veri okuyor:
USE AdventureWorks2014;
GO
BEGIN TRAN
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty = 10;
WAITFOR DELAY '00:00:10'; -- 10 saniye beklet (kilidi serbest bırakma)
COMMIT;Bu işlem, OrderQty = 10 olan satır üzerinde Shared (S) Lock oluşturur. Başka bir kullanıcı delay komutuyla belirtilmiş süre zarfı içerisinde aynı tablo ve koluna SELECT Çekiyor. İlk kullanıcının delay komutuyla select ifadesine shared lock koymasına rağmen ikinci kullanıcı aynı veriyi okur ve ilk kullanıcı ikinci kullanıcının çalışmasına engel olmaz çünkü Shared Lock’lar birbirini engellemez.
USE AdventureWorks2014;
GO
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty = 10;Aşağıdaki sorguda ise yukarıdaki kodda delay komutuyla belirtilmiş süre zarfı içerisinde aynı tablo ve kolonunu OrderQty = 10 olan satırları güncellemek ister. Ancak, İşlemler bitmeden çalışmaz çünkü UPDATE işlemi Exclusive (X) Lock gerektirir ve Shared Lock bunu engeller. Yukarıdaki sorguda herhangi isolation level belirtilmemişse Read committed isolation level kullanılır. OrderQty = 10 satırı dışında herhangi bir satır üzerinde güncelleme işlemi yapılır.
USE AdventureWorks2014;
GO
BEGIN TRAN
UPDATE Sales.SalesOrderDetail SET OrderQty = 15 WHERE OrderQty = 10;
COMMIT;Yukarıdaki işlemde sql üzerinde default olan Read committed isolation level seviyesini desteklediği için Shared lock ifadesi aldığı için yukarıdaki komut güncelleme işlemini yapmamaktadır. Güncelleme işlemi yapılmaması için ayrıca Repeatable read vb. isolation seviyelerinin kullanılması gerekmektedir. Bu isolation level seviyesinde delete ve güncellenme işlemi yapılmaz. Yukarıdaki örneklerle deneye bilirsiniz.
NOLOCK kullanıldığında:
- Başka işlemler satırı update/delete edebilir.
- Bu, dirty read’e neden olabilir (okunan veri henüz commit edilmemiş olabilir, yani rollback bile edilebilir).
- Satır okunduğu anda başka bir transaction onu değiştirmiş olabilir.
Herhangi bir satırda veya page’de exclusive lock varsa son kullanıcı WITH (NOLOCK) ile gelirse exclusive lock olan page veya satırı da okumaktadır.
- WITH (NOLOCK), mevcut exclusive lock’ları yok sayar ve kilitli veri sayfasına erişmeye çalışır.
- Ancak bazı özel durumlarda (özellikle yüksek trafikli sistemlerde veya büyük transaction’larda):
- Eğer sayfa hareket halindeyse (örneğin page split oluyor ya da başka bir işlem fiziksel yapıyı değiştiriyor),
- SQL Server veri bütünlüğünü korumak için geçici olarak erişimi engelleyebilir.
- Bu durumda WITH (NOLOCK) bile o an için veriye ulaşamayabilir ya da hatalı/eksik/çift veri okuyabilir.
Yani WITH (NOLOCK), genellikle exclusive lock’ları da aşarak okuma yapar, ama bu veri tutarlılığı açısından risklidir ve her zaman başarılı olacağı garantilenemez.
Yukarıdaki resimlerde dikkat ederseniz ikinci bir update cümlesi gelrse select ifadesinin üzerine sonucumuz dönmez. Update lock’ın exclusive lock’a dönüştüğünü söylemiştik.
2-Exclusive (X) Lock
Veriyi değiştiren işlemler (INSERT, UPDATE, DELETE) için kullanılır. Başka işlemler bu veriye erişemez. Diğer işlemler bu işlemin bitmesini beklemektedir. Exclusive Lock (X), yalnızca bir işlem tarafından alınabilir ve bu işlem başka hiçbir işlem tarafından okuma veya yazma işlemi yapılmasını engeller. Yani, bu kilit read-write (okuma ve yazma) işlemleri için engelleyici bir kilittir.
Şu yapının unutulmaması gerekmektedir. Lock row-belirli bir alan-page-tablo bazında olmaktadır. Güncellenen herhangi bir kolon üzerinde işlem yapılmaz diğer kolonlar üzerinde işlem yapılmaktadır.
Kullanıcı bir işlemi güncelleme işlemi yapmaya çalıştığında:
BEGIN TRAN
UPDATE Musteriler SET Bakiye = 4 WHERE MusteriID =3;Başka bir kullanıcı ilgili veri satırına select çekiyor.
BEGIN TRAN
Select *from Musteriler where MusteriID=3
With(nolock) ile bu sorun yaşanmaz.
Exclusive lock Shared lock ile uyumsuz olduğu için select ifademiz beklemektedir. Kullanıcı exclusive lock aldığı satır dışında diğer satırların okunması gerçekleşir. Aşağıdaki lock seviyesi yazısında bu durumun detaylı açıklaması görülmektedir.
Exclusive lock olan row dışında başka bir satır üzerinde güncelleme işlemi yapılmaktadır.
Exclusive lock’un kapsamı veritabanının kilit alma stratejisine ve sorgunun eriştiği nesneye bağlıdır.
Lock Seviyesi
Row-Level Lock: Genellikle tek bir satırı kilitler. (Row Identifier – RID)
Page-Level Lock: Bazen tüm sayfayı kilitler (Birden fazla satır etkilenebilir).
Table-Level Lock: Eğer SQL Server kilit yükseltme (Lock Escalation) yaparsa, tüm tabloyu kilitleyebilir.
Database-Level Lock: Çok nadiren olur, genellikle offline işlemler sırasında.
Örneğin, UPDATE veya DELETE sorgusu sadece bir satırı etkiliyorsa, yalnızca o satır (RID) kilitlenir. Eğer SQL Server kilit yükseltme (Lock Escalation) yaparsa, tüm tabloyu kilitleyebilir. Örneğin, UPDATE işlemi çok fazla satırı değiştirirse, SQL Server tek tek satırları kilitlemek yerine tablo seviyesinde (Table-Level Lock) bir X Lock alabilir. Yukarıdaki örnekte detaylı olarak açıklamasını görmüş olduk.
Kilitleme eşiği: SQL Server genellikle 5000’den fazla satır değiştirildiğinde lock escalation (kilit yükseltme) yapar ve tüm tabloyu kilitler.
Eğer aynı sayfadaki birçok satır siliniyorsa, SQL Server Page-Level Lock kullanabilir. Bazen bu lock, tüm tabloya yükseltilebilir.
Exclusive Lock (X), o satır üzerinde tam erişim sağlar. Yani, satır sadece bu işlem tarafından güncellenebilir ve başkası ne okuyabilir ne de yazabilir. Bu kilit, satıra tam sahiplik sağlar ve bu nedenle, aynı satır üzerinde başka bir işlem tarafından herhangi bir okuma veya yazma işlemi yapılmasına izin vermez.
UPDATE Sales.SalesOrderDetail SET OrderQty = 15 WHERE OrderQty = 10;Güncelleme sırasında satıra Exclusive Lock koyulur, başka işlemler bekler. Sadece With(NOLOCK) olan select cümleleri bu kilidi görmezden gelerek(dirty read) yaparak veriye erişebilmektedir. Sorgunun with(nolock ) ile Read Uncommitted gibi çalışmasını sağlayan bir yapısı vardır.
3- Update (U) Lock
Deadlock (kilitlenme) riskini azaltmakiçin kullanılan bir kilittir. Önce Shared Lock alır, sonra gerekirse Exclusive Lock’a çevirir. Update Lock (U), okuma ve yazma işlemlerine engel olacak şekilde işlev görür, ancak başka bir işlem bu satır üzerinde Shared Lock (S) alarak okuma yapabilir. Update Lock (U), SQL Server’ın bir satır üzerinde yazma işlemi yapmadan önce, o satırı “güncellenmeye hazırlanmak” amacıyla kilitlemesidir. Bu kilit türü, genellikle row-level kilidi olarak kullanılır ve Shared Lock (S)’in aksine, U kilidi satıra yalnızca yazma işlemi yapılacağı zaman alınır. Bu kilit, bir satırın güncellenmesine niyetlendiğini belirtir ve satır üzerinde yazma işlemi yapılacağı zaman başka bir işlem yazmaya müdahale edemez, ancak okuyabilir. Kısacası başlangıç adımı olarak U kilidinde select sorguları sıkıntı yaşamaz. Ne zaman U kilidi X kilidine dönüşürse sadece bu satır için bekleme yaşanmaktadır. SQL Server *lock escalation* denen bir mekanizmayla çok sayıda satır seviyesi kilidi sayfa veya tablo seviyesine yükseltebilir.
Gözlemlerim sonucu update işlemi herhangi bir satıra uygulandıktan sonra başka bir kullanıcı aynı satırı okuyamıyor. Shared lock alınır. Aynı satırı tekrardan güncelleme işlemi yapıldığında exclusive oluşuyor.
Aşağıdaki komut ile update işlemi başlatılır.
Başka bir kullanıcı ilgili tabloya select çektiğinde sonuç dönmez.
Lock konulan satır dışında başka bir satıra select çekildiğinde sonucumuz döner.
İlgili satıra başka bir kullanıcı lock koyduğunda ilgili lock yeni gelen kullanıcıda Exclusive lock’a dönüşür.
Lock Satır bazında olduğu için başka satırların update işlemi sıkıntısız gerçekleştirilir.
Tablo bazlı bir lock olmadığı için insert işleminde herhangi bir sıkıntıya sebebiyet vermez.
İlgili satıra herhangi bir delete işlemine izin vermez.
Yukarıda dikkat edersek tablomuzun üzerinde Update lock varken select işlemi farklı bir satır üzerinden gerçekleşmişti. Exclusive lock oluştuğunda aynı komut üzerinde select komutu çalıştırdığımızda locklar birbirleriyle uyumsuz olduğu için select işlemi gerçekleşmez.
UPDATE Customers SET Age = Age + 1 WHERE ID = 2;Önce Update Lock (U) alır, eğer başka kimse kullanmıyorsa Exclusive Lock’a çevirir.
Deadlock Senaryosu (Update Lock Kullanımı)
BEGIN TRAN
UPDATE [Person].[Person] SET FirstName = 'Gigi' WHERE BusinessEntityID = 1; -- Update Lock (U)
WAITFOR DELAY '00:00:10' -- 10 saniye beklet
UPDATE [Sales].[SalesOrderDetail] SET OrderQty = 500 WHERE SalesOrderID = 1; -- Exclusive Lock (X)
COMMIT;BEGIN TRAN
UPDATE [Sales].[SalesOrderDetail] SET OrderQty = 600 WHERE SalesOrderID = 1; -- Update Lock (U)
WAITFOR DELAY '00:00:10'
UPDATE [Person].[Person] SET FirstName = 'John' WHERE BusinessEntityID = 1; -- Exclusive Lock (X)
COMMIT;Yukarıdaki iki komut deadlock oluşturabilmesi için ayrı sessionlarda aynı anda çalışması gerekmektedir. Deadlock oluşur çünkü her iki işlem birbirini bekliyor. Yukarıdaki kodda belirtilen sırasıyla update lock ve exclusive durumlarına değinelim.
İlk UPDATE komutunda Update Lock (U) kullanılır çünkü SQL Server, işlem başlamadan önce yalnızca satırda bir değişiklik yapılacağına dair bir hazırlık yapmaktadır. Bu U kilidi, SQL Server’ın güncelleme yapma niyetini gösterir. U kilidi, satırın sadece güncellenmeye uygun olup olmadığını kontrol etmek içindir. Yani, bir işlem U kilidi aldıktan sonra, başka bir işlem bu satırı okuyabilir ancak yazamaz. Daha sonra, işlem X kilidi almak için ilerler.
Transaction 1’de İkinci UPDATE komutunda Exclusive Lock (X) alınır yukarıda resimdede görmüş olduk çünkü, satırdaki veri gerçekten güncelleniyor ve tam erişim gereklidir. Exclusive Lock (X), bu satır üzerinde yapılan güncellemeyi başka hiçbir işlemle paylaşmanıza izin vermez. X kilidi, güncellemeyi tam anlamıyla tamamlamak ve veri üzerinde tam bir kontrol sağlamak için gereklidir.
U kilidi başlangıç aşaması için, X kilidi ise işlem tamamlanana kadar tam kontrol sağlamak için gereklidir.
Transaction 1, SalesOrderDetail tablosunda Exclusive Lock (X) almak ister, ancak Transaction 2 bu satırı Update Lock (U) ile kilitlemiştir. Dolayısıyla Transaction 1 T2’nin kilidini bekler.
Transaction 2 ise Person tablosunda Exclusive Lock (X) almak ister, ancak Transaction 1 bu satırı Update Lock (U) ile kilitlemiştir. Bu nedenle Transaction 2 de T1’in kilidini bekler.
Transaction 1’in ilk UPDATE komutu (Person tablosunda) ile Transaction 2’nin ikinci UPDATE komutu (Person tablosunda) arasında doğrudan bir bağlantı vardır, çünkü her iki işlem de Person tablosundaki aynı kaynağı kilitlemeye çalışır.
Aynı şekilde, Transaction 1’in ikinci UPDATE komutu (SalesOrderDetail tablosunda) ile Transaction 2’nin ilk UPDATE komutu (SalesOrderDetail tablosunda) arasında da bir bağlantı vardır, çünkü her iki işlem de SalesOrderDetail tablosundaki aynı kaynağı kilitlemeye çalışır.
Deadlock oluşmaması için yapılacak olan adım Transactions’ları kısa tutulması gerekmektedir. Update işlemlerini hep aynı satırda yapılması gerekmektedir. NOLOCK veya READ COMMITTED kullanarak kilitleri optimize etmek gerekmektedir.
Bu kilit geçici bir önkilit (pre-emptive lock) olarak kullanılır. Yani, SQL Server verinin gerçekten güncellenip güncellenmeyeceğini görmek için bu kilidi alır.
Eğer veride gerçekten bir değişiklik yapılacaksa, bu durumda Update Lock Exclusive Lock’a dönüşür.
Update Lock kullanmak, SQL Server’ın gereksiz yere güçlü kilitler koymaktan kaçınmasını sağlar ve kilit çakışmalarını azaltır.
4-Intent Locks (IS, IX, IU)
Daha büyük bir objeye (Tablo, Sayfa) alınan kilitlerin belirtilmesi için kullanılır. Örneğin, bir satıra X (Exclusive) kilidi koyulacaksa, önce tabloya IX (Intent Exclusive) kilidi koyulur. Lock sıraya konulur. Sorgu beklerken intent lock koyarak bir sonraki lock koyacak transaction’ın kendisi olduğunu belirtir ve diğer transaction’ların lock koymasını engeller.
Intent kilitler, SQL Server’ın hiyerarşik bir yapıda (örneğin, tablo, sayfa, satır) kilitleri yönetmek için kullandığı bir mekanizmadır. Bu kilitler, daha büyük bir kaynağın (örneğin, bir tablo) altındaki daha küçük kaynakların (örneğin, sayfalar veya satırlar) kilitlenebileceğini belirtmek için kullanılır. Bu, SQL Server’ın kilit uyumluluğunu ve ölçeklenebilirliğini artırır.
UPDATE Customers SET Name = 'Veli' WHERE ID = 3;Tabloya IX (Intent Exclusive) Lock, satıra X (Exclusive) Lock koyulur.
Birkaç tipi vardır:
Intent Shared (IS): Şu an lock’lı durumda olan kaynaktan bir kısmını okuyacağını belirten lock tipidir. Lock bittiğinde Shared Lock koyarak ihtiyacı olan veriyi okur. Shared Lock koyduğunda Intent Shared Lock’ı kaldırmaz. İşi bittiğinde iki lock türünü beraber kaldırır.
Intent Exclusive (IX): Şu an lock’lı durumda olan kaynaktan bir kısmını değiştireceğini belirten lock tipidir. Lock bittiğinde Exclusive Lock koyarak ihtiyacı olan veriyi değiştirir. Exclusive Lock koyduğunda Intent Exlusive Lock’ı kaldırmaz. İşi bittiğinde iki lock türünü beraber kaldırır.
Shared With Intent Exlusive (SIX): Şu an lock’lı durumda olan kaynağın tamamını okuyacağını ve bir kısmını değiştireceğini belirten lock tipidir. Hem tablo düzeyinde Shared (S) kilidi hem de alt seviye kaynaklarda Exclusive (X) kilit alınacağını belirtir.
LCK_M_IX, bir işlemin Intent Exclusive (IX) kilidi almak için beklediğini gösterir. Bu, genellikle aşağıdaki durumlarda ortaya çıkar:
1. Bir işlem, bir tablo veya sayfa üzerinde IX kilidi almak istiyor.
Örneğin, bir UPDATE veya DELETE işlemi, belirli bir satırı değiştirmek için önce tablo düzeyinde bir IX kilidi alır. Bu, alt seviye kaynaklarda (örneğin, satırlar) Exclusive (X) kilit alınacağını belirtir.
2. IX kilidi, başka bir işlem tarafından engelleniyor.
Örneğin, başka bir işlem aynı tablo veya sayfa üzerinde zaten bir Exclusive (X) veya Shared (S) kilidi almış olabilir. Bu durumda, IX kilidi almak isteyen işlem, diğer işlemin kilidini serbest bırakmasını bekler.
Örnek bir senaryo üzerinde açıklayalım:
Bir işlem Bir tablo üzerinde Exclusive (X) kilidi alır ve bir satırı günceller.
BEGIN TRAN;
UPDATE Sales.SalesOrderDetail SET OrderQty = 5 WHERE SalesOrderID = 43659;COMMIT veya ROLLBACK yapılmadı, X kilidi devam ediyor.
Başka bir işlem Aynı tablo üzerinde Intent Exclusive (IX) kilidi almak ister.
BEGIN TRAN;
UPDATE Sales.SalesOrderDetail SET OrderQty = 10 WHERE SalesOrderID = 43659;Birinci işlemin X kilidi nedeniyle IX kilidi bekler (LCK_M_IX).
Bu durumda, İşlem 2, İşlem 1‘in kilidini serbest bırakmasını bekler ve LCK_M_IX durumunda kalır.
LCK_M_IX beklemeleri, genellikle aşağıdaki nedenlerle ortaya çıkar:
Uzun süren işlemler (örneğin, açık bir transaction).
Yanlış tasarlanmış sorgular veya indeksler.
Aşırı kilitlenme (deadlock) durumları.
Bu durumu çözmek için:
1. Uzun süren işlemleri kontrol edin ve gereksiz transaction’ları kapatın.
2. Sorguları optimize edin (örneğin, indeks ekleyin veya sorguyu yeniden yazın).
3. Deadlock’ları analiz edin ve gerekirse işlem sıralamasını değiştirin.
Sonuç olarak:
LCK_M_IX, bir işlemin Intent Exclusive (IX) kilidi almak için beklediğini gösterir. Bu, genellikle bir tablo veya sayfa üzerinde Exclusive (X) kilit alınacağını belirtir. Çözüm için işlemleri optimize edin ve gereksiz kilitlenmeleri önleyin.
5-Schema Lock (Sch-S, Sch-M)
Şema (Tablo yapısı) değişikliklerini korur. Sch-S sadece okuma içindir, Sch-M ise tablo değişikliklerinde kullanılır.
ALTER TABLE Musteriler alter column Ad NVARCHAR(100);
Sch-M (Schema Modification Lock) alınır, başka işlemler bekler. Online index oluşum işlemlerinin son aşamasında ise aynı locklama türü ile tablo locklanmaktadır.
6- Key Range Lock
Serializable transaction isolation level kullanıldığında, bir sorgu select çekerse, select çektiği satır aralığını lock’layan lock çeşididir.
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31'Eğer bu veri aralığında başka bir işlem veri eklemeye çalışırsa, Key Range Lock devreye girer.
Sonuç Olarak:
SQL Server’da kilit türlerini anlamak, performansı artırmak ve deadlock’ları önlemek için kritiktir.
Shared Lock sadece okuma için, Exclusive Lock ise veri değişimi için kullanılır. Update Lock, gereksiz kilitlenmeleri önler. Intent Locks, Büyük ölçekli işlemlerde kilitlerin sırasını yönetir. Deadlock senaryolarını önlemek için transaction sıralamasına dikkat etmek gerekir.
Aşağıdaki komut sayesinde anlık gelen sorgularımızdaki kilit durumlarını görebiliriz.
SELECT
session_id AS SessionID,
blocking_session_id AS BlockingSessionID,
wait_type AS WaitType,
wait_time AS WaitTime,
wait_resource AS WaitResource
FROM
sys.dm_exec_requests
WHERE
blocking_session_id > 0;
Bir başka komut ise:
Sp_whoIsactive ekranındada ilgili kilitlenmeleri görebiliriz.
Veritabanımızın üzerindeki lock’ları aşağıdaki uzantıda görebiliriz.
Bu makalede mssql server lock çeşitlerini görmüş olduk. Başka bir makalede görüşmek dile
“Lokmân, “Sevgili oğlum” (dedi), “Yaptığın iş bir hardal tanesi ağırlığında bile olsa, bir kayanın içinde saklansa veya göklerde yahut yerin dibinde bulunsa yine de Allah onu açığa çıkarır. Kuşkusuz Allah her şeyi bütün gizlilikleriyle bilir, O her şeyden haberdardır.”Lokman-16
