SQL Server 2005 : Lock, Blocking, Deadlock Sorunları ve Giderilmesi

SQL Server 2005 : Lock, Blocking, Deadlock Sorunları ve Giderilmesi Anahtar Kelimeler: Transaction Concurency Locking Blocking Long Running Query Deadlock SQL Profiler

Bu makalede aşağıdaki konulardan bahsedilecektir: Transaction temelleri Transaction Isolation ve Locking Locking neden Deadlock ve Blocking e sebep olur? Blocking ve Deadlock ın analizi Makalede kullanılan teknolojiler:

MS SQL Server 2005

Makale ile ilgili kaynaklar:

Blocki ng – Deaclock Trace Templates

GİRİŞ

Data, günümüz bilişim dünyasının en önemli parçasıdır. Ancak durağan datanında bilişim sektörüne kattığı bir değer yoktur. Ne zamanki elimizdeki veriler (data) durağanlıktan çıkıp güvenli bir şekilde korunur hale gelir, ardından elimizdeki bu verilerden anlamlı sonuçlar kümesi çıkarmaya başlarız, iste o zaman data, paha biçilemez bir hal alır.

MS SQL Server 2005, bu amaçla iş dünyasına hizmet etmektedir. (sql 2005 özellikleri)

Veritabanının sahip oldugu kaynaklar sınırlıdır. Veri dosyaları ve memory veritabanın yönettiği paylaşılan kaynaklardan (shared resource) birkaçıdır. Veritabanlarındaki en temel ihtiyaç ise bu “shared resource” ların kullanıcı isteklerine göre en iyi şekilde yönetilip, isteğin en hızlı biçimde, diğer istek yapan kullanıcıları en az etkileyecek ve onları bozmayacak  şekilde çalıştırılmasıdır.

Bu makalede veritabanının “shared resource” ları nasıl yönettiğine, “multi user” isteklerine nasıl öncelik ve cevap verdiğine değineceğiz. Ardından çıkabilecek  Deadlock ve Blocking sorunlarından, bu sorunların nasıl tespit edilip çözüldüğünden bahsedeceğiz.

TRANSACTION ve LOCKING

Transaction bir veritabanı üzerine yapılan her türlü sorgulama ve değiştirme işlemidir. Veritabanı üzerinde yaptığımız her bir,

SELECT ... ,

INSERT ... ,

UPDATE ... ,

DELETE ...

işlemi transaction kavramı içerisine girer.

Transaction ların veritabanında sağlıklı şekilde çalışmasına devam edebilmesi için temelde bazı özelliklere sahip olması gerekir. Bu özellikler Atomicity, Consistency, Isolation, Durability kelimelerinin başharflerinden oluşan ACID ile temsil edilir.

Transacion Özellikleri

A

tomicity

C

onsistency

I

solation

D

Urability

Atomicity (Birim iş): Atomicity transaction ın en temel özelliğidir. Atomicity sayesinde transaction içindeki işler bir bütün olarak ele alınır. İşimiz ya tamamen biter (başarılı transaction), yada (her hangi bir sebeple iş aksadığında) yapılan bütün işin başa dönmesini (başarısız transaction) sağlar.

Consistency: Veritabanında daha önceden belirlenen kurallar kümesinin transaction sırasında kontrol edilmesidir. Veritabanının transaction bittikten sonra da kararlı bir yapıda bırakılmasını sağlar Sadece sayı girilmesi gereken bir alana karakter girmeye kalktığımızda bu durum transaction ın başarısız olmasına sebep olur. Bu sayede veritabanı her zaman istenen tipte veriyi kendi üzerinde saklar.

Durability: Veritabanının üzerinde bulunduğu ortamdaki (network, disk, yada güç birimi) problemlerden dolayı oluşabilecek bozuklukları engellemeye calışır. Böyle bir bozulma  karşısında veritabanı o an üzerinde işlemekte olan transaction ları bitmemiş olarak işaretler, sistem tekrar açıldığında ise yarım kalan transaction kaldığı yerden devam eder.

Isolation: Veritabanları aynı anda birden cok kullanıcıya hizmet edebilmek üzere tasarlanmıştır. Aynı anda bir çok kullanıcı sisteme bağlanıp bir hesaplama yapmak yada bir rapor almak isteyebilir. Kullanıcı açısından bu durum hic de karmaşık yada zor değildir. Çünkü sistemin sadece kendisine hizmet ettiğini sanmaktadır. Oysaki veritabanı tarafında durum pekde basit değildir. Yapılması gereken, aynı anda gelen kullanıcı isteklerini birbirlerini bozmadan, bekleme sürelerini en aza indirererek sonucu kullanıcılara iletmektir. Bu işi başarabilmek için Isolation (mantıksal olarak her bir kullanıcının o anki veritabanın birebir kopyası ile çalışması anlamına gelir) mekanizmasını kullanılır. Veritabanı gelen kullanıcı isteklerini birbirlerinden izole edebilmek için ise Locking i kullanır.

Locking in olmadığı sistemlerde 3 temel problem ortaya çıkmaktadır.

Dirty Read: Bir transaction sonlanmadan diğer bir transaction ın bir önceki transaction ın yaptığı değişiklikleri görmesidir.

Zaman

Transaction 1

Transaction 2

Yorum 1

Yorum 2

t1

BEGIN TRAN

 

574 numaralı Customer kaydının NAME değeri ‘AHMET’ tir.

 

t2

 

BEGIN TRAN

 

 

t3

UPDATE CUSTOMERS

SET NAME = ‘KEMAL’

WHERE

CUSTOMERID = 574

 

 

 

t4

 

SELECT NAME

FROM CUSTOMERS WHERE

CUSTOMERID = 574

 

t4 anının sonunda ‘KEMAL’ bilgisi görülür.

t5

ROLLBACK TRAN

COMMIT TRAN

 

 

Non-Repeatable Read: Bir transaction sonlanmadan diğer bir transaction ın kendi içinde yaptığı artarda okuma işlemlerinin bir önceki transaction ın yaptığı değişikliklerden dolayı etkilenip farklı sonuçlar üretmesidir.

Zaman

Transaction 1

Transaction 2

Yorum 1

Yorum 2

t1

BEGIN TRAN

 

 

 

t2

 

BEGIN TRAN

 

 

t3

 

SELECT NAME

FROM CUSTOMERS WHERE

CUSTOMERID = 574

 

t3 anının sonunda ‘AHMET’ bilgisi görülür.

t4

UPDATE CUSTOMERS

SET NAME = ‘KEMAL’

WHERE

CUSTOMERID = 574

-- diğer işlemler

 

 

 

t5

 

SELECT NAME

FROM CUSTOMERS WHERE

CUSTOMERID = 574

 

t5 anının sonunda ‘KEMAL’ bilgisi görülür.

t6

ROLLBACK TRAN

COMMIT TRAN

 

 

Phantom: Bir transaction sonlanmadan diğer bir transaction ın kendi içinde yaptığı artarda okuma işlemlerinin bir önceki transaction ın yaptığı Insert/Delete işlemlerinden dolayı etkilenip farklı sonuçlar üretmesidir.

Zaman

Transaction 1

Transaction 2

Yorum 1

Yorum 2

t1

BEGIN TRAN

 

574 numaralı müsteri nin ülkesi Türkiye’dir.

 

t2

 

BEGIN TRAN

 

 

t3

 

SELECT COUNT(NAME)

FROM CUSTOMERS WHERE

COUNTRY = ‘TR’

 

t3 anının sonunda 150 bilgisi görülür.

t4

DELETE CUSTOMERS

WHERE

CUSTOMERID = 574

-- diğer işlemler

 

 

 

t5

 

SELECT COUNT(NAME)

FROM CUSTOMERS WHERE

COUNTRY = ‘TR’

 

t5 anının sonunda 149 bilgisi görülür.

t6

ROLLBACK TRAN

COMMIT TRAN

 

 

Yukarıda belirtilen Locking problemleri ANSI ninde belirlediği 4 adet standart Transaction Isolation seviyesi ile çözülmüştür. MS SQL Server da ANSI isolation seviyelerini desteklemektedir. Hangi isolation seviyesinin hangi problemleri çözdüğü aşağıda belirtlimiştir.

Isolation Level

Dirty Read

Non-Repeatable Read

Phantom

Read uncommitted

Var

Var

Var

Read committed

Yok

Var

Var

Repeatable read

Yok

Yok

Var

Serializable

Yok

Yok

Yok

MS SQL Server da default olarak (aksi belirtilmedikçe) bütün transactionlar READ COMMITTED olarak işaretlenir.

MS SQL Server da transaction isolation seviyesi değiştirmek için aşağıdaki komut kullanılır.

SET TRANSACTION ISOLATION LEVEL [ READ UNCOMMITTED  /

                                         READ UNCOMMITTED  /

 REPEATABLE READ   /

 SERIALIZABLE ]

Eğer bütün transaction boyunca aynı Locking seviyesinin geçerli olmasını istemezek, query ler için TABLE LOCK HINTLER uygulanıp, READ COMMITTED seviyesine sahip bir transaction ın içerisinde SERIALIZABLE seviyesine sahip bir sorgulama yapılabilir.

SET TRANSACTION ISOLATION LEVEL  READ COMMITTED

BEGIN TRAN

SELECT NAME FROM CUSTOMERS WITH (SERIALIZABLE)

COMMIT TRAN

Locking mekanizması veritabanın yönetiminde gerçekleşmektedir. Shared resource lar üzerinde duran bu Lock lar doğal olarak diğer transactionlar üzerinde bir etki bırakmaktadır. Aynı anda gelen 2 transaction, aynı veriyi güncellemek istediğinde (ilk işi yapan transaction shared resource üzerine Lock koyacağından) biri diğerinin işinin bitmesini beklemek durumundadır. Kısaca,

Transaction içerisinde yaptığımız işler (Isolation dan ötürü) shared resource ların Lock lanmasına, Lock lanan shared resource lar ise aynı anda gelen diğer isteklerin (aynı shared resource a gelen istekler) Block lanmasına, Blocking kısır bir döngüye girdiyse Deadlock a sebep olur.                      

BLOCKING (long running)

Veritabanı Isolation işini gerçekleştirebilmek için Locking i kullanır. Transaction ımız ne kadar uzun zaman alırsa, veritabanının Lock lı tuttuğu kaynağı (table, row, index vb.) bırakmasıda o oranda gecikir. Bu gecikmeden yola çıkarak, diğer transaction ların, mevcut işin bitmesini beklemelerine Blocking denir.

Yukarıda da belirtildiği gibi Blocking, Locking den dolayı oluşan bir sonuçtur.

Kendi örneğimiz ile bir Blocking oluşturalım.

AdventureWork sample database imize baglanıp 2 adet query penceresi açalım. Ardından yukardaki resimde belirtildiği gibi STEP leri sırasına göre çalıştıralım. 4.STEP i çalıştırdıktan sonra işlemimiz, 3.STEP te oluşan Lock yüzünden bekleme durumuna geçer. Sonuçta, bloklayan (blocking) sol penceredeki query, bloklanan (blocked by) da sağ penceredeki query dir.

Sistemde her an blocking olması muhtemeldir. Asıl kritik problem, Blocking süresinin makul sürelerin çok çok üstüne çıkmasıdır. Long running query ler işte bu sebepten ötürü oluşurlar.

Veritabanında Blocking ve Long Running Query ler, izlenmesi ve çözümlenmesi karmaşık durumlardır. Cevap bulunması gereken sorular şunlardır:

Hangi process, hangi process i yada process leri bloklar Blocking esnasında aktif process lerin çalıştırmak istediği query nedir, (hangi query yüzünden Blocking oluşmuştur) Belli bir zaman limitini aşan query lerin tespiti, Bu zaman limitinin sebebinin anlaşılması, (Blocking den ötürümü yoksa gerçekten zaman alıcak bir iş mi)

Cevaplarımızı bir analiz tool u olan SQL Profiler ile alacağız. Sql Profiler üzerindeki Trace Template lerinden faydalanacağız.

SQL Profiler dan yeni bir Trace oluştururuz. Ardından Trace özelliklerini belirlemek için diğer tab a geçeriz. “Blocked Process Report” isimli event e bağlanırız. Böylece Blocked Process lerimiz XML rapor halinde log lanabilir hale gelir.

Blocking Log süresini belirlemek için (kaç saniye bloklu kalırsa Profiler tarafından Log a yazılsın) sp_configure procedure ünden faydalanılır.

sp_configure ‘blocked process threshold’, ‘10000’goreconfigurego

Komutunu çalıştırırsak 10 saniyeyi geçen Blocking leri Log lamasını sağlarız. Aşağıdaki Log 10 sn boyunca kendisini engelleyen işin bitmesini bekleyen diğer bir iş tarafından Trace e yazılmıştır.

   <process status="suspended" spid="142”>      EXEC STOCK_MOVEMENT 1000173888,&apos;2&apos;,&apos;41&apos;,1002999414                  <process status="running" spid="119>   EXEC STOCK_SERIAL_INSERT &apos;2&apos;,&apos;8&apos;,999221,2200470415,&apos;3138500548&apos;,44855,1261, 3,12,81,81,101064,NULL,NULL,NULL,70,91,103         

Yukarıdaki log u incelersek, 142 numaralı Process in 119 numaralı Process tarafından blokelendiğini ve bu probleme sebep olan input buffer (query) ları görürüz.

Bu trace leri inceleyerek, kendi “İş Kuralları” mız çerçevesinde problem in boyutunu ölçüp ona göre çözümler getirebiliriz.

DEADLOCK

Blocking bir önceki konuda da bahsedildiği gibi uzun süren, fakat bir şekilde işini tamamlayan problem türümüzdü. Deadlock ise, hiç bir şekilde işini tamamlayamayan, o ana kadar yaptığı işini de geri alan problem türüdür.

Aşağıdaki durumda Deadlock oluşur:

Zaman

Transaction 1

Transaction 2

Yorum 1

Yorum 2

t1

BEGIN TRAN

BEGIN TRAN

 

 

t2

UPDATE CUSTOMERS

SET NAME = ‘UPDATED_NAME’

WHERE CUSTOMERID = 574

UPDATE ORDERS

SET COMMENT = ‘UPDATED_COMMENT’

WHERE ORDERID = 48

t2 anının sonunda işimizde hiç bir problem yoktur

t2 anının sonunda işimizde hiç bir problem yoktur

t3

UPDATE ORDERS

SET COMMENT = ‘UPDATED_COMMENT’

WHERE ORDERID = 48

UPDATE CUSTOMERS

SET NAME = ‘UPDATED_NAME’

WHERE CUSTOMERID = 574

t3 anının sonunda “Transaction 1” “Transaction 2” nin ORDERS tablosu üzerindeki Lock ından ötürü sonsuz bir bekleme sürecine (DEADLOCK) girer.

t3 anının sonunda “Transaction 2” “Transaction 1” in CUSTOMERS tablosu üzerindeki Lock ından ötürü sonsuz bir bekleme sürecine (DEADLOCK) girer.

t4

COMMIT TRAN

COMMIT TRAN

 

 

Yukarıdaki zaman tablosunda da açıkça görüldüğü gibi hiç bir şekilde t4 anına ulaşılamaz. Çünkü Transaction lar birbirlerini bekler durumdadırlar. Sonuçta her iki transaction da işini tamamlayamayacaktır.

İşte bu durumu engelleyebilmek, en azında bir tanesinin işine devam edebilmesi için veritabanı bir karar vermek durumundadır. Veritabanı kendisi için Rollback masrafı en az olan Transaction u kurban (victim) olarak seçer ve o transaction ın başarısız bir şekilde sonlanmasına karar verir. Böylece diğer bekleyen Transaction yoluna devam edebilecektir.

Deadlock lar da analizi ve tespiti son derece zor ve yorucu bir konudur. Biz kendimize yine en etkili tool olan Profiler ı seçtik.

SQL Profiler dan yeni bir Trace oluştururuz. Ardından Trace özelliklerini belirlemek için diğer tab a geçeriz. “Deadlock Graph” isimli event e bağlanırız. “Events Extra Settings“ tabından ise, oluşan bu deadlock trace lerini ayrı bir XML dosyaya kaydetmesini isteyebiliriz. Bu trace ler sayesinde deadlock larımız tıpkı blocking de olduğu gibi, hangi process ler tarafından hangi query leri execute ederken vb. ayrıntı bilgilere ulaşırız.

Deadlock ları ve Blocking (long running) leri tümüyle yok etme gibi bir şansımız yoktur. Multi-user bir ortamda bu problemler her zaman olacaktır. Bu problemlerin sayısını minimuma indirmek yada makul seviyelerde tutmak bizim asıl çabamız olmalıdır.

Deadlock ları minimuma indirgemek için:

Nested trigger lardan mümkün olduğunca kaçınmalıyız. (Kontrolü imkansız bir hal alabilir) Mümkün olduğu kadar bütün server object lerimizde (function, procedure vb.) kaynaklarımızı aynı mantıksal sırada kullanmalıyız.

                        Procedure A                         Procedure B

                                                Tablo1                                   Tablo1

                & nbsp;                &nbs p;              Tablo2                                  Tablo2

Deadlock oluştuğunda sys.messages tablosundaki 1205 numaralı error_id si ile hata fırlatılır. Bu hatanın olması durumunda aynı istek (kullanıcıya hata bildirimi yapmaksızın) tekrardan veritabanına iletilip 2. bir deneme yapılmalıdır. 2. denememizin sonunda başarı ihtimalimiz çok yüksektir.

SONUÇ

Locking, veritabanının kendini koruması ve aynı bütünlük içerisinde çalışmasına devam edebilmesi için kullandığı en önemli silahıdır. Multi-user bir ortamda Locking ler Blocking lere ve Deadlock lara sebep olur. Blocking ve Deadlock sorunlarının tümüyle yok edilmesinden bahsedemeyiz. Sadece uygun trace parametreleri ile sistemi inceleyip (Profiler ile), bu sorunların kaynağına ulaşıp, kendi iş kurallarımız çerçevesinde bu sorunlara çözümler getirebiliriz.

Blocki ng – Deaclock Trace Template leri Profiler a import edip sisteminizi takip edebilirsiniz.

Döküman Arama

Başlık :

Kapat