SQL Server’da LOCK Nedir?

Lock veri tutarlılığını sağlamaya yaran bir mekanizmadır. Satır düzeyinde, sayfa düzeyinde, tablo düzeyinde ve veritabanı düzeyinde bir lock uygulanabilir.

Lock türlerini aşağıdaki tablodan ulaşabilirsiniz.

Lock Mod	Açıklama
Shared (S)	SELECT ifadesi gibi verileri değiştirmeyen veya güncellemeyen işlemler (salt okunur işlemler) için kullanılır.
Update (U)	Güncellenebilen kaynaklarda kullanılır. Birden çok oturum kaynakları okurken, locklarken ve potansiyel olarak daha sonra güncellerken ortaya çıkan yaygın bir kilitlenme biçimini önler.
Exclusive (X)	INSERT, UPDATE veya DELETE gibi veri değiştirme işlemleri için kullanılır. Aynı kaynağa aynı anda birden fazla güncelleme yapılamamasını sağlar.
Intent	Hedef kilidi, bir işlem tarafından, başka bir işlemi lock edinme hedefi hakkında bilgilendirmek için kullanılır.
Bulk Update (BU)	Bulk update prosedürü işlenirken diğer işlemlerin bir tabloya erişmesini önlemek için kullanılır.
Schema	Bir tablonun şemasına bağlı bir işlem yürütülürken kullanılır. Şema kilitlerinin türleri şunlardır: şema değişikliği (Sch-M) ve şema kararlılığı (Sch-S).
Deadlock	İki veya daha fazla işlemin karşılıklı olarak birbirlerinin kilitlediği kaynaklara erişmek istemesiyle oluşur.

Shared(S): Bu lock türü, uygulandığında, yalnızca okunmak üzere kullanılabilir. Bir sayfa veya satır ayırır, bu da lock etkin olduğu sürece lock kaydı değiştirmek için başka herhangi bir işlemin engelleneceği anlamına gelir. Bununla birlikte, paylaşılan bir lock, aynı sayfa veya satır üzerinde aynı anda birkaç işlem tarafından uygulanabilir ve bu şekilde, okuma işleminin kendisi gerçek sayfa veya satır verilerini hiçbir şekilde etkilemeyeceğinden, birkaç işlem veri okuma yeteneğini paylaşabilir. Eşzamanlı işlemlerin bir kaynağı okumasına (SELECT) izin verir, ancak DDL değişikliklerine izin verilmez

Update(U): Bir tablo üzerinde update işleri yapılırken oluşur. SQL Server, güncellenen verileri okumaya kalktığında kullanılır. Güncelleme sırasında, update lock, exclusive lock a dönüşür. Exclusive lock, birden fazla transaction ın aynı satırları güncellemeye çalışıp, deadlock oluşturmasını engeller.

Exclusive (X): Eşzamanlı işlemlerle bir kaynağa erişimi engeller. Başka hiçbir işlem, exclusive (X) lock ile kilitlenmiş verileri okuyamaz veya değiştiremez. DML ifadeleri DELETE, INSERT ve UPDATE durumunda, sayfa veya satır verilerini değiştirmek istediğinde işlem tarafından uygulanacaktır.

Kullanıcı verileri güncellediğinde, işlemi, diğer işlemlerin verilere erişmesini engelleyen özel bir veri kilidi tutar.

Örnekle açıklamak gerekirse,

Aşağıdaki sorgularda USER 1 önce ID’si 1 olan bir kaydı günceller, ardından 20 saniye sonra aynı kaydı tekrar günceller sonra rollback yaparak tüm işlemi geri alır. O sırada USER 2 aynı veriyi çekmeye çalışır fakat USER 2 herhangi bir kayıt görüntüleyemez. Exclusive lock oluştuğunda kullanıcılar değeri seçemez, güncelleyemez ve ekleyemez.

20 SANİYE SONRA İŞLEM TAMAMLANDIKTAN SONRA SORGU ÇIKTILARI

Intent: Bu lockun amacı, başka bir işlemin bir sonraki hiyerarşi nesnesinde bir lock almasını engelleyerek veri değişikliğinin düzgün bir şekilde yürütülmesini sağlamaktır. Pratikte, bir işlem satır üzerinde bir lock almak istediğinde, daha yüksek bir hiyerarşi nesnesi olan bir tablo üzerinde bir amaç locku edinecektir. intent lock alarak, işlem diğer işlemlerin o tablodaki exclusive locku almasına izin vermeyecektir (aksi takdirde, başka bir işlem tarafından uygulanan exclusive lock, satır kilidini iptal eder).

Bu, performans açısından önemli bir lock türüdür, çünkü SQL Server veritabanı, işlemin bu tabloda güvenli bir şekilde bir lock elde etmesinin mümkün olup olmadığını kontrol etmek için intent locklarını yalnızca tablo düzeyinde inceler ve bu nedenle intent lock ortadan kalkar. işlemin tüm tablo üzerinde lock alabileceğinden emin olmak için bir tablodaki her satır/sayfa locklarını incelemeniz gerekir.

Farklı türlerden Intent Lock vardır.

Intent shared (IS): lock istekleri veya shared lockları korur. Bir kayıtta bir shared lock oluşmuş ise, ilgili kaydın bulunduğu page üzerinde intent shared lock oluşur.

Intent exclusive (IX): Lock Intent shared lock ın bir üstü seviyesidir. Bir kayıtda exclusive lock varsa, ilgili kaydın bulunduğu page üzerinde Intent Exclusive Lock konulur.

Shared with intent exclusive (SIX): lock kilitleme hiyerarşisinde daha alt seviyede duran shared lock ları korur. Bir tabloda shared with intent exclusive lock oluşursa, değişiklik yapılan page üzerinde intent exclusive lock da yer alır.

Intent update (IU): Lock güncellenen tablonun bulunduğu sayfalardaki, shared ve istenilelen diğer lockları korur.

Shared intent update lock, shared ve intent update locklarının birleşimininden oluşur. Bir transaction bir tablodan okuma yaptığında shared lock oluşur, daha sonra aynı transaction bir update işlemi yaparsa o zaman shared intent update lock a dönüşür. Bir tabloda select cümlesi çalıştırılırken, PAGLOCK hint i kullanılarak çekildiğinde de oluşan lock türüdür.

Birbirini kapsayan birden fazla lock olmasının nedeni, SQL Server’ın aynı anda bir tablo üzerinde oluşan birçok lock yerine, tek bir lock ile çalışmasının daha verimli olmasındandır.

Bulk Update(BU): Bulk Update (BU) lockları, verileri bir tabloya toplu olarak kopyalarken ve TABLOCK ipucu belirtildiğinde veya toplu yükleme tablosunda tablo lock seçeneği sp_tableoption kullanılarak ayarlandığında kullanılır. Bulk Update (BU) lockları, verilerin toplu olarak kopyalanması olmayan diğer işlemlerin tabloya erişmesini engellerken, işlemlerin verileri eşzamanlı olarak aynı tabloya toplu olarak kopyalamasına olanak tanır.

Schema Locks: Şema değiştirme (Sch-M) lockları, bir tablo veri tanımlama dili (DDL) işlemi (sütun ekleme veya tablo bırakma gibi) gerçekleştirilirken kullanılır.

Sorgular derlenirken şema kararlılığı (Sch-S) lockları kullanılır. Şema kararlılığı (Sch-S) lockları, exclusive (X) locklar da dahil olmak üzere hiçbir işlem kilidini engellemez. Bu nedenle, bir tabloda özel (X) kilitleri olan işlemler de dahil olmak üzere, sorgu derlenirken diğer işlemler çalışmaya devam edebilir. Ancak tablo üzerinde DDL işlemleri yapılamaz.

Deadlock : İki veya daha fazla işlemin karşılıklı olarak birbirlerinin kilitlediği kaynaklara erişmek istemesiyle oluşur. Her iki işlem de sürekli birbirlerini beklediği için sistem kaynakları olumsuz yönde etkilenir. Özellikle sunucunun işlemci değeri boşuna harcanmış olur. Bu da, sunucunun performansını olumsuz yönde etkiler ve sunucuyu cevap veremez duruma bile getirebilir.

Örnek ile destekleyecek olursak;

Task T1’in R1 kaynağı üzerinde bir kilidi var (R1’den T1’e kadar olan okla gösterilir) ve R2 kaynağı üzerinde bir lock talep etti (T1’den R2’ye kadar olan okla gösterilir).
Task T2’nin R2 kaynağı üzerinde bir kilidi var (R2’den T2’ye okla gösterilir) ve R1 kaynağı üzerinde bir lock talep etti (T2’den R1’e okla gösterilir).

Bir kaynak kullanılabilir olana kadar hiçbir task devam edemeyeceğinden ve bir task devam edene kadar hiçbir kaynak serbest bırakılamayacağından, bir kilitlenme durumu vardır.
SQL Server kilitlenmesini çözmenin tek yolu, işlemlerden birini sonlandırmak ve işlemin tamamlanabilmesi için kilitli kaynağı serbest bırakmaktır. Sp_whoisactive prosesini çalıştırarak deadlocka düşen tablolar görülebilir. Activity Monitor kullanılarak aktif olan kilitlinmeler görülüp proseslerin üzerinde kill yapılarak deadlocklar çözülebilir.

Locks Hiyerarşisi

SQL Server, verilere erişilirken veya güncellenirken gerekli olan locks hiyerarşisini bizlere sundu. Kilit yapısı en üstte veritabanı ile başlar, ardından en son satıra tablolar ve sayfalar gelir. Locklar, kilitlenme ve yarış koşulu (race condition) oluşmasını engellediği için yukarıdan aşağıya hiyerarşiye yerleştirilir. Aşağıdaki şekil bunu daha iyi göstermektedir:

Locklar her zaman yukarıdan aşağıya doğru alınır, çünkü bu şekilde SQL Server, yarış koşulu (race condition)denen bir durumun oluşmasını engeller.

Veritabanına bağlı bir işlem sırasında veritabanı düzeyinde her zaman shared lock bulunur. Veritabanı düzeyinde shared lock , veritabanının düşürülmesini veya bir veritabanı yedeğinin geçerli veritabanı üzerinden geri yüklenmesini önlemek için kullanılır. Verileri okumak için bir SELECT sorgusu kullanıldığında, örneğin, veritabanı düzeyine bir shared lock(S) yerleştirilir, tablo ve sayfa düzeyine bir Intent shared (IS) yerleştirilir ve bir shared locks (S) sıra seviyesine yerleştirilmiştir.

Bir kullanıcı veri tabanından veri okumak istediğinde, yani bir SELECT deyimi kullanarak kilitleme hiyerarşisine bakalım:

Data Modification (veri ekleme, güncelleme, silme gibi) durumunda, veritabanı düzeyinde bir shared lock (S), tablo ve sayfada bir Intent shared lock (IS) ve bir exclusive veya update lock( X veya U), kilitlenme durumunu önlemek ve veritabanının düşmesini önlemek için satır düzeyinde yerleştirilir.

Bir kullanıcı DML , yani INSERT, UPDATE ve DELETE deyimlerini kullanarak verileri değiştirmek istediğinde kilitleme hiyerarşisine bakalım.