There are 5 different Isolation levels available in SQL Server. They are Read Committed, Read Uncommitted, Repeatable Read, Snapshot and Serializable. There is another flavor of Isolation know as Read Committed Snapshot as well. Before we start learning about Isolation Levels, let's learn about ACID nature of SQL Server as well from here.
Lets create a sample DB and table as below to play with different isolation levels:
USE MASTER
CREATE DATABASE IsolationDB
GO
USE IsolationDB
GO
CREATE TABLE Isotab
(
EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(256),
LastName varchar(256),
Gender varchar(10)
CONSTRAINT pk_Isotab PRIMARY KEY (EmployeeID)
)
GO
Read Committed: This is the default Isolation of the SQL Server, It means selects will only returns the committed data. The select transactions will issue a Shared lock on the requested data, and the Select statement will wait until any another transactions with exclusive locks that trying to modify the data are completed. Once the shared lock is captured other transactions trying to modify that data will request an exclusive lock and wait until this Read Committed transaction finishes.
Run both the queries:
Query 1:
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
USE IsolationDB
GO
BEGIN TRAN
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'F')
WAITFOR DELAY '00:00:10'
ROLLBACK
Query 2:
USE IsolationDB
GO
SELECT * FROM dbo.Isotab
GO
You will see query 2 just spins and waits for query 1 to finish. You will see also a blocking on the session that running query 2 which gets blocked by session that running query 1. At the end Query 2 returns an empty set as the Query 1 rollbacks all the modifications.
Read Uncommitted: This is the lowest isolation level. Read uncommitted issues\requests no shared locks on the data, it allows the Selects to read data that is currently being modified in other transactions. It also allows other transactions to modify data that Selects are reading. This where we can see some unexpected results in a variety of different ways. For example data returned by the select is partial as an update was running in another transaction causing some of rows to come back with the updated values and may be few records got deleted .
To see the effect of read uncommitted lets run Query 1 in one tab of SSMS and then quickly run Query 2 in another tab before Query 1 completes. We will see the Query 2 returns the data which is not committed on Query 1.
Query 1:
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
USE IsolationDB
GO
BEGIN TRAN
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'F')
WAITFOR DELAY '00:00:10'
ROLLBACK
Query 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
USE IsolationDB
GO
SELECT * FROM dbo.Isotab
GO
From above we found that Query 2 will not wait for Query 1 to complete. This why Query 2 produced the the dirty data. Remember Query 1 rolls back all its changes however Query 2 has returned the data anyway, this is because it didn't wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.
There is a shortcut for querying data with the read uncommitted isolation level with using the NOLOCK table hint as below, which will yield the same result as Query 2.
USE IsolationDB
GO
SELECT * FROM dbo.Isotab WITH (NOLOCK)
GO
Repeatable Read: This is similar to Read Committed but with the additional guarantee that if a transaction issues a same select twice it will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction. This means any transactions that try to modify these records are forced to wait for the read transaction to complete. But it will allow new records to get added, it does not stop new records being inserted so it is possible to get Phantom Reads with this isolation level.
Before we start testing this truncate our test table on our test database.
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
Run the below Queries simultaneously in two sessions on SSMS.
Query 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
USE IsolationDB
GO
BEGIN TRAN
SELECT * FROM dbo.Isotab
GO
WAITFOR DELAY '00:00:15'
SELECT * FROM dbo.Isotab
COMMIT
Query 2:
USE IsolationDB
GO
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'M')
Below is the result set for Query 1 one select set is empty and another select set has 3 records. Query 2 with inserts completed as soon as it ran after Query 1 started and it wont wait for Query 1 to finish as Query 2 is not running Update or Deletes.
Now Repeat the Query 1 and Query 2, but this time run a update Statement in Query 2.
Query 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
USE IsolationDB
GO
BEGIN TRAN
SELECT * FROM dbo.Isotab
GO
WAITFOR DELAY '00:00:15'
SELECT * FROM dbo.Isotab
COMMIT
Query 2:
USE IsolationDB
GO
UPDATE isotab
SET Gender='F' WHERE lastname='Trump'
Below is the result set for Query 1 , This time both select sets are equal with 3 records and same data. This time Query 2 with update is waited for Query 1 complete as Query 2 is not running Update. We will see same result if Query 2 running a delete.
Serializable: This isolation level also eliminates Phantom Reads as well. This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try Insert\Delete\Update to these records are forced to wait for the read transaction to complete.
Before we start testing this truncate our test table on our test database.
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
Run the below Queries simultaneously in two sessions on SSMS.
Query 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
USE IsolationDB
GO
BEGIN TRAN
SELECT * FROM dbo.Isotab
GO
WAITFOR DELAY '00:00:15'
SELECT * FROM dbo.Isotab
COMMIT
Query 2:
USE IsolationDB
GO
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'M')
Below is the result set for Query 1, both select sets are with zero records. The Query 2 with Insert waited for Query 1 to complete unlike Repeatable Read which allows Inserts. Query 2 will finish and inserts records after Query 1 is finished. We will see same result for Query 2 running an update or Insert.
Snapshot: This provides the same guarantees as Serializable. The difference is the way it works. It works entirely different than the Serializable. When we use the Snapshot it doesn't block other queries from Inserting or Updating or Deleting the data touched by the snapshot transaction unlike Serializable. Instead Snapshot uses row versioning when the data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.
The positive side of Snaphot isolation is not blocking any transactions that try modifying the data while running the select transaction but it uses extra resources on the SQL Server to hold multiple versions of your changes in the tempdb.
To use the snapshot isolation level we need to enable it on the database by running the following command.
ALTER DATABASE IsolationDB
SET ALLOW_SNAPSHOT_ISOLATION ON
Or we can enable it from Database properties:
Before we start testing this truncate our test table on our test database.
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
Query 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
USE IsolationDB
GO
BEGIN TRAN
SELECT * FROM dbo.Isotab
GO
WAITFOR DELAY '00:00:10'
SELECT * FROM dbo.Isotab
COMMIT
Query 2:
USE IsolationDB
GO
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'M')
Below is the result set for Query 1, both select sets are with zero records. The Query 2 with Insert Doesn't waited for Query 1 to complete unlike Serializable which makes insert\delete\updates to wait. Query 2 finished and inserted records before Query 1 is finished. We will see same result for Query 2 running an update or Delete.
Note: Even though we have enabled the Snapshot isolation at database level we still need to use the SET TRANSACTION ISOLATION LEVEL SNAPSHOT to use the Snapshot Isolation, other wise it will use the Default Isolation (Read Committed). And we can use any Isolation on session base though SNAPSHOT Isolation is enabled at database level.
In any Isolation (Read Committed, Read Uncommitted, Repeatable Read and Serializable) the Update\Delete transaction which is issued later will wait until the prior Update\Delete transaction to complete and later Transaction will end as successful transaction as soon as prior transaction completes.
But In Snapshot Isolation Conflict will occur and later transaction will end with failure as soon as the the prior transaction complete. Lets test this.
Before we start testing this truncate our test table on our test database and set DB Isolation to Snapshot:
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
---- insert some test data
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'F')
---- Change DB to use Snapshot Isolation
ALTER DATABASE IsolationDB SET ALLOW_SNAPSHOT_ISOLATION ON
Query 1:
USE IsolationDB
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE Isotab
SET Gender='Female' WHERE LastName='Trump'
WAITFOR DELAY '00:00:15'
COMMIT
Query 2:
USE IsolationDB
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE Isotab
SET Gender='Male' WHERE LastName='Trump'
COMMIT
Now first run Query 1 which try changes Trump gender to Female and it waits for 15 secs before it commit. Meanwhile we have figured out the mistake and tried to change it to Male using the Query 2. Query 2 will wait until the Query 1 finishes and fails to change it to male. And the Query 2 error looks like below. And it doesn't happen in any other Isolation Level.
Read Committed Snapshot: To rectify this Update Conflict issue with Snapshot Isolation we have an option which is Enabling Is_Read_Committed_Snapshot_On. It works more like Read_Committed while modifications and works like Snapshot while selects. But there few differences.
Before we start testing this truncate our test table on our test database and set DB Isolation to Read_Committed_Snapshot:
USE IsolationDB
GO
TRUNCATE TABLE dbo.Isotab
GO
DBCC CHECKIDENT ('Isotab', RESEED,0)
GO
---- insert some test data
BEGIN TRAN
INSERT INTO DBO.Isotab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'F')
COMMIT
---- Change DB to use Snapshot Isolation
ALTER DATABASE [IsolationDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
Query 1:
USE IsolationDB
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE Isotab
SET Gender='Female' WHERE LastName='Trump'
WAITFOR DELAY '00:00:15'
COMMIT
Query 2:
USE IsolationDB
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE Isotab
SET Gender='Male' WHERE LastName='Trump'
COMMIT
Now first run Query 1 which try changes Trump gender to Female and it waits for 15 secs before it commit. Meanwhile we have figured out the mistake and tried to change it to Male using the Query 2. Query 2 will wait until the Query 1 finishes and changes it back to male. Below is the result.
Key Differences between Snapshot and Read Committed Snapshot are Snapshot isolation provides Transaction Level Read Consistency where as Read Committed Snapshot Isolation provide Statement Level Read Consistency And you can see a Nice Video on these two Isolation Levels here (Venkat has given a great and simple presentation on this).
We can check the Isolation Level that we are using at session level by running the DBCC USEROPTIONS command.
Thanks
Hope it helps some one.
