SQL Server ve Transaction

SQL Transaction’ları da diğer işlemlerden farksız olarak atomik bir yapıya sahiptir.
Herhangi bir SQL sorgusu da aynı yapıda olduğu için ya tüm komut/sorgu başarıyla çalıştırılır ve sonucu alınır ya da veri tabanında hiçbir değişiklik yapılmadan işlem sonuçlandırılır. Dolayısı ile bunları Transaction yapıları içinde kullanmak mümkündür.
SQL Server’ da transaction’ın genel yapısı aşağıdaki gibidir.

    BEGIN TRANSACTION
    Transaction komutları
    COMMIT TRANSACTION / ROLLBACK


Aşağıdaki niteliklere sahip banka hesaplarının tutulduğu bir tablo oluşturalım.

Oluşturduğumuz bu tabloya iki adet kayıt ekleyelim. Dilerseniz daha fazla kayıtta ekleyebilirsiniz.

Oluşturduğumuz bu tablo üzerinde bir Stored Procedure (SP) tanımlayalım.

Bu SP dışarıdan havale yapanın hesap numarasını, havaleyi alacak olan kişinin hesap numarasını ve havale miktarını alacak ve Transaction başladıktan sonra havale işlemini gerçekleştirecektir.
Eğer havalede bir sorun çıkmaz ise işlem geçerli olacaktır. Bir sorun oluşur ise, her aşamada «ROLLBACK» komutu ile bütün işlemler geri alacaktır.

Oluşturduğumuz «havale» procedure’ü aşağıdaki gibidir.

 

SP’yi bir havale işlemi gerçekleştirerek test ederek sonuçlarına baktığımızda havale işleminin sorunsuz gerçekleştiği görülmektedir. 

 

 Bu transaction bloğu çalıştırıldığında hesap numaralarının herhangi birinin yanlış girilmesi durumunda dikkat edilirse herhangi bir uyarının verilmediği görülür.
Bu gibi durumlarda hata yakalama komutu olan "TRY-CATCH" yapısına ihtiyaç vardır. Bu yapı oluşacak hataları kullanıcının görmesini ve gerekli düzeltmeleri yapmasını sağlamaktadır.
TRY-CATCH bloğunu ekleyerek SP’yi yeniden düzenleyelim.

Yeni SP aşağıdaki gibi olacaktır.

Eğer herhangi bir hata meydana gelirse bir hata kodu üretilerek kullanıcı bilgilendirilecektir.
Bu hata elektrik kesintisi, donanımsal yada programlama ile ilgili bir hata olabilmektedir.

Transaction Bloğunun Çalışması

SQL Server üzerinde herhangi bir veride değişiklik yapıldığı zaman, ilgili sayfalar daha önce diskten hafızaya çağrılmamış ise öncelikle tampon belleğe alınır.
Daha sonra üstünde değişiklikler yapılır.
Yapılan değişiklikler diske hemen yansıtılmaz. Bu şekilde içeriği denenmiş ama henüz diske kaydedilmemiş sayfalara kirli sayfa adı verilmektedir.
Sayfada meydana gelen her değişiklik *.ldb uzantılı transaction log dosyalarına kaydedilir. Kirli sayfaların diske kaydedilmesi işlemine «arındırma» adı verilmektedir.
Arındırma işlemi gerçekleşmeden önce kirli sayfalardaki tüm değişiklikler log dosyasına yansıdığı için işlem gerçekleşirken ortaya çıkacak olan istenmeyen durumlardan sonra eski haline dönmek mümkün olmaktadır.
Eğer herhangi bir sorun ortaya çıkmaz ise arındırma işlemi düzgün olarak gerçekleşir ve veriler diske yazılır.

Transaction’lar içerdikleri tüm işlemleri başarıyla gerçekleştiren ya da veri tabanı üzerinde hiçbir değişiklik yapmayan atomik yapılardır.
Olası bir durumda kurtarma işlemlerinin gerçekleştirilebilmesi için her işlemin ne zaman başladığı, ne zaman bittiği ve başarılı bir şekilde tamamlanıp tamamlanmadığı bilgisinin sistemde tutulması gereklidir. Bu sebeple kurtarma yöneticisi (recovery manager) şu bilgileri saklamaktadır

BEGIN TRANSACTION: Transaction’ ın başlangıcını işaretler.
READ/WRITE: Transaction’ın içeriğinde gerçekleştirilen işlemlerin bilgisini içerir.
END TRANSACTION: Transaction’ın bitişini işaretler.
COMMIT: Transaction’ın başarıyla sonuçlandığı bilgisini içerir.
ROLLBACK/ABORT: Transaction’ın başarısız olduğu bilgisini içerir.


Bir transaction’ın çalışması aşağıdaki gibidir.

Transaction Kuralları

Bir veritabanı veriler üzerinde değişiklik yaparken dört kuralı sağlamak zorundadır. Sağlanması gereken bu dört kural aşağıdaki gibidir.
Bölünmezlik (Atomicity)
Bir transaction bloğu asla yarım kalamaz. Ya hepsi gerçekleşmiş sayılır yada hiçbir işlem gerçekleşmemiş kabul edilerek başa dönülür. Başka bir ifade ile transaction, daha küçük parçalara ayrılamayan işlem birimi olarak ele alınır.
Tutarlılık (Consistency)
Transaction veritabanının yapısını bozmadan işlem bloğunu terk etmelidir. Yani ara işlemler yapılırken işlem bloğunun etkisini veritabanına yansıtarak transaction’ı terk edemez.
Örneğin A kullanıcısının hesabından parayı azaltıp B kullanıcısına eklemeden işlemi sonlandırmaz. Veritabanının mutlaka tutarlı olması gerekmektedir.
İzolasyon (Isolation)
Farklı transaction’lar birbirinden farklı olarak ele alınmalıdırlar.
Her transaction için veritabanının yapısı ayrı ayrı korunmalıdır.
İlk transaction tarafından yapılan değişiklikler ikinci transaction’da görülmemelidir. Ancak işlem bittiğinde bir bütün olarak görülmelidirler.
Dayanıklılık (Durability)
Tamamlanmış bir transaction’nın hatalara karşı esnek olması gerekmektedir.
Elektrik kesilmesi, herhangi bir donanım arızası gibi nedenlerden dolayı yapılacak işlemlerin gerçekleşmesine engel olması gerekmektedir.
Bunun için gerçekleşmiş ve başarılı olmuş transaction’nın değişikliklerinin diske kalıcı olarak yansıtılması gerekmektedir.

Transaction - Temel Bilgiler

Önce transaction bloğu çalıştırılır. Transaction bloğunun çalıştırılması ile bütün işlemlerin bir bütünlük arz ettiği ve her an tamamının geçersiz sayılabileceği tanımlanmış olur.
Bütün işlemler transaction log’ ların da tutulur ve herhangi bir problemde bu log’ lar dikkate alınır. 
Transaction bloğu SQL Server tarafından otomatik olarak veya kullanıcı tarafından «BEGIN TRANSACTION» komutu ile başlatılabilir.
BEGIN TRANSACTION yerine BEGIN TRAN komutu da kullanılabilmektedir.


Transaction bloğunda yapılan her işlemin başarılı olup olmadığı, her biten işlem sonunda kontrol edilir. Eğer başarılı olunmadıysa yapılan işlem geri alınır.
Geri alma işlemi «ROLLBACK» komutu ile gerçekleştirilir.
Başarılı olunduysa bir sonraki işleme geçilir.
Bu işlemler kullanıcı tarafından ve SQL Server tarafından gerçekleştirilebilir.

Tüm işlemler tamamlandığında «COMMIT» işlemi ile tüm veriler yeni hali ile veritabanına kaydedilir.
Eğer işlem başarısız olursa «ROLLBACK» komutu ile bütün işlemler tekrar en başa alınır ve yapılacak olan işlem veritabanına yansıtılmaz.

Örnek: Transaction

Bir banka sistemini ele alarak bir havale işlemini gerçekleştirelim.
A kullanıcısı B kullanıcısına havale yaptığında;
A kullanıcısının hesabından havale edilecek olan miktar düşülür. Ardından B kullanıcısının hesabına bu havale miktarı eklenir ve havale işlemi gerçekleşmiş olur.
Ancak her zaman bu şartlar sağlanamamış olabilmektedir. Örneğin, A kullanıcısı para havale ettiğinde elektrikler kesilebilir yada programda bir hata meydana gelebilir. Bu gibi bir durumda neler olabilir?

Transaction Nedir?

Transaction veri tabanındaki verilere erişen ve çoğunlukla bu veriler üzerinde değişiklikler yapan bir program kesimidir.
Birçok kullanıcının eşzamanlı olarak işlem yaptığı büyük veri tabanı sistemlerinde daha çok kullanılmaktadır.
Bir işlem büyük bir bütünün parçası olabilir. Bu işlemlerden herhangi bir tanesinin gerçekleşmemesi bütün işlemleri anlamsız kılmaktadır.
Böyle bir durumda bütün işlemler tek bir işlem gibi ele alınmalıdır. Bu parçalanamaz işlemlerin oluşturduğu yeni tek işleme «transaction» adı verilmektedir.


Transaction yönetim sistemleri (transaction processing systems) banka, otel rezervasyon, süpermarket gibi birçok kullanıcının eşzamanlı olarak işlem yaptığı büyük veri tabanı sistemleri için geçerlidir. Bu sistemlerin yüksek performansla ve doğrulukla çalışması son derece önemlidir.
Bazı durumlarda yapılan bir işlem büyük bir bütünün parçasıdır. Bu işlemlerden herhangi bir tanesinin gerçekleşmemesi bütün işlemleri anlamsız kılmaktadır. Bu durumda bürün işlemler tek bir işlem gibi ele alınmalıdır. Bu parçalanamaz işlemlerin oluşturduğu yeni tek işleme transaction adı verilmektedir. Bir diğer değişle transaction, daha küçük parçacıklara ayrılamayan işlem bloklarıdır.

Tablo Döndüren Fonksiyonlar

Bu tür fonksiyonlar çalışma prensibi bakımından view’ lara benzerler fakat farklı olarak dışarıdan parametre de alabilirler. Tablo döndüren fonksiyonların genel kullanım biçimi şöyledir.

CREATE FUNCTION [sahip-ismi] fonksiyon_adı ( [@Parametre_adı veri_tipi, …..] )
RETUNS TABLE
AS
RETURN (SELECT cümlesi)

Yazılan fonksiyon incelendiğinde RETURNS TABLE ifadesi ile bir tablo döndürüleceği belirtilmiştir. Daha sonra RETURN ifadesinde yazılmış olan SELECT cümlesi ile kullanıcıya geri döndürülmesi gereken kayıtlar sorgulanarak gönderilmiştir. Fonksiyonun çalıştırılması şöyledir.


Örnek-2: Genellikle E-ticaret sitelerinde yer alan ve bir ürünü alanlar bunları da satın aldı şeklinde karşımıza çıkan ürünlerin listesi yer almaktadır. Aynı şekilde bir kullanıcı kodu kullanarak böyle bir liste oluşturacak bir fonksiyon yazalım. Bunun için gerekli olan tablolar şunlardır. 
tblUrun: Ürün bilgilerinin yer aldığı tablodur.
tblSiparisDetay: Sipariş detaylarının yer aldığı tablodur.
tblSiparis: Siparişi verilen ürünlerin tutulduğu tablodur.


Bu fonksiyon belli bir ürünü satın alan kullanıcıya ait kullanıcı kodunun bulunmasını sağlamaktadır. Buradan elde edilecek olan kullanıcı kodu yazılacak olan ikinci fonksiyonda kullanılacaktır.

Bu işlem iki aşamalıdır. Yani iki ayrı fonksiyon yazılmalıdır. Bunlardan birincisi şöyledir:
CREATE FUNCTION AlanlarinKodu(@urunKod int)
RETURNS TABLE
AS
RETURN SELECT kullaniciKod FROM tblSiparisDetay SD
INNER JOIN tblSiparis S ON S.faturaKod=SD.faturaKod
WHERE urunKod=@urunKod


Bu fonksiyon belli bir ürünü satın alan kullanıcıya ait kullanıcı kodunun bulunmasını sağlamaktadır. Buradan elde edilecek olan kullanıcı kodu yazılacak olan ikinci fonksiyonda kullanılacaktır.


Bu fonksiyonda ise ürün kodu girilerek bu ürünü alan kişinin başka hangi ürünleri aldığı listelenmektedir.
Burada tblUrun ve tblSiparisDetay tabloları kullanılarak siparişi verilen ürünler tespit edilmiştir.
Ardından tblSiparis tablosu ve elde edilen bu liste faturaKod değerleri kullanılarak birleştirilmiştir.
Elde edilen yeni liste kullanıcı kodlarına bakılarak bu listede hangi kullanıcının hangi ürünü satın aldığı tespit edilmiştir.


İkinci yani asıl fonksiyonumuz ise şöyle olacaktır.
CREATE FUNCTION BunuAlanBunlariAldi(@urunKod int)
RETURNS TABLE
AS
RETURN
SELECT U.urunKod,U.urunAd FROM tblUrun  U
JOIN tblSiparisDetay SD ON SD.urunKod=U.urunKod
JOIN tblSiparis S ON S.faturaKod=SD.faturaKod
JOIN AlanlarinKodu(@urunKod) FNA ON
FNA.kullaniciKod=S.kullaniciKod

Bu fonksiyonda ise ürün kodu girilerek bu ürünü alan kişinin başka hangi ürünleri aldığı listelenmektedir.
Burada tblUrun ve tblSiparisDetay tabloları kullanılarak siparişi verilen ürünler tespit edilmiştir.
Ardından tblSiparis tablosu ve elde edilen bu liste faturaKod değerleri kullanılarak birleştirilmiştir.
Elde edilen yeni liste kullanıcı kodlarına bakılarak bu listede hangi kullanıcının hangi ürünü satın aldığı tespit edilmiştir.

Skaler Kullanıcı Tanımlı Fonksiyonlar

Skaler fonksiyonlar, bir işlem neticesinde sadece tek bir değer döndüren fonksiyonlardır.
SQL içerisinde tanımlı birçok skaler fonksiyon vardır.
Matematiksel, karakter, tarih fonksiyonları vb.
Örneğin SELECT GETDATE (l) komutu FROM ifadesi olmadan kullanılabilir. Bu tür yani GETDATE ( )  şeklindeki fonksiyonlar skaler fonksiyonlardır.
Bazı durumlarda kullanıcılarda bu tür fonksiyonlara ihtiyaç duyabilirler. Örneğin yapılan alışveriş neticesinde sepette kaç ürün olduğunu döndüren fonksiyon skaler bir fonksiyondur.

Skaler fonksiyonların genel tanımı şöyledir:
CREATE FUNCTION [sahip-ismi] fonksiyon_adı
(
@parametre_adı veri_türü,
….
)
RETURNS skaler_veri_tipi
AS
BEGIN
SQL_ifadeleri
RETURN skaler_deger veya değişken
END

Örnek: Bir müşteri kodunu girerek müşterinin sepetinde kaç adet ürün olduğunu bulan bir fonksiyon yazalım.

Yazmış olduğumuz fonksiyona dikkat ettiğimizde RETUNS deyimi ile fonksiyonun integer tipte bir veri döndüreceği anlaşılmaktadır.
@Urun_sayisi olarak bir değişken tanımlandı.
Daha sonra SQL cümlesinde dışarıdan parametre olarak verilen kullanıcıKod bilgisi ile veritabanındaki kullaniciKod bilgisi karşılaştırılarak toplam ürün_sayısı hesaplandı ve @Urun_sayisi değişkenine aktarıldı.
RETURN komutu ile elde edilen bu ürün sayısı dışarıya tek bir değer olarak gönderildi.