Snapshot Isolation Level in SQL Server – What, Why and How Part 2

Introduction

Introduced in SQL Server 2005, Snapshot Isolation levels improve performance, however, there are some caveats to consider when using this feature. Some people use it frequently, as it minimizes blocking and improves performance/concurrency, without knowing its impact on maintaining versions in tempdb. Others stay away from it because of this extra overhead. Some people are confused by the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one variant where the other is needed or vice versa.

In this article series I discuss what snapshot isolation levels are, their variants, their performance and cost impact, why and when we should use them, and how we should start using this feature.

Please refer to my earlier article (Snapshot Isolation Level in SQL Server – What, Why and How – Part 1) for a basic understanding of isolation levels and snapshot based isolation levels and how they differ from each other in terms of performance and cost.

How to Use Snapshot Isolation Level

Whichever snapshot isolation you use, you need to first enable it at a database level. For RCSI it becomes the default isolation level for all the transactions, whereas you need to specifically specify the SI with the transaction with which you want to use it with the SET command. Here is the command to enable it at database level:

--To enable Snapshot Isolation (SI)
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON
 
--To enable Read Committed Snapshot Isolation (RCSI)
ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT ON
 
--To view current snapshot configuration for a database
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state   
FROM sys.databases
WHERE name = 'AdventureWorks2012'

Example – Read Committed Snapshot Isolation (RCSI)

In the example below you can see at Time 4 the data reader (Connection 2) does not get blocked even though there is already an exclusive lock on the object. SQL Server provides data from the version store to the data reader without blocking it for the transaction to complete (Connection 1). At Time 6, you can see the data reader (Connection 2) gets the last committed version and that it differs from Time 4.

 

Connection 1

Connection 2

Time 1

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 2

BEGIN TRAN

UPDATE HumanResources.Employee

SET JobTitle = ‘Chief Executive Officer – CEO’

WHERE BusinessEntityID = 1

 

Time 3

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

 

Time 4

 

–No Blocking returns committed version before statement starts

BEGIN TRAN

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 5

COMMIT TRAN

 

Time 6

 

–No Blocking returns committed version before this statement starts

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 7

 

COMMIT TRAN

Time 8

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Example – Snapshot Isolation (SI)

In the example below you can see that at Time 4 the data reader (Connection 2) does not get blocked even though there is already an exclusive lock on the object. SQL Server provides data from the version store to the data reader without blocking it for the transaction (Connection 1) to complete. At Time 6, you can see the data reader (Connection 2) gets the same data that it got at Time 4, as SI lets data readers read the last committed data before the transaction began (and not the last committed data before the statement began). Also notice that to use SI we need to explicitly specify it with athe SET command.

 

Connection 1

Connection 2

Time 1

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

UPDATE HumanResources.Employee

SET JobTitle = ‘Chief Executive Officer – CEO’

WHERE BusinessEntityID = 1

 

Time 3

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

 

Time 4

 

–No Blocking returns committed version before transaction starts

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 5

COMMIT TRAN

 

Time 6

 

–No Blocking returns committed version before this transaction starts

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Time 7

 

COMMIT TRAN

Time 8

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

SELECT JobTitle FROM HumanResources.Employee

WHERE BusinessEntityID = 1

Job Title

Conclusion

SQL Server 2005 and later versions have six different isolation levels. I briefly discussed the earlier four, which have been in SQL Server for a long time, and discussed in detail the two new snapshot based isolation levels. I also talked about the pros and cons of using this and how they differ from each other.

In conclusion, I would like to reiterate, these new snapshot based isolation levels are great as they provide better concurrency but it comes at a cost. Please ensure that you do a  thorough study before using this feature and consider the size and load on tempdb, especially in the case of SI.

Resources

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Isolation Levels in the Database Engine

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles