Deadlock Nedir? Extended Events ile Deadlock Nasıl İzlenir

Deadlock iki veya daha fazla transactionların/proseslerin birbirini kilitlemesi ve birbirini beklemesi olarak kısaca açıklayabiliriz. Örnekle anlatacak olursak elimizdeki bir ürünü iki müşteri aynı anda satın almak istediğinde bir kilitlenme meydana gelir iki kişiden birinin vazgeçmesi gerekmektedir ki diğeri ürünü satın alabilsin aksi taktirde ne zaman biteceği belli olmayan bir beklemenin içine girebiliriz ya da olaya biz müdahale olarak ürünü birine vererek diğer müşteriyi göndermemiz gerekecektir.

Resim 1

SQL Server tarafında bu olay Resim 1’deki gibi Transaction 1 ,  A tablosunu kilitledikten sonra B tablosuna erişmek istiyor ama Transaction 2’nin B tablosunu kilitlemesinden dolayı bekliyor. Aynı şekilde Transaction 2 , A tablosuna erişmek için Transaction 1 tarafından A tablosuna konulan kilidin kalkmasını bekliyor. Burada SQL Server olaya müdahale olarak timeout süresinden sonra Rollback maliyeti daha az olan sorguyu kurban(victim) olarak seçiyor ve kilidi kaldırıyor ve victim transactionı rollback yapıyor

Resim 2

Şimdiye kadar tablo olarak ifade etsek de SQL Server daha küçük ve büyük birimleri kilitleyebilir. Bunlara bakacak olursak

RID

KEY

PAGE

EXTENT

HoBT

TABLE

METADATA

ALLOCATION_UNIT

DATABASE

Bir satırlık kilitlenmeden page,tablo veya veri tabanına kadar büyüyen bir kilit mekanizması bulunmaktadır. Bunların detayına buradan ulaşabilirsiniz

Ufak bir örnekle bu işleyişin SQL Serverda nasıl gerçekleştiğini inceleyelim

Öncelikle 2 adet tablo oluşturmakla ve veri girmekle başlıyorum

Resim 3

DROP TABLE IF EXISTS Table_A
  CREATE TABLE Table_A (Id INT PRIMARY KEY, Brand VARCHAR(50))
  GO
  INSERT INTO Table_A VALUES(1,'Mercedes')
  INSERT INTO Table_A VALUES(2,'BMW')
  GO
  DROP TABLE IF EXISTS Table_B
  CREATE TABLE Table_B (Id INT PRIMARY KEY, Brand VARCHAR(50))
  GO
  INSERT INTO  Table_B VALUES(1,'Seat')
  INSERT INTO Table_B VALUES(2,'VW')
GO  

Sırada Update sorgularımız iki ayrı transaction olarak başlayak şekilde

Resim 4

BEGIN TRAN
  UPDATE Table_B SET Brand ='Seat Cupra' WHERE Id=1
  WAITFOR DELAY '00:00:10'
  UPDATE Table_A SET Brand ='Mercedes-Benz' WHERE Id=1
COMMIT TRAN

 BEGIN TRAN
  UPDATE Table_A SET Brand ='Mercedes Benz' WHERE Id=1
  WAITFOR DELAY '00:00:10'
  UPDATE Table_B SET Brand ='Cupra' WHERE Id=1
  COMMIT TRAN

Yeri gelmişken “EXEC sp_lock” stored prosedürü yardımıyla o an oluşan kilitlenmeleri inceleyebiliyoruz. Resim 5 de gördüğümüz üzere sorgularımız çalışmaya devam ederken SPID 62 ve 64 tarafında kilitlenmeler gerçekleşmiş.

Resim 5

Ve sonuç bir transaction kurban olarak seçilerek rollback gerçekleşti diğer transaction başarıyla tamamlandı.

Resim 6

Peki uygulama tarafında oluşabilecek Deadlockları nasıl izleyeceğiz. Burada Extended Events yardımımıza koşuyor. SSMS içerisinde  Management altında Extended Events > Sessions> New Session Wizard diyerek sihirbazı açıyoruz.

Resim 7

Oluşturacağımız sessiona bir isim veriyoruz ve bir sonraki adıma geçiyoruz.

Resim 8

Bir sonraki adımda bizden eventleri seçilmiş hazır şablonlardan birini mi yoksa kendimizin şablon kullanmadan mı devam edeceğini seçiyoruz. Burada ben alttaki seçeneği seçerek devam ediyorum

Resim 9

Arama yerine “deadlock” yazarak Resim 10 da gözüken eventleri seçiyorum ve devam ediyorum.

Resim 10

Sırada Capture Global Fields kısmında “sql_text” , “session_id” seçiyorum. Burada seçilerek bilgi verebilecek fazlasıyla metrik bulunuyor.

Resim 11

Bir sonraki aşamada Set Session Event Filters sekmesi bu sekme altında bir önceki adım olan “Capture Global Fields” bulunan alanlar ve başka alanlara göre filtreleme imkanı sunuyor.Örnek olarak sadece AdventureWorks veri tabanı olarak belirtebiliriz.

Resim 12

Son aşama olarak oluşturduğumuz bu Extened Eventimizin topladığı verileri bir dosyada saklayacak mıyız ona karar veriyoruz. Sadece anlık izlemek istiyorsak işaretli alanı doldurmadan devam ediyoruz. Eğer ki oluşan olayları kayıt etmek istiyorsak oluşan kayıtların hangi yola kayıt edileceğini ve oluşacak maksimum dosya boyutu gibi parametreleri ayarlıyoruz.

Resim 13

Son aşama olarak “Start the event session immediately after session creation” kutucuğunu işaretleyerek event oluşur oluşmaz başlamasını sağlıyoruz.

Resim 14

Oluşturduğumuz eventin üzerine sağ tıklayarak durdurabilir , başlatabilir veya canlı gerçekleşen olayları inceleyebiliriz.

Resim 15

Eventler durdurmadığımız müddetçe arka planda oluşan olayları kayıt altına almaktadır.Oluşan kayıtları incelemek için oluşturduğumuz eventin altında bulunan package dosyasını View Target Data diyerek açtığımızda kayıt altına alınan olayları inceleyebiliriz.

Resim 16

Extended Eventimiz oluşturduktan sonra yukarda ki örneğimize geri dönelim, iki transactionı tekrar başlatıyoruz dead lock oluşuyor ve bir tanesi rollback yapıldı

Resim 17

Eventimizi Resim 16 da ki gibi View Target Data diyerek incelediğimizde eventimiz oluştururken seçtiğimiz eventler, alanların aldıkları değerleri görebiliyoruz. Örnek olarak SQL Text, client app name, host name vb gibi

Resim 18

Seçtiğimiz eventlerden bir diğeri olan xml_deadlock_report  eventi altında Deadlock sekmesi altında oluşan deadlock hakkında grafik  üzerinden bilgi alabiliyoruz.

Resim 19

Oluşan grafiği incelecek olursak sol taraftaki üzeri çizilen transaction victim seçilen transactiondır. Üzerine geldiğimizde bu transactiona ait sql textini görebiliyoruz. Hangi transactionın hangi kaynağa erişmek istediği, kilit türü gibi birçok detaylı bilgiye ulaşabiliyoruz.

Bu yazımda sizlere veri tabanında dead lock kavramını bir örnekle anlatmaya çalıştım. Oluşan dead lockların extended event yardımıyla nasıl tespit edilebileceğinden bahsettim.

Faydalı olması dileğiyle