SQL Server’da Isolation Level (İzolasyon Seviyesi), birden fazla işlemin (transaction) aynı anda çalışırken birbirini nasıl etkileyebileceğini belirleyen bir ayardır. İzolasyon seviyeleri, kirli okuma (dirty read), tekrar eden okuma hatası (non-repeatable read) ve hayalet okuma (phantom read) gibi durumları önlemek veya azaltmak için kullanılır. Isolation level yapısına geçmeden önce commit işlemini değinelim.
SQL Server’da COMMIT işlemi, bir transaction içinde yapılan değişiklikleri kalıcı hale getirir. Bir transaction içinde yapılan değişiklikler COMMIT edilene kadar geçici olarak veritabanında tutulur ve diğer oturumlar bu değişiklikleri göremez. Eğer COMMIT yapılmazsa ve işlem ROLLBACK edilirse, değişiklikler iptal edilir ve veritabanı işlem başlamadan önceki haline geri döner.
COMMIT İşleminin Mantığı
BEGIN TRANSACTION → Transaction başlatılır.
UPDATE / INSERT / DELETE gibi işlemler yapılır.
COMMIT → Değişiklikler kalıcı olur ve diğer oturumlar görebilir.
ROLLBACK (opsiyonel) → Eğer işlem iptal edilirse, değişiklikler geri alınır.
Daha detaylı açıklamak gerekirse:
SQL Server’da bir transaction COMMIT edildiğinde, aşağıdaki adımlarla veri kalıcı (persistent) hale gelir:
1. Transaction Log (WAL – Write Ahead Log)
• İlk olarak, yapılan tüm değişiklikler (INSERT, UPDATE, DELETE…) Transaction Log dosyasına (*.ldf) yazılır.
• Bu işlem, COMMIT olmadan önce bile gerçekleşir.
• Bu log, “ne zaman ne değişti” bilgisini tutar.
2. COMMIT ne yapar?
• COMMIT TRANSACTION komutu verildiğinde, SQL Server:
• Transaction Log’daki girişleri “kalıcı” olarak işaretler.
• Gerekirse Checkpoint mekanizması tetiklenir veya daha sonra çalışır.
• Bu işlem ile veri kaybına karşı koruma sağlanır (örneğin elektrik kesilirse transaction log üzerinden geri yükleme yapılabilir).
3. Veri Sayfalara (Data Pages) yazılır (Buffer Pool üzerinden)
• COMMIT işleminden sonra, değişen veri hala bellekte (buffer pool) tutulabilir.
• SQL Server bir süre sonra bu veriyi veritabanı veri dosyasına (*.mdf) yazar.
• Bu işlem immediate (anlık) olmak zorunda değildir. Çünkü transaction log zaten güvenliğe alınmıştır.
Özetle:
Aşama Nereye Yazılır? Ne Zaman?
1 Transaction Log (*.ldf) Hemen (COMMIT’ten önce bile)
2 Log kalıcı hale gelir COMMIT sırasında
3 Data file (*.mdf) Checkpoint ile veya sonra
Ne Zamana Kadar Kirli Veri Olarak Kalır
COMMIT edilene kadar transaction içindeki değişiklikler kirli veri olarak kalır.
Başka oturumlar bu veriyi sadece READ UNCOMMITTED seviyesinde okuyabilir.
COMMIT yapıldığında, tüm oturumlar veriyi görebilir.
ROLLBACK yapılırsa, değişiklikler tamamen iptal edilir ve eski veri korunur.
1. Isolation Level Çeşitleri
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
| READ UNCOMMITTED | Var | Var | Var |
| READ COMMITTED | Yok | Var | Var |
| REPEATABLE READ | Yok | Yok | Var |
| SERIALIZABLE | Yok | Yok | Yok |
| SNAPSHOT | Yok | Yok | Yok |
Dirty Read: Bir transaction, başka bir transaction tarafından henüz commit edilmemiş verileri okursa kirli okuma olur. Veri rollback olursa transaction tarafından okunan veri geçerli olmayan veri olacaktır. Bu yüzden dirty read olarak adlandırılır.
Non-Repeatable Read: Aynı transaction içinde bir veri iki kez okunursa ve arada başka bir transaction o veriyi değiştirirse tekrar eden okuma hatası oluşur.
Phantom Read: Bir transaction belirli bir koşula göre veri okuduğunda, başka bir transaction yeni bir kayıt ekleyip commit ederse hayalet okuma olur.
Öncelikle, örnek verilerle bir tablo oluşturalım:
CREATE TABLE Musteriler (
MusteriID INT PRIMARY KEY IDENTITY(1,1),
Ad VARCHAR(50),
Bakiye DECIMAL(10,2)
);
INSERT INTO Musteriler (Ad, Bakiye) VALUES
('Ahmet', 1000),
('Mehmet', 2000),
('Yunus', 3000);
Şimdi örnekler üzerinden Isolation Level seviyelerini inceleyelim:
A. READ UNCOMMITTED (Kirli Okuma)
Başka bir işlem tarafından commit edilmemiş verileri okumaya izin verir.
Senaryo:
Transaction 1: Ahmet’in bakiyesini güncelliyoruz fakat henüz commit yapmıyoruz.
Transaction 2: Aynı anda Ahmet’in bakiyesini okuyoruz.
Transaction 1: Ahmet’in bakiyesini güncelle ama commit yapma
BEGIN TRANSACTION
UPDATE Musteriler SET Bakiye = 5000 WHERE MusteriID = 1;
-- COMMIT yapılmadı!Transaction 2: Başka bir oturumdan okuma işlemi yapıyoruz. Read uncommitted şeklinde yapmazsak kullanıcı veriye ulaşamaz. Transaction 1 ifadesinin commit edilmesi gerekmektedir. Çünkü sql server default olarak Read committed modunda çalışıyor. Yukarıda update yaptığımız işlem ilgili satır üzerinde exclusive lock oluşturur select çektiğimiz sorgu shared lock alır bu iki lock çeşidi uyumsuz olduğu için select sorgumuz sonuç döndürmez. Aşağıdaki resimde dikkat ederseniz sonuç dönmedi. Select sorgumuza with(nolock) ifadesi yerleştirilirse kirli okuma yapmaktadır. Bu ifade read uncommitted ifadesine karşılık gelmektedir.
Yukarıda resimde dikkat ederseniz kirli okuma yapmış oldu. Çünkü henüz commit edilmemiş. Aynı zamanda kullanıcı tekrardan select çekmesi halinde kullanıcı farklı bir değer görebilir. Buda non repeatable read ve phantom read okuma oluşmasına sebep olmaktadır.
Kaldığımız yerden devam edelim: With(nolock) ifadesi kullanılmayıp Set transaction isolation… komutu ile de işlemlerimizi gerçekleştirelim. Çünkü ikisi aynı ifadeye denk gelmektedir.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Musteriler WHERE MusteriID = 1;
Herhangi bir sebepten dolayı Transaction 1 işlemi rollback olursa Son kullanıcı işlemin güncellenmediğini görmektedir. Normal select sorgularımızda Read Committed olarak çalışmaktadır. Burada yapılan işlemlerde sanki veri commit edilmeden önceki durumu anlamaya çalışıyoruz.
BEGIN TRANSACTION
UPDATE Musteriler SET Bakiye = 5000 WHERE MusteriID = 1;
-- COMMIT yapılmadı!
ROLLBACK
Sonuç:
Transaction 2 read uncommitted ısolation level veya with(nolock) ile commit edilmemiş yeni bakiye olan 5000’i görebilir! Ancak, Transaction 1 rollback yaparsa, Transaction 2 yanlış veri okumuş olur. Yukarıda aynı yapının olduğunu bahsetmiştik.
BEGIN TRANSACTION
UPDATE Musteriler SET Bakiye = 5000 WHERE MusteriID = 1;
ROLLBACK TRANSACTIONTekrar tablomuza select çektiğimizde yukarıda kullanıcının yanlış veriyi okuduğunu görebiliyoruz.
Not: Yukarıda örnekte bahsettiğimi gibi sorguların sonuna WITH(NOLOCK) hintini koyduğumuz takdirde, sorgularımızda Isolation Level’ı set etmemiş olsak bile Read Uncommitted olarak çalışacaktır.
Kirli okuma (dirty read), tekrar eden okuma hatası (non-repeatable read) ve hayalet okuma (phantom read) ifadelerini bu yapıda görebiliriz. Yukarıdaki örnekte dikkat ederseniz güncellenen 5000 değerinin olmadığı görülmektedir.
Update yapılan satır, belirli bir aralık veya page dışında başka bir transaction üzerinde güncellenen bölüm dışında sorgumuz çalıştığında herhangi bir engelleme ile karşılaşmaz. Çünkü yukarıdaki örneğimizde sadece 1 satır üzerinde güncelleme yapıyor. Diğer satırlar üzerinde herhangi bir arama işlemi yapıldığında sorgu sonucumuz dönmektedir. Çünkü lock row seviyesindedir.
Bu isolation seviyesinde insert işlemlerinde herhangi bir sıkıntıya sebebiyet vermez. Delete işlemlerinde sadece kilitlenmiş satır haricinde diğer satırlar üzerinde işlem yapılmaktadır.
B. READ COMMITTED (Varsayılan)
Sadece commit edilmiş verileri okur. Read Uncommitted’tan farklı olarak, transaction içerisinde update edilen data commit olmadan başka bir transaction bu datayı okuyamaz. Bu şekilde dirty read oluşumu engellenmiş olur. Diğer taraftan concurrency ve performans azalmış olacaktır. Okuma konusunda herhangi bir sıkıntı yoktur.
Transaction 1: Ahmet’in bakiyesini güncelle ama commit yapma
BEGIN TRANSACTION
UPDATE Musteriler SET Bakiye = 5000 WHERE MusteriID = 1;
-- COMMIT yapılmadı!
Transaction 2: Ahmet’in bakiyesini okumaya çalışıyoruz.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM Musteriler WHERE MusteriID = 1;
Not: SET TRANSACTION ISOLATION LEVEL READ COMMITTED ifadesi yazılmazsa bile normal select ifademizde Read Committed şeklinde çalışmaktadır. Sql serverda default olan yapıdır.
Sonuç:
Transaction 2, Transaction 1’in commit edilmemiş verisini okuyamaz. Bloklanır ve bekler. Çünkü shared lock ile exclusive lock uyumsuz çalışmaktadır.
COMMIT edildikten sonra sonucumuzun döndüğünü görmüş oluyoruz
Not: Begin Transaction ifadesi kullanılmazsa sql server update’den sonra default olarak commit eder. Bu yapı karşımıza READ COMMITTED olarak geçmektedir.
Yukarıdaki ifadelerde dikkat ederseniz select cümlesi aynı satıra erişmeye çalışıyor. İlgili satır üzerinde exclusive lock oluşur. Bu ifade kendinde sonra gelen tüm ifadeleri engeller. Dikkat ederseniz sadece ilk satıra lock konulmuş bu yüzden başka bir oturumdan ilk satır dışında bulunan diğer satırlara select çekilirse sonuç dönecektir.
Aşağıdaki komutla default olan isolation level öğrenilir.
dbcc useroptions
C. REPEATABLE READ (Tekrar Eden Okuma Garantisi)
Aynı transaction içinde aynı veriyi iki kez okuduğumuzda, başka bir transaction tarafından değiştirilmesini engeller. Kirli okumayı (dirty read) engeller: Başka bir transaction, henüz commit edilmemiş değişiklikleri göremez. Tekrar eden okuma hatasını (non-repeatable read) engeller: Aynı transaction içinde aynı veriyi iki kez okursan, başka bir transaction bu veriyi değiştiremez veya silemez. Ancak phantom read olabilir: Başka bir transaction yeni bir kayıt ekleyebilir ve bu transaction sona erdiğinde, tekrar yapılan sorgularda yeni kayıtlar görünebilir.
Transaction 1: Belirtilen isolation seviyesinde okuma işlemi yapılması gerekmektedir.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION
SELECT * FROM Musteriler
-- 30 saniye boyunca bekleyelim (bu sırada başka işlem denensin)
WAITFOR DELAY '00:00:30';
SELECT *FROM Musteriler
COMMIT;
COMMIT işlemi bittikten sonra dönen sonuç aşağıdaki görülmektedir. Neden farklı değerler döndü şimdi bunları inceleyelim. Yukarıdaki sonuç çalıştırıldıktan sonra Transaction 1-2-3 aynı çalıştırılmış oldu.
Aşağıdaki işlemleri yaparken Transaction 1 işlemimiz devam ediyor.
Transaction 2: Ahmet’in bakiyesini değiştirmeye çalışalım. Yukarıdaki commit işleminin gerçekleşmesini bekler. Commit Transaction 1’de gerçekleştikten sonra update işlemimiz yapılmaktadır.
UPDATE Musteriler SET Bakiye = 20000 WHERE MusteriID = 1;
Transaction 1’de commit ifadesi gerçekleştikten sonra update işlemi gerçekleşir. Yapı gereği beklemektedir.
Sonucumuz yukarıda ilk resimde görüldüğü gibi güncel değil, tekrardan select çektiğimde değerimiz güncellenmiş olacaktır. Select ifademiz sonuçlandıktan sonra update işlemi gerçekleşmiş oldu.
Transaction 2, Transaction 1 bitene kadar güncelleme yapamaz. Fakat yeni müşteri eklenirse Transaction 1 bunu görebilir.
Yukarıdaki REPEATABLE READ isolation level seviyesi devam ederken insert işlemi gerçekleşmiş oldu.
Transaction 3
insert into Musteriler(Ad,Bakiye)values('Talat',4000)
insert into Musteriler(Ad,Bakiye)values('Kadir',10000)
Yukarıda dikkat edersek aynı anda Transaction 1 işlemi devam ederken select ifadesinde insert ettiğimiz sonuçları görüyoruz. Ayrıca Transaction 1’de commit olmadan önceki select ifadesinde bir insert değerinin geldiği görülmektedir. Update işlemi select sonuçlarım döndükten sonra gerçekleşti. Tabloya ekstra bir select çektiğimde görmüş oldum. Repeatable read yapısında commit işlemi gerçekleşmeden diğer kullanıcılar select çekebilir. Shared lock birbirlerini engellemez. Çünkü transaction1 işlemi select işlemleri shared lock birbirini engellemez.
Yukarıdaki transaction 1 ifadesi içinde olan update delete gibi ifade ise select sonucumuz dönmez.
REPEATABLE READ Phantom Read’leri Önler mi?
Hayalet okuma (phantom read) durumu, transaction içinde belirli bir koşula göre kayıt okunduğunda, başka bir transaction’ın yeni kayıt eklemesiyle ortaya çıkar. Transaction içinde ilk select sorgusunda olmayan bazı yeni satırlar, ikinci select’te görünebilir. Phantom Read önlenmez.
REPEATABLE READ Dirty Read’i Önler mi? Aynı transaction içinde, başka bir transaction’ın commit etmediği veriyi okuyamazsın.
Non-REPEATABLE READ Dirty Read’i Önler mi? Transaction sırasında okuduğun bir satırın başkası tarafından değiştirilmesini engeller. Yani select ettiğin satır bir daha select ettiğinde aynı kalır.
Yukarıdaki örnek üzerinden açıklamak gerekirse:
Transaction 1’in yaptığı ilk SELECT sorgusunda Kadir yer almaz. Yukarıdaki ilk resimde görülmektedir.
Transaction 3, Kadir ekleyebilir ve COMMIT edebilir. Çünkü REPEATABLE READ yeni satır eklemeye izin verir.
Transaction 1 delay komutundan sonra ikinci kez aynı sorguyu çalıştırdığında yeni eklenen Kadir’i görebilir. Bu, REPEATABLE READ’in hayalet okumaları (phantom read) engellemediğini gösterir.
D. SERIALIZABLE (Tam İzolasyon)
Okunan tüm satırları kilitler, hatta yeni satır eklenmesini bile engeller.
Transaction 1: Müşteri listesini alalım
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
SELECT * FROM Musteriler;
-- 5 saniye bekleyelim
WAITFOR DELAY '00:00:05';
COMMIT;Transaction 2: Yeni müşteri eklemeye çalışalım
insert into Musteriler(Ad,Bakiye)values('Veli',15000)Not: Transaction 1 devam ederken farklı bir transactiondan select çektiğimizde sonucumuz dönmektedir. Transaction 1 bitene kadar önceki değerler dönmektedir.
Sonuç
İki işlemi farklı sessionlarda çalıştırdığımda Transaction 2, Transaction 1 bitene kadar beklemek zorunda kalır.
Transaction 1 işlemin sonucunda tablomuzun insert şeklinde gelmediğini görmüş olduk.
Tekrar select çekersek güncel değerin geldiğini görmüş oluyoruz.
Bu isolation levelde Dirty Read, Non-Repeatable Read ve Phantom Read yoktur. Ayrıca bu isolation level seviyesinde devam eden bir sorgu başka bir oturumdan ilgili tabloya, page’e veya row’a erişebilir.
E. SNAPSHOT (Versiyon Tabanlı İzolasyon)(SI)
Verileri anlık kopyalar ile okur, bu sayede diğer işlemler beklemez. İlk olarak veritabanı seviyesinde bu özelliğin aktif edilmesi gerekmektedir. Okuma tutarlılığı (read consistency) sağlamayı amaçlar. SI, MVCC (Multi-Version Concurrency Control)mekanizmasını kullanarak, her işlem için verilerin bir “anlık görüntüsünü” (snapshot) oluşturur ve işlemlerin bu görüntüye göre çalışmasını sağlar. Database snapshot işleminin benzeri olarak karşımıza çıkmaktadır.
Snapshot Isolation’ın Temel İlkeleri
1. Anlık Görüntü (Snapshot) Mantığı
Bir transaction başladığında, veritabanındaki verilerin o anki haliyle bir anlık görüntüsü (snapshot) alınır. İşlem boyunca tüm SELECT işlemleri, bu görüntü üzerinden gerçekleştirilir.
2. Okuma (Read) Tutarlılığı
Okuma işlemleri, işlem başladığında alınan snapshot’taki verilere dayanır. Bu sayede, diğer işlemler tarafından yapılan güncellemeler işlem tamamlanana kadar görülmez.
3. Yazma (Write) Çatışmaları
Eğer bir işlem, belirli bir satırı değiştirmek isterse, bu satır başka bir işlem tarafından değiştirilmiş olabilir. SI altında, ilk yazan kazanır (first-writer-wins) prensibi geçerlidir. Yani, eğer bir işlem başka bir işlem tarafından güncellenmiş bir satırı değiştirmeye çalışırsa, hata (write conflict) alır ve işlemi geri alır (rollback).
ALTER DATABASE AdventureWorks2014 SET ALLOW_SNAPSHOT_ISOLATION ON;Transaction 1: Müşteri listesini okuyalım. Tablomuzu okuduktan sonra 10 saniye bekleyip diğer işlemlerimizi gerçekleştirip sonuçları gözlemleyelim.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
SELECT * FROM Musteriler;
WAITFOR DELAY '00:00:10';
SELECT * FROM Musteriler;
COMMIT;Transaction 2: Ahmet’in bakiyesini güncelliyoruz. Güncelleme sonucunda Transaction 1 işlemi devam ederken işlemin gerçekleştiğini görüyoruz.
UPDATE Musteriler SET Bakiye = 1 WHERE MusteriID = 1;Tablomuza select çektiğimizde güncelleme işlemimiz görünüyor. Transaction 1 devam etmektedir.
Transaction 1 işlemimiz devam ederken tablomuza insert işlemi gerçekleştirelim. Transaction 1 devam etmesine rağmen insert işlemi gerçekleşti.( Transaction 3)
insert into Musteriler(Ad,Bakiye)values('OKKES',1000)Transaction 1 işlemimiz devam ederken tablomuza select çekelim:
Sonuç:
Transaction 1, Transaction 2 ve Transaction 3’ün yaptığı değişiklikleri görmez. Kendi başlattığı anda var olan verinin bir kopyasını okur. Transaction 1 işlemi bitmeden önce tablomuza select çektiğimizde insert ve update değerlerinin döndüğünü görmüş oluyoruz.
Transaction 1 işlemi sonuç döndüğünde yapılan değişiklikleri görmez.
Örnek olması açısında Transaction 1 işlemini tekrardan başlatıyorum. Bu süre zarfın MusteriID değeri 10 olan veriyi silip dönen sonuçları gözlemleyelim.
Transaction 1 işleminin devam ettiği sırada veri silme işlemi yapılır.
delete from Musteriler where MusteriID=10
Transaction 1 işlemi sonuçlandıktan sonra snapshot üzerinden verilerin okunduğunu görmekteyiz.
Sql server üzerinde sys.databases komutu çalıştırdığınız snapshot_isolation_state durumu görünür. Bu ifade veritabanı seviyesinde ne kadar aktif edilsede sorgumuzun bu isolation seviyesinde başlatılması gerekmektedir. Yoksa bu değerin 1 veya sıfır olması hiç bir anlam ifade etmez. Bizden with(nolock) aynı yapıya denk gelir bu özelliği böyle kullanmamız daha mantıklı derlerse şöyle bir durum ortaya çıkar. Nolock dirty page’leri yani commit edilmemiş verileri okuma ihtimali var. Genellikle commit edilme ihtimali yüksek commit işlemi saniye dakika bazında gerçekleşir ama snapshot da ise sizin select sorgunuz 30 dakika sürebilir. Tablomuzda binlerce verinin değişmesi silinmesi ihtimali daha yüksektir. Bu da tutarsızlığı çok olan veriler üzerinde işlem yapmanız demek.
Örnek:
– Bir hastanede iki doktor var, her biri en az bir nöbette olmak zorunda.
– İlk işlem: Dr. A nöbetten çekilir, ama Dr. B hala nöbette olduğu için sorun yok.
– İkinci işlem: Dr. B de nöbetten çekilir, ama Dr. A’nın hala nöbette olduğunu düşünüyor.
– Sonuç: İki işlem de başarılı olur ama hastanede nöbette doktor kalmaz!
Anlık görüntüsüne bakarak sonuç dönderir. verinin eklendiği silindiğiyle ilgilenmez.
Sonuç ve Karşılaştırma
| Seviye | Avantajı | Dezavantajı |
| READ UNCOMMITTED | Çok hızlı | Kirli veri okuyabilir |
| READ COMMITTED | Varsayılan seviye, kirli okuma yok | Non-repeatable read ve phantom read var |
| REPEATABLE READ | Non-repeatable read yok | Phantom read var |
| SERIALIZABLE | Tam izolasyon | Performans çok düşer |
| SNAPSHOT | Okuma işlemleri bloklanmaz | Ekstra tempdb alanı kullanır |
Eğer hız ve esneklik önemliyse, READ COMMITTED veya SNAPSHOT tercih edilebilir. Ancak tam güvenlik gerekiyorsa, SERIALIZABLE kullanılmalıdır. Isolation levelle ilgili daha detaylı ve kapsamlı bilgi almak isterseniz ilgili isolation level makalelerini okumalısınız.
Not: SQL Server’da isolation level‘i genel (global) olarak değiştiremezsin çünkü isolation level, session (oturum) bazlı çalışır. Yani her bağlantı kendi isolation level’ini ayarlamak zorundadır. Yukarıdaki her bölümde vermiş olduğum kodların başlangıç olarak eklenebilir. Ayrıca SSMS arayüzünden Query>Query Options kısmında bulanan advanced sekmesinde gerekli olan ayarlamalar yapılabilir.
Başka bir makalede görüşmek dileğiyle.. Kolay gelsin.
“Yürüyüşünde ölçülü ol, sesini yükseltme; çünkü seslerin en çirkini eşeğin anırmasıdır.”Lokman-19
