Isolation Levels 3

Bu makalede RCSI ve Snapshot Isolation farklarını ve bu Isolation Level’leri kullandığımız’da oluşabilecek tutarsızlıkları inceleyeceğiz.

İki Isolation Level’da da;Tempdb’de yeterince yer olmazsa, update’ler fail olmaz fakat versiyonlamada yapamaz. Bu yüzden selectler fail olabilir.

Snapshot Isolation Level’ında update’ler conflict olabilir. RCSI’da bu gerçekleşmez.

RCSI, Snapshot Isolation Level’a göre, tempdb’de daha az yer tüketir.

RCSI Distrubuted Transaction ile çalışabilirken Snapshot çalışamaz.

RCSI tempdb,msdb ya da master veritabanlarında enable edilemez.

SNAPSHOT Isolation Level’da global temp tablo oluştururken tempdb’de SNAPSHOT’a izin verilmelidir.

SNAPSHOT Isolation Level’da,  DDL Statement ile herhangi bir obje modifiye edildiği anda, başka bir transaction aynı nesneye erişirse DDL Statement’ı fail olacaktır. Aşağıdaki örnekte görebilirsiniz.

İlk session’da aşağıdaki scripti çalıştıralım.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO
WAITFOR DELAY '00:00:10.000';
COMMIT TRANSACTION

İkinci sessionda’da da aşağıdaki scripti çalıştıralım.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
SELECT TOP 1000 [BusinessEntityID]
FROM [AdventureWorks2012].[HumanResources].[Employee]
where LoginID='adventure-works\rob0'
COMMIT TRANSACTION

İlk session aşağıdaki hata ile sonuçlanacaktır.

Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

Msg 3902, Level 16, State 1, Line 2

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Aşağıda hangi DDL işlemlerinin kısıtlandığını görebilirsiniz.

• CREATE INDEX
• CREATE XML INDEX
• ALTER INDEX
• ALTER TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER PARTITION SCHEME
• DROP INDEX
• Common language runtime (CLR) DDL

RCSI’da ise böyle bir kısıtlama yok.

RCSI ya da SNAPSHOT kullanırken tempdb’de yeterince yer olması gerektiğini daha önce söylemiştik. tempdb’de versiyonlama yapabilmek için hangi veritabanı ne kadar yer kullanıyor aşağıdaki şekilde görebilirsiniz.

select DB_NAME(database_id) AS DBName,SUM(aggregated_record_length_in_bytes)/1024 [SpaceUsed_KB] from sys.dm_tran_top_version_generators
GROUP by database_id
ORDER by SpaceUsed_KB DESC

SNAPSHOT’ı ve RCSI’ı kullanırken veride tutarsızlığa neden olabilecek birkaç örneği inceleyelim. Örneklerimizi READ COMMITTED, RCSI ve SNAPSHOT üzerinde tekrarladığımızda farklılıklarını göreceğiz.

1) İlk iki örneğimizde kullanmak için herhangi bir veritabanı üzerinde aşağıdaki scripti çalıştıralım.

create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Siyah')
insert marbles values(2, 'Beyaz')

İlk olarak READ COMMITTED üzerinde deneyelim. Yeni bir session açıp aşağıdaki sorguyu çalıştıralım. Transaction’ı başlatıp update’i gerçekleştirdik fakat henüz commit etmedik.

begin tran
update marbles set color = 'Beyaz' where color = 'Siyah

ikinci sessionda aşağıdaki sorguyu çalıştıralım. Sorguyu çalıştırdığımızda, ilk sorgu commit olmadığı için beklediğini göreceğiz.

begin tran
update marbles set color = 'Siyah' where color = 'Beyaz'
commit tran

Daha sonra tekrar ilk session’a geri dönelim ve aşağıdaki gibi commit edelim.

commit tran

Commit işleminden sonra tablomuza select çektiğimizde sonuç iki Siyah olarak dönecektir. Ve yukarıdaki gibi ilk session’ı commit ettiğimizde ikinci session’da ki bekleme sona erecektir. İlk sorguda rengi siyah olanları beyaza çevirmek istedik, fakat henüz commit etmedik. İkinci sorguda rengi beyaz olanları siyaha çevirmeye çalıştık. Fakat ilk sorgu commit olmadığı için beklemek zorunda kaldık. İlk sorgu tamamlandığında tablomuzdaki iki kayıtta beyaz haline geldi ve ikinci session beyaz olarak bu iki kaydı gördü ve ikisinide siyaha çevirdi.

Örneğimizi RCSI üzerinde tekrarladığımızda da aynı sonuçları alacağız. RCSI pessimistic write yaptığı için ikinci session READ COMMITTED örneğine olduğu gibi bekleyecek.

SNAPSHOT üzerinde örneğimizi tekrarlayıp ne olacağını görelim. Veritabanı bazında SNAPSHOT’a izin verelim. Ve ilk sessionda aşağıdaki scripti çalıştıralım.

set transaction isolation level snapshot
begin tran
update marbles set color = 'Beyaz' where color = 'Siyah'

ikinci sessionda ise aşağıdaki scripti çalıştıralım.

set transaction isolation level snapshot
begin tran
update marbles set color = 'Siyah' where color = 'Beyaz'
commit tran

Diğer iki Isolation Level’dan farklı olarak, Snapshot Isolation Level optimistic write yaptığı için ikinci session burada beklemeyecektir. Ve sonuçta 1 rows affected şeklinde dönecektir. Tekrar birinci session’a dönüp commit yaptığımızda verinin son halini Beyaz Siyah olarak görürüz. Snapshot’ta farklı kayıtların update edilmesine izin verildi fakat yukarıdaki gibi bir senaryoda uygulama snapshot davranış şekli göz önüne alınarak yazılmadıysa sıkıntılara neden olabilir. Bu senaryoyu uygulamalarınıza göre düşündüğünüzde optimistic write’ın bazı sakıncaları olduğunu görebilirsiniz.

Yalnız bu senaryoyu aynı kaydın update edilmesi ile karıştırmamak gerekir.Eğer yukarıdaki örneği aynı kaydı update edecek şekilde dönüştürürseniz ikinci session’ın beklediğini ve birinci session commit edildiğinde ikinci session’ın update conflict yaşadığını göreceksiniz.

2) İkinci örneğimizde aynı tabloyu kullanarak RCSI ile alakalı bir sıkıntıya değineceğiz.

Öncelikle örneğimizi READ COMMITTED üzerinde gerçekleştirelim. Aşağıdaki sorguyu ilk session’da çalıştıralım.

DECLARE @id INT;
BEGIN TRAN
SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Siyah';
UPDATE  dbo.marbles
SET     color = 'Beyaz'
WHERE   id = @id;

Yukarıdaki sorgu ile rengi siyah olanlardan id’si en küçük olan’ın id değerini lokal @id değişkenimize atıyoruz ve daha sonra id’si @id değişkenimizle aynı olan kayıtları Beyaz’a çeviriyoruz. Aslında yaptığımız işlem Siyah olanları beyaza çevirmek. Fakat bu işlemi direk update ile yapmıyoruz. Öncelikle select ile çekip lokal değişkenimize atıyoruz. İlk session’ı commit etmeden ikinci session’da aşağıdaki sorguyu çalıştırıyoruz.

DECLARE @id INT;
BEGIN TRAN
SELECT  @id = MIN(id)
FROM    dbo.marbles
WHERE   color = 'Siyah';
UPDATE  dbo.marbles
SET     color = 'Sarı'
WHERE   id = @id;
COMMIT TRAN
GO

İkinci sorguda da rengi siyah olanlardan id’si en küçük olan’ın id değerini lokal @id değişkenimize atıyoruz ve daha sonra id’si lokal @id değişkenimize eşit olan satırın rengini Sarı olarak update ediyoruz.Fakat sorgunun beklediğini göreceğiz. Tekrar ilk session’a geçip commit işlemini gerçekleştirdiğimizde ikinci session 0 rows affected şeklinde sonuç döndürecek. Verinin son haline baktığımızda Beyaz Beyaz olarak göreceğiz. İkinci session ilk session bitene kadar update edilmiş ama commit edilmemiş veriyi okuyamadı. İlk session commit edildiğinde, ikinci session commit edilmiş veriyi okuyabildi fakat rengi siyah olan bir satır bulamadığı için herhangi bir update gerçekleştirememiş oldu.

Aynı örneği RCSI kullanarak gerçekleştirdiğimizde ikinci session’ın yine ilk session’ın commit edilmesini beklediğini görüyoruz. Fakat READ COMMITTED’tan farklı olarak, RCSI’da yapılan select, commit edilmemiş verinin en son commit edilmiş haline tempdb üzerinden ulaşabildiği için, ikinci session’da ki ilk select okuma yaparak rengi siyah olanlardan id’si en küçük olan’ın id değerini lokal @id değişkenine aktarabildi. Fakat update beklemeye devam etti. İlk session’ı commit ettiğimizde ise ikinci session tamamlandı ve 1 rows affected değerini döndürdü. İlk session Siyah’ı beyaza döndürmüş olsa bile ikinci session id değerini elinde bulundurduğu için update’i id üzerinden gerçekleştirdi. Verinin son haline baktığımızda ise Sarı Beyaz olarak göreceğiz.

Aynı örneği SNAPSHOT kullanarak gerçekleştirdiğimizde ikinci session ilk session’ı yine bekleyecek ve ilk session commit edildiğinde ikinci session update conflict hatasını verecek. Sonuç olarak verinin son hali Beyaz Beyaz olacaktır.

3) Son olarak RCSI veSNAPSHOT Isolation Level’larında oluşabilecek farklı bir soruna değinen bir örnekle makalemi noktalayacağım. 

Örneğimizi yine öncelikle READ COMMITTED üzerinde gerçekleştireceğiz. Aşağıdaki sorguyu herhangi bir veritabanında çalıştıralım.

CREATE TABLE Tickets(TicketId INT NOT NULL,
  AssignedTo INT NOT NULL,
  Priority VARCHAR(10),
  CONSTRAINT PK_Tickets PRIMARY KEY(TicketId),
)
GO
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(1, 1, 'High')
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(2, 8, 'High')
INSERT INTO Tickets(TicketId, AssignedTo, Priority)
  VALUES(3, 10, 'High')
go

Yeni bir query sayfası açıp aşağıdaki scripti çalıştıralım. Bu sorguda, Ticket tablosunda AssignedTo değeri 6 olan ve Priority değeri High olan bir satır yoksa  TicketId’si 1 olan satırın AssignedTo değerini 6 olarak set ediyoruz.

BEGIN TRANSACTION
UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

Commit işlemini yapmadan ikinci session’a geçip aşağıdaki scripti çalıştıralım. Bu sorguda da ilk koşulumuz yine  Ticket tablosunda AssignedTo değeri 6 olan ve Priority değeri High olan bir satır olmaması. Bu şartlar sağlanıyorsa bu sefer TicketId 2 olan satırın AssignedTo değerini 6 olarak set ediyoruz.

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

İkinci session’ımızda yukarıdaki sorguyu çalıştırdığımızda beklediğini göreceğiz. İlk session’a geri dönüp commit işlemini gerçekleştirdiğimizde ikinci session’da ki bekleme bitecektir ve 0 rows affected olarak değer dönecektir. Çünkü ilk sorgu tamamlandığında ikinci sorguda Ticket tablosunda AssignedTo değeri 6 olan ve Priority değeri High olan bir satır olmaması şartı sağlanamadı. Artık TicketId’si 1 olan satırın AssignedTo değeri 6 olarak gözüküyor.

Aynı örneği RCSI ya da SNAPSHOT kullanarak gerçekleştirdiğimizde, ilk session commit olmadan ikinci session’ın çalıştığı anda sonuçlanacağını ve 1 rows affected şeklinde sonuç döndüreceğini göreceğiz. READ COMMITTED’da select commit olmamış veriyi okuyamadığı için, ikinci session’da ki,Ticket tablosunda AssignedTo değeri 6 olan ve Priority değeri High olan bir satır olmaması şartını oluşturan select okuma işlemini gerçekleştiremedi fakat RCSI’da ya da SNAPSHOT’ta bu select en son commit edilmiş halini okuyabildiği için select işlemi gerçekleşti ve bunun sonrasında update yapabildi. İlk session’a dönüp commit işlemini gerçekleştirdiğimizde onunda tamamlandığını ve tablomuzda artık AssignedTo değeri 6 olan ve Prioritry değeri High olan iki tane kayıt olduğunu görebiliriz. Yukarıdaki örnekte aslında tablomuzda AssignedTo değeri 6 olan ve Priority değeri High olan tek bir satır bulunabilmesini amaçladığımız halde, RCSI ya da SNAPSHOT ile bu koşulları sağlayan iki kaydın oluşmasına sebep olduk. Tabiki sorguda yapılacak değişikliklerle RCSI ya da SNAPSHOT kullanarak aynı işlemleri yapabilmek mümkün. Fakat burada göstermek istediğim şey; RCSI ya da SNAPSHOT kullanırsak, uygulamamızda tutarsız veri olabilir. Bunun için uygulamacıları bu konularda uyarmalı ve geçişe karar verildiyse gerekli değişiklikleri yapmasını sağlamalıyız. Aşağıda Hangi Isolation Level’da hangi concurrency sorunlarının meydana gelebileceğini ve isolation Level’ların concurrency kontrol yaklaşımlarını tablo halinde görebilirsiniz.

Isolation Levels 2

1) Read Committed Snapshot(RCSI): Read Committed Isolation Level’ın row versioning kullanan halidir. SQL Server 2005 ile gelen bir Isolation Level’dır. Diğer Isolation Level lardan farklı olarak SET TRANSACTION ISOLATION LEVEL komutuyla set edilmez. Bu Isolation Level veritabanı bazlı set edilebilir. Set edildiğinde veritabanındaki tüm transactionlar bu şekilde çalışacaktır. Bu nedenden dolayı , bu Isolation Level’a geçerken uygulamada çok değişiklik yapmak gerekmez. Diğer taraftan transaction bazlı olmaması bazı durumlarda tutarsızlığa neden olabilir. Isolation Level Serisinin 3. kısmında SNAPSHOT ile RCSI üzerinde meydana gelebilecek sorunları incelerken bu durumlardan bahsedeceğim. Aşağıdaki şekilde set edebilirsiniz.

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

Eğer veritabanınız başka sessionlar tarafından kullanılıyorsa yukarıdaki scripti çalıştırdığınızda aşağıdaki gibi hata alabilirsiniz.

ALTER DATABASE [AdventureWorks2012] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Isolation Levels 1

Isolation Level, bir transaction’ın bir işlemi karşısında diğer transaction’ların nasıl davranacağını belirler.

Öncelikle veritabanında oluşabilecek concurrency sorunlarını ele alalım. Daha sonra bu sorunların hangi isolation Level’da nasıl ortaya çıktığını örneklerle inceleyeceğiz.

Dirty Read: Transaction select yaptığında, başka bir transaction tarafından yapılan update’in commit edilmemiş halini okur. Diğer transaction commit yerine rollback yaptığı takdirde, ilk transaction tarafından yapılan select aslında geçerli olmayan datayı okumuş olacaktır. Bu yüzden dirty read olarak adlandırılır.

Lost update: İki transaction’ın aynı veriyi okuması ve daha sonra bu okuduğu değeri yeni bir değere set etmesi sonucu oluşur. İlk yapılan update kaybolacaktır. Bu yüzden lost update olarak adlandırılır.

Non-repeatable read: Bir transactionda select yapıldıktan sonra, başka bir transaction’ın yapılan bu select sonucunu değiştiren bir update gerçekleştirmesiyle, ilk transactionda aynı select ikinci kez çağrıldığında farklı bir değer dönmesi sonucu oluşur. 

Phantom read: Bir transactionda yapılan bir select’in birden fazla kez çalışmasıyla, farklı sayıda kayıt döndürmesi sonucu oluşur. Birinci transactionda select yapıldığını düşünelim. Başka bir transaction içersinde birinci transactionda yapılan select’in içerdiği aralığa bir kayıt ekleme veya bu aralıktan bir kayıt sime işlemi gerçekleşmesi sonucunda , ilk transactionda ikinci kez select yapıldığında döndürdüğü kayıt sayısı farklı olacaktır.

Şimdi Isolation Level’ları örnekler yaparak inceleyelim. Örnekleri AdventureWorks2012 veritabanı üzerinde yapacağız.

1) Read Uncommitted: Update edilen datayı commit edilmemiş hali ile okumaya izin verir. Bu Isolation Level’da bütün concurrency sorunları meydana gelebilir. Bunun yanı sıra performans olarak daha etkilidir. Sırayla bu Isolation Level’da meydana gelebilecek concurrency sorunlarını örnekler yaparak inceleyelim.

Dirty Read: Aşağıdaki sorguyu yeni bir query sayfası açıp çalıştıralım. Bu sorguda FirstName’i  Abigail, LastName’i Jones ve BusinessEntityID’si 12038 olan kayıt üzerinde işlem yapacağız. Aşağıdaki örnekte bu kaydın FirstName’ini ‘DirtyReadsExample’ olarak set ediyoruz. Daha sonra 10 saniye bekliyoruz ve update işlemini rollback yapıyoruz. Ve son olarak kaydımızı gerekli filtrelerle çekiyoruz.

USE AdventureWorks2012;
BEGIN TRANSACTION;
UPDATE Person.Person
SET FirstName = 'DirtyReadsExample'
WHERE LastName = 'Jones' AND BusinessEntityID=12038;
WAITFOR DELAY '00:00:10.000';
ROLLBACK TRANSACTION;
SELECT FirstName
,LastName,BusinessEntityID
FROM Person.Person
WHERE LastName = 'Jones' AND BusinessEntityID=12038;

İkinci bir session açıp aşağıdaki scripti çalıştıralım.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT FirstName ,LastName,BusinessEntityID
FROM Person.Person
WHERE LastName = 'Jones' AND BusinessEntityID=12038;

Aşağıda gördüğümüz gibi ilk sessionda yaptığımız update’i rollback yaptığımız için en son çalıştırılan select’te FirstName Abigail olarak geldi. İkinci session ise, ilk session’da yapılan update commit edilmese bile değişikliği aldı ve FirstName’i DirtyReadsExample olarak gördü.

Sorguların sonuna  WITH(NOLOCK) hintini koyduğumuz takdirde, sorgularımızda Isolation Level’ı set etmemiş olsak bile Read Uncommitted olarak çalışacaktır.

Lost Update: Aşağıdaki sorguyu ilk sessionda çalıştıralım.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 5;
BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;
WAITFOR DELAY '00:00:10.000';
UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;
SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;

Aşağıdaki sorguyu hemen arkasından ikinci bir sessionda çalıştıralım.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 100;
BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;
UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;
SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;

Aşağıda da görüldüğü gibi ilk transaction’da ProductID’si  1 olan kolana ait değer önce @SafetyStockLevel değişkenine aktarılıyor. Daha sonra bu değer @Uplift değişkeni ile toplanıp 10 saniye bekleme işlemi başlatılıyor. İkinci transactionda bekleme işlemine kadar olan kısım aynen gerçekleştiriliyor. Ve hemen arkasından bekleme olmadan update gerçekleştiriliyor. Sonuç olarak ikinci transactionda 1100 değerinin döndüğünü görüyorsunuz. İlk transaction’da da bekleme sonrası update’i gerçekleştirelim. Bu sefer değerin 1005 olduğunu ve ikinci transaction daki 1100 değerinin kaybolduğunu görüyoruz. Bu örneği daha iyi anlayabilmek için iki kişinin ortak bir hesap kullandığını düşünelim. Ve hesapta 1000 TL para olsun.İkinci transactionda ikinci kişinin hesaba 100 TL yatırdığını farzedelim. Birinci transactionda birinci kişide 5 TL yatırmış olsun. Eğer bu örnekteki gibi sorun olursa ikinci kişinin yatırdığı 100 TL kaybolmuş olacaktır.

Non-repeatable read: İlk sessionda aşağıdaki scripti çalıştıralım.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person
ORDER BY LastName;
WAITFOR DELAY '00:00:10.000';
SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person
ORDER BY LastName;
COMMIT TRANSACTION;

İkinci sessionda da aşağıdaki scripti çalıştıralım.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
UPDATE Person.Person
SET Suffix = 'Junior'
WHERE LastName = 'Abbas'
AND FirstName = 'Syed';
COMMIT TRANSACTION;

Aşağıda da görüldüğü gibi ilk sessiondaki ilk select’te Suffix değerleri Null  geliyor. İkinci sessionda FirstName’i Syed olan ve LastName’i Abbas olan kolon’un Suffix değeri Junior olarak set ediliyor. İlk sessionda 10 saniye beklemeden sonra aynı select tekrar çekiliyor ve gördüğünüz gibi farklı bir sonuç döndüyor.

Phantom Read: İlk sessionda aşağıdaki sorguyu çalıştıralım.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION
--T1:RowCount=2
SELECT *
  FROM [AdventureWorks2012].[HumanResources].[Department]
  where GroupName='Manufacturing'
  WAITFOR DELAY '00:00:10.000';
--T2:RowCount=3
--Bu arada select sonucu aldığımız verilerle bazı işlemler yapıyor olabiliriz.
SELECT *
  FROM [AdventureWorks2012].[HumanResources].[Department]
  where GroupName='Manufacturing'
COMMIT TRANSACTION

ikinci sessionda aşağıdaki sorguyu çalıştıralım.

USE [AdventureWorks2012]
GO
INSERT INTO [HumanResources].[Department]
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
     VALUES
           ('PhantomReadExample'
           ,'Manufacturing'
           ,'2002-06-01 00:00:00.000')
GO

Aşağıda da görüldüğü gibi birinci transactiondaki ilk select iki kayıt döndürürken ikinci select 3 kayıt döndürmektedir.

READ UNCOMMITTED Isolation Level’ını kullandığımızda gördüğümüz gibi bütün concurrency sorunları meydana gelebiliyor. Bunun yanı sıra selectler bekleme olmadan sonuç aldığı için performans anlamında bazı kazançlar sağlıyor. Eğer veri tutarlılığı çok önemli olmayan bir sistemde çalışıyorsanız bu Isolation Level’ı tercih edebilirsiniz.

2) Read Committed: SQL SERVER ‘da default Isolation Level’ dır. Read Uncommitted’tan farklı olarak, transaction içersinde 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. Lost Update, Non-repeatable read ve Phantom Read bu Isolation Level’da da Read Uncommitted’ daki gibi davranacaktır. Dirty Read örneğimizi bu Isolation Level’ı kullanarak tekrarlayalım.

Dirty Read: READ UNCOMMITTED’tan farklı olarak ikinci session’ı çalıştırdığımızda, birinci session bitene kadar bizi bekletecektir. Birinci session bittikten sonra ikinci session’da da işlem tamamlanacaktır  ve sonuç olarak bize Abigail Jones 12038 şeklinde kirli olmayan data geri dönecektir.

3) Repeatable Read: Bu Isolation Level’ın amacı, bir transaction içersinde yapılan select’e ait sonucun transaction sonlanana kadar aynı kalmasını sağlamaktır. Repeatable Read ile çalışan bir transaction içersinde select sorgusu çekilmişse, bu data başka bir transaction tarafından güncellenemez.  Bu Isolation Level’da, dirty read örneğimiz ve phantom read örneğimiz READ COMMITTED Isolation Level’daki gibi davranacaktır. Lost Update ve Non-repeatable read örneklerimizi sırasıyla tekrar uygulayalım.

Lost Update: Örneğimizi uyguladığımızda ikinci session’ın, ilk session’ın bitmesini beklediğini görüyoruz. İlk session update komutunu çalıştırdığında ise ikinci session’ın aşağıdaki hatayı aldığını görüyoruz. Repeatable Read Isolation Level’ının, ilk session’da yapılan select’in ikinci session’da değişmesini engelleyerek lost update’i engellediğini görüyoruz

Transaction (Process ID X) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Non-Repeatable Read: Örneğimizi uyguladığımızda ikinci session’ın, ilk session’ın bitmesini beklediğini görüyoruz. İlk session commit olduğunda READ COMMITTED Isolation Level’dan farklı olarak iki select sonucunun da aynı olduğunu görüyoruz. REPEATABLE READ Isolation Level’ı transaction bitene kadar select’in başka bir transaction tarafından update edilmesine izin vermedi. Ama transaction bittikten sonra ikinci session’ın istediği değişikliği yaptığını görüyoruz.

4) Serializable: Bu Isolation Level’da bir transaction içersinde select yapılıyorsa, başka bir transaction içersinden, ilk transaction bitene kadar ilk transactiondaki select aralığına erişilemez. (Insert,Update,Delete) Ve bir transaction içersinde data modifikasyon işlemleri yapılıyorsa, diğer transactionlar  işlem yapılan aralıkta read yapamaz. Bu Isolation Level’da Dirty Read, Lost Update ve Non-repeatable Read örneklerimizi tekrarladığımızda, Repeatable Read Isolation Level’ daki  sonuçlarla aynı sonuçları alacağız. Fakat Phantom Read örneğimizi tekrarladığımızda bu sorunun ortadan kalkmış olduğunu göreceğiz.

Phantom Reads: Sorgularımızı SERIALIZABLE Isolation Level’da çalıştırdığımızda ilk sorgudan aşağıdaki gibi bir sonuç dönüyor. Gördüğünüz gibi bu Isolation Level’da ilk transaction bitene kadar ikinci transaction insert işlemini gerçekleştiremedi.

Serimizin ikinci makalesinde, row versioning bazlı çalışan Isolation Level’ları (RCSI ve SNAPSHOT) inceleyeceğiz. Bu Isolation Level’ların tanımlarını yapıp hangi Isolation Level’da hangi concurrency sorunu nasıl oluşuyor, row versioning bazlı çalışmalarının artıları ve eksileri nelerdir örnekler üzerinde göreceğiz.

Numa Nodes, MAX/MIN Server Memory, Lock Pages In Memory, MAXDOP

Bu makalede Numa Node’ lu bir sistemde yapılması gereken ayarları inceleyeceğiz. Ama bu Numa Node’u olmayan sistemlerde bu ayarların yapılmayacağı anlamına gelmiyor. Makalede okuyacağınız ayarların hepsi, aslında her sistemde yapılması gereken ayarlar. Konuya Numa Node ile girmemizin sebebi, Numa Node devreye girdiğinde bu ayarların daha kritik bir hale gelmesi.

Bir sistemdeki CPU sayısı ve saat hızı^Not1 arttığında, Memory anlamında darboğazlar başlar. Artan CPU ve saat hızı nedeniyle artan işlemler, tek memory bus üzerinde bir çekişme haline girerler. Bu yüzden memory’de bazı beklemeler yaşanmaya başlar. Numa, bunu önlemek için tasarlanmış bir yapı olarak karşımıza çıkıyor. Örnekleme üzerinden gidecek olursak 64 CPU‘ya sahip bir sistem olduğunu düşünelim. Sistemde 8 tane numa Node’u olsun. Böylelikle her numa node’unda 8 tane CPU olur. Ve bu 8 CPU’lu numa node’u için kendine ayrılmış özel bir memory tahsis edilir. Her numa node’un kendi içersindeki memory’e local memory denir. Diğer node lar üzerindeki memory’e ise foreign memory denir.  Ve numa node ları üzerindeki her memory grubu kendine ait memory bus üzerindedir. Var olan cpu sayısının bölünmesi ve bu bölünen CPU grubuna memory tahsis edilmesiyle birlikte memory darboğazımız aşılmış olur. Artık her numa node’una ait CPU üzerinde çalışan işlem çalıştığı numa node’un kendine ait olan memory bus’ı üzerine gidecek ve latency azalacaktır. 

Aşağıda, maddeler halinde Numa Node’u olan bir sistemde yapılması gerekenleri listeledim:

1.  MAX SERVER MEMORY ve MIN SERVER MEMORY

Aynı olacak şekilde set edilmelidir.

Bu şekilde set edildiğinde SQL Server’da memory allocate ve deallocate işlemleri yapılmayacaktır. MAX SERVER MEMORY’nin set edilmesiyle, sql server’ın memory’i eşit bir şekilde numa nodelarına dağıtması için elinde daha iyi bir değer olur. Ve bu da  Foreign Page gerekliliğini azaltarak sorguların performansına etki eder.^Not2

2.  Lock Pages In Memory

SQL Server Service hesabına gerekli hak verilmelidir.

Bu şekilde SQL Server’ın sahip olduğu memory’nin windows’a geri verilmesi engellenmiş olur.Lock pages in memory Windows üzerindeki bir özelliktir. SQL Server servis hesabına Windows üzerinde gerekli hak verildiğinde sql server, buffer pool için windows’tan aldığı memory’i bir daha windows’a geri iade etmez. Lock pages in memory ayarlarını yaparken de windows’a gerekli ram miktarını bırakmalısınız. Bu konuda dikkat edilmesi gerekenleri Not2’de belirttim. Aşağıdaki şekilde gerekli hakkı verebilirsiniz.

Lock pages in memory üzerinde sağ tıklayıp properties dedikten sonra sql server servis hesabını tanımlıyoruz.

Ek bir bilgi paylaşımı olarak Lock pages in memory’nin enable ettikten sonra large page extensions’ı da aktif hale getirebilirsiniz. Bunun için 834 numaralı trace flag’ı startup parametlerine eklemeniz gerekiyor. Ama large page kullanımını aktif hale getirmek sisteminiz için ne kadar gerekli, faydaları ve sakıncaları nelerdir, memory nizdeki large page büyüklüğünü nasıl öğrenebilirsiniz? Bunlar başka bir makalenin konusu olabilir.

3.  MAXDOP, her bir numa node’undaki CPU sayısını geçmemelidir. Bu şekilde bütün numa node’larının eş zamanlı daha verimli çalışması sağlanır. MAXDOP, numa node içersindeki cpu sayısını geçmediği takdirde gelen sorgu büyük olasılıkla local memory’i kullanacaktır. Ve buda Foreign Page gerekliliğini azaltarak sorguların performansına etki edecektir. Tabi bu MAXDOP’u numa node’u içersindeki CPU sayısı kadar set edelim anlamına da gelmiyor. MAXDOP ayarı sistemin türüne göre yapılmalıdır. OLTP sistemlerde genelde bu değer 1 olur. Önemli bir ayrıntı olarak MAXDOP 0 olarak ta set edilmemeli. Çünkü 0 olduğunda, sorgu, sunucudaki CPU sayısı kadar paralel çalışabilir anlamına geliyor, bu da 3. Maddenin başında belirttiğimiz gibi istenilen bir şey değil.

Aşağıdaki sorguyla SQL Server 2012 için Foreign Node Memory, Free Node Memory ve Total Node Memory bilgilerini öğrenebilirsiniz.

select  counter_name , cntr_value / 1024 memory_mb ,
        instance_name,*from    sys.dm_os_performance_counters
where  [object_name] like '%SQLServer:Memory Node%'

Yukarıdaki script’i öncelikle where koşulu olmadan çalıştırıp SQLServer yazan yere ne yazacağınızı belirleyip daha sonra where koşulunu kullanarak çalıştırmalısınız.

Not1: Clock speed(saat hızı), işlemcinin bir komutu işleyebileceği en küçük an dilimidir. İşlemci, saat hızının artmasıyla bir işlemi daha kısa sürede tamamlayabilir.

Not2: Eğer sunucuda birden fazla instance kullanıyorsanız, ya da SQL Server A/P,A/P Failover Cluster yapıda çalışıyorsa, lock pages in memory ve MIN SERVER MEMORY’i set ederken instance’ların kullandıkları memory’nin toplamına dikkat etmelisiniz. Always On için ise böyle bir sıkıntı yok. Çünkü primary ve secondary olmak üzere her iki sunucuda da 1 instance olacak şekilde Always On yapısını kullanabilirsiniz. Ayrıca bahsettiğim tüm yapılar için geçerli olmak üzere  windows’a gerekli ram miktarını bırakmalısınız. Aksi takdirde Windows memory darboğazı yaşayıp sistemi restart edebilir. Özellikle reporting services, analysis services çalışıyorsa ya da uygulamalar veritabanı sunucusu üzerinden hizmet veriyorsa buna daha çok dikkat etmek gerekir. (Bu hiç tavsiye edilmez) Bu özelikleri set etmeden önce Resource Monitor’den hangi uygulamanın/servisin ne kadar ram kullandığına bakabilirsiniz. Sistemin memory anlamında bir düzensizlik yaşamaması için Available Mbytes perfmon counter’ını SCOM ile takip etmelisiniz. Sistemde memory anlamında bir baskı olduğunda baskıya neden olan uygulamayı/servisi bulup İşletim Sistemini bu baskıdan kurtarabilirsiniz.