Veri ile uğraşan herkes az ya da çok bir veri tabanı üzerinde çok sık kullanılan CRUD ( Create – Read – Update – Delete ) işlemleri yapmıştır. Bizler veri tabanı üzerinde bir işlem yaptığımızda, tabloya yeni bir kayıt eklemek, silmek veya güncellemek olsun. Bu yaptığımız işlemler için SQL Server bir günlük dosyasına ( Transaction Log ) yapılan işlemleri kayıt etmektedir. Bilindiği gibi Transaction Log dosyası, veri tabanı için en önemli dosya olup, her SQL Server veri tabanı için en az bir tane olmak zorundadır.
Transaction Log ( Günlük ) dosyası SQL Server veri tabanının tutarlılığını denetlediği gibi hiç istenmeyen bir durum olan data ( .mdf ) dosyasında herhangi bir sorun olduğunda, verilerin kurtarılması için kullanabileceğimiz en önemli dosyamızdır. Transaction Log dosyasının veri kurtarmak için nasıl kullanıldığını “SQL Server Felaketten Kurtarma – MDF Dosyasına Erişilemiyor!” makale üzerinden inceleyebilirsiniz.
Transaction Log (Günlük) dosyası hakkında biraz bilgi verdikten sonra makale konumuza gelelim. Herhangi bir uygulama üzerinden veya SQL Server Management Studio üzerinde olsun veri tabanımız üzerinde bir tabloya ve kayıt eklediğimizde, kaydı sildiğimiz veya güncellediğimizde data ( .mdf ) dosyamıza yapılan işlem yansıtılmadan önce Transaction Log ( Günlük ) dosyasına bu işlemler yazılır ve daha sonra asıl dataların tutulduğu dosyamıza ( .mdf ) yansıtılır. Veri tabanımız üzerinde yapılan Transaction Log ‘ların nasıl tutulacağını Recovery Model seçeneği ile belirleriz. SQL Server üzerinde bir veri tabanı için üç ayrı Recovery Model seçeneği mevcuttur. Bu seçenekler de ayrı bir makale konusu olup kısaca bahsetmek gerekirse;
- SIMPLE Recovery Model: Geriye dönük işlem kayıtları saklanmaz. Checkpoint işleminden sonra bu modelde Transaction Log dosyasından işlem kayıtları silinir. Simple Recovery Model tercih edildiğinde Transaction Log yedeği alınamaz. Özel bir durum yoksa bu seçeneğin kullanılması tercih edilmez.
- BULK LOGGED Recovery Model: Bulk ( Toplu ) yapılan işlemler dışında tüm işlemler bu model seçeneğinde Transaction Log dosyasına kaydedilir. Bulk Logged Recovery Model tercih edildiğinde yapılan toplu işlemler tek bir kayıt olarak Transaction Log dosyasına kaydedilir. Genellikle yoğun kullanılan sistemlerde bu model sürekli olarak tercih edilmeyip, Bulk ( Toplu ) bir işlem yapılmadan önce bu model veri tabanı için seçilip işlemler bittikten sonra tekrar değiştirilir. Bulk ( Toplu ) işlemlere örnek : “Bulk Insert, Select INTO, Index oluşturma ve bakım vb.”
- FULL Recovery Model: En çok tercih edilen bu model seçeneğinde, SQL Server veri tabanı üzerinde yapılan tüm işlemler Transaction Log ( Günlük ) dosyasına kaydedilir. FULL Recovery Model tercih edildiğinde yapılan tüm işlemler Transaction Log üzerine kaydedildiği için en güvenilir seçenektir. Full Recovery Model tercih edildiğinde Transaction Log ( .ldf ) dosyasında oluşan işlem kayıtlarını silmek için Log Backup ( Günlük Yedeği ) alabilir veya Log Shrink ( Günlük Küçültme ) işlemi yapabilirsiniz.
Kısaca üzerinden geçmiş olduğumuz Recovery Model seçenekleri daha detaylı bir konu olup başka bir makalede detaylıca incelenebilir.
Peki, SQL Server üzerinde yapılan CRUD işlemleri Transaction Log dosyasına nasıl kaydediliyor? Bu soruya cevap bulabilmek için Transaction Log dosyamızdaki kayıtları okuyup analiz etmeye başlayalım. Makale konumuz için SQL Server üzerinde yeni bir veri tabanı oluşturuyorum.
Resim-1
MsHowToDemoDB adında bir veri tabanı oluşturdum. Oluşturmuş olduğum veri tabanımın Recovery Model türünü aşağıdaki T-Sql sorgusu ile öğreniyorum.
Resim-2
Oluşturmuş olduğum veri tabanı varsayılan olarak en güvenilir seçenek olan FULL Recovery Model seçili olarak gelmiş oldu. Yukarıda bahsettiğimiz gibi bu seçenekte bütün işlemler Transaction Log dosyasına yazılacak olup, dosya içeriği Log Backup ( Günlük Yedeği ) veya Log Shrink ( Günlük Küçültme ) işlemleriyle temizlenmektedir. Ben örneğimde Log dosyasını sık sık temizleyeceğim için SIMPLE Recovery Model seçeneğini kullanacağım. Veri tabanımın Recovey Model tercihini aşağıdaki sorgu yardımıyla değiştiriyorum.
Resim-3
Transaction Log dosyasını daha rahat inceleyebilmemiz için otomatik oluşturulan ve güncellenen istatistikleri de aşağıdaki sorgu yardımıyla kapatıyorum.
Resim-4
SQL Server üzerinde veri tabanımızı oluşturduk. Veri tabanımızın Recovery Model türünü SIMPLE olarak değiştirdik ve daha az işlem kaydı oluşması için veri tabanı üzerinde varsayılan olarak açık gelen, otomatik oluşturulan ve güncellenen istatistikleri kapattık. Şimdi örneğimizde kullanmak için bir tablo oluşturuyoruz.
Resim-5
Örneklerimde kullanmak için üç kolonlu basit bir tablo oluşturuyorum.
Resim-6
Oluşturmuş olduğum tabloya kayıt eklemeden daha temiz bir günlük dosyasını incelemek için önce “CHECKPOINT” komutu ile Transaction Log (Günlük) dosyasındaki mevcut işlem kayıtlarını siliyorum.
Resim-7
Oluşturmuş olduğum “LogDemoTable” isimli tabloya bir adet kayıt ekliyorum. Kaydımızı oluşturduktan sonra Transaction Log kayıtlarını okumak için SQL Server’ da tanımlı olan “fn_dblog” tablo değer döndüren fonksiyonu kullanacağım.
Kullanımı çok kolay olan fn_dblog fonksiyonu iki parametre alır. Fonksiyonun aldığı başlangıç ve bitiş LSN ID’ sini verebilir veya tüm kayıtları okumak için bu parametreleri NULL geçebiliriz. Transaction Log ( Günlük ) dosyasında bulunan her bir kayıt için UNIQUE ( Benzersiz ) bir değer verilir. Verilen bu değere LSN ( Log Sequence Number ) denir.
Tablomuza bir tane kayıt ekledikten sonra oluşan günlük kayıtlarımızı sorgulayalım.
Resim-8
Transaction Log ( Günlük ) kayıtlarımızı sorguladığımızda yukarıdaki gibi altı satır kayıt geliyor olacak. İlk üç kayıt bizim çalıştırmış olduğumuz “CHECKPOINT” komutuna ait olup ikinci üç kayıt yapmış olduğumuz INSERT işlemine aittir. Çok fazla kolon geldiği için kayıtları daha rahat okumak adına sorgumu aşağıdaki gibi değiştirip tekrar çalıştırıyorum.
Resim-9
Yukarıdaki sorgu çıktımızı incelediğimizde tablomuza eklemiş olduğumuz bir kayıt için Transaction Log dosyasında üç satır kayıt mevcut. Tabloya bir kayıt eklemek için SQL Server bizim için bir tane Transaction işlemi başlatıyor, ilgili Page’ e ( Sayfa ) verimizi yazdıktan sonra açmış olduğu Transaction işlemini bitiriyor. SQL Server, varsayılan olarak “AUTOCOMMIT” Transaction yapısına sahiptir. Yapmış olduğumuz her işlem için biz Transaction açmasak bile SQL Server otomatik olarak bizim için Transaction oluşturur. Yapmış olduğumuz Insert ( Veri Ekleme ) işleminde bir hata meydana gelmesi durumunda tüm işlemi geri almak için Transaction başlangıç noktasına dönülecektir.
SQL Server ‘da Transaction yapısı çok detaylı bir konu olup başka bir makalede bu Transaction yapısı detaylı olarak incelenecektir.
Bu adıma kadar özetlemek gerekirse;
- Yeni bir veri tabanı oluşturduk,
- Veri tabanımızın Recovery Model’ini SIMPLE olarak değiştirdik,
- Transaction Log ( Günlük ) dosyasında daha az kayıt oluşması için otomatik oluşturulan istatistikleri kapattık.
- Veri tabanımız üzerinde yeni bir tablo oluşturduk,
- Oluşan tüm Transaction kayıtlarını temizlemek için CHECKPOINT komutunu kullandık,
- Oluşturmuş olduğumuz tablomuza bir adet kayıt ekledik,
- Yapmış olduğumuz kayıt işleminin Transaction Log ( Günlük ) dosyasında nasıl satırlar oluşturduğunu incelemek için fn_dblog fonksiyonu ile dosyamızdaki verileri okuduk.
Şimdi tekrar Transaction Log kayıtlarımızı CHECKPOINT komutu ile temizleyelim. SQL Server yerine biz kendi Transaction ‘ımızı açıp bir kayıt ekleyelim.
Resim-10
Tablomuza Transaction açıp kaydımızı ekledikten sonra Transaction Log ( Günlük ) dosyamızı tekrar fn_dblog fonksiyonu ile okuyoruz. Bir önceki Insert işleminde üç satır olmasına rağmen kendi açmış olduğumuz Transaction ile Insert işleminde Transaction Log dosyasında iki satır olduğunu görüyoruz.
Transation Log dosyamızda yapmış olduğumuz Insert işlemine ait bir kayıt ( 5. Satır ) olmasına rağmen, SQL Server işlemi henüz tamamlamadı. SQL Server bizden işlemin başarılı olup olmadığı bilgisini de bekliyor. Bu aşamada tablomuzu sorgulamak istersek, SQL Server tablodaki kayıtları açık kalan Transaction işlemini tamamlanmadan okumamıza izin vermeyecek ve bekletileceğiz.
SQL Server’ da bir Transaction işlemi başlatıldığında, bu işlem başarılı olursa COMMIT komutu ile eğer başarısız bir işlem olursa tüm adımları geri almak için ROLLBACK komutu ile açılan Transaction’ u kapatmamız gerekmektedir.
Yapmış olduğumuz işlemi geri almak ve Transaction Log dosyasındaki davranışını incelemek için ROLLBACK komutunu çağırıyorum.
Resim-11
Çok yaygın olarak bilinenin aksine SQL Server, geri alınan işlem için Transaction Log ( Günlük ) dosyasındaki Insert satırını silmedi. Insert ( Veri Ekleme ) işlemini iptal etmek için Delete ( Veri Silme ) işlemini çağırdı. SQL Server, yapılan her işlem için Transaction Log ( Günlük ) dosyasına kayıt ekler, günlük dosyasından kayıt silme işlemi yapmaz ve iptal edilen işlemler için ters kayıtlar ekler.
SQL Server veri tabanımızda mevcut bir tablodaki kayıtları sildiğimizde Transaction Log ( Günlük ) dosyamıza nasıl kayıtlar eklendiğini de görelim. CHECKPOINT ile tekrar Transaction Log dosyasını temizliyorum. Tablodan bir kayıt silip tekrar fn_dblog fonksiyonu ile Transaction Log kayıtlarımı sorguluyorum.
Resim-12
Delete ( Veri Silme ) işleminden sonra günlük dosyasını incelediğimizde Insert ( Veri Ekleme ) sorgumuza benzer satırlar meydana geldi.
Son olarak Update ( Veri Güncelleme ) işleminde Transaction Log dosyasında nasıl bir kayıt meydana geliyor onu inceleyelim. Tekrar CHECKPOINT komutu ile günlük dosyamı temizliyor basit bir Update sorgusu çalıştırıyorum.
Resim-13
Yukarıdaki çıktımızı incelediğimizde Insert ve Delete komutları çalıştığında oluşan çıktı gibi üç satır işlem kaydı meydana geldi.
Buraya kadar Insert, Update ve Delete komutlarının Transaction Log dosyası üzerinde nasıl kayıtlar eklediğini incelemiş olduk.
Yaptığımız örneklerde biz HEAP tablo kullandık. Bilindiği gibi HEAP tablolarda veriler belirli bir düzene göre saklanmaz. Verilerin belirli bir düzen içerisinde diske yazılması için CLUSTERED INDEX kullanıyoruz. Kısaca bahsetmek gerekirse en temelde Index’ ler CLUSTERED ve NON-CLUSTERED olarak ikiye ayrılır. Kendi içlerinde de ayrımları mevcuttur. Clustered Index verinin fiziksel olarak diskte nasıl sıralanıp ve yazılacağını belirler. Non-Clustered Index ise verileri mantıksal bir sıralamaya tabi tutarak saklar. Index yapıları da çok detaylı konular olduğu için başka bir makale üzerinde incelenecektir.
Tablomuzdaki verileri sildikten sonra tablo üzerine Clustered Index tanımlayalım. Tablomdaki Col1 alanı üzerinde Index’ imi oluşturuyorum. Tablo üzerindeki Col1 sütunundaki verilere göre kayıtlar fiziksel sıralı olarak diske yazılacaktır.
Resim-14
Index’ imizi tanımladıktan sonra tablomuza bir tane de kayıt ekliyorum. Transaction Log kayıtlarını temizlemek için tekrar CHECKPOINT komutunu çağırıyorum. Tabloma eklediğim kayıt için bir güncelleme sorgusu yazıp oluşan günlük kayıtlarını sorgulayalım.
Resim-15
Update işlemi sonrasında Transaction Log ( Günlük ) kayıtlarını sorguladığımızda farklı bir çıktı ile karşılaşmıyoruz. Daha önceki yaptığımız Update örneğinde olduğu gibi Modify Row operasyonu gerçekleşti.
Dikkat!
Çok yaygın bir şekilde yanlış bilinen bir gerçek ile karşı karşıya kaldık. Bir Update sorgusu çalıştığında SQL Server önce mevcut kaydı siler ve daha sonra yeni kaydı ekler? Peki, SQL Server niye bizim örneklerimizde mevcut kaydı silip yeni kayıt eklemedi?
Tam olarak yanlış bilinen kısım her UPDATE işleminde SQL Server mevcut kaydı silmez. Verinin tutulduğu Page üzerinden kaydı günceller. Ancak Update işleminde Index’te tanımlı olan bir kolon değeri güncellendiyse o zaman SQL Server veriyi sıralı bir şekilde diskte yazmak zorunda olduğu için mevcut kaydı sildikten sonra yeni kayıt ekler. Index’ lerde tanımlı bir kolon güncellendiği zaman DELETE – INSERT komutları sıralı bir şekilde çağırılır.
Şimdi de bu durumu incelemek için CHECKPOINT ile Transaction Log kayıtlarını temizledikten sonra Clustered Index tanımlı Col1 kolonu üzerinde Update işlemi gerçekleştirelim.
Resim-16
Update sonrasında tekrar Transaction Log kayıtlarımızı incelediğimizde SQL Server’ın Delete – Insert işlemi yaptığını görüyoruz. Aslında Update işlemi sonrası SQL Server’ ın arka planda Delete – Insert yaptığını söylemek hatalı bir söylem değil eksik bir söylemdir diyebiliriz. Özetle SQL Server üzerinde bir Update ( Veri Güncelleme ) işlemi yaptığımızda Index’ lerde tanımlı bir kolon verisi güncelleniyorsa SQL Server önce mevcut kaydı siler değiştirilen veriyi yeni kayıt olarak ekler. Index üzerinde tanımlı olmayan bir kolon verisi değiştirildiğinde mevcut kaydı silme işlemi yapmadan güncellemektedir.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
TAGs: SQL Server, Transaction Log, Read Transaction Log, Analysis Transaction Log, Recovery Model,Transaction,CRUD,Clustered Index, Non-Clustered Index,fn_dblog,Heap Table
