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
|
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
|
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
|
|
|
Time 4 |
|
–No Blocking returns committed version before statement starts BEGIN TRAN SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
|
Time 5 |
COMMIT TRAN |
|
|
Time 6 |
|
–No Blocking returns committed version before this statement starts SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
|
Time 7 |
|
COMMIT TRAN |
|
Time 8 |
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
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
|
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
|
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
|
|
|
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
|
|
Time 5 |
COMMIT TRAN |
|
|
Time 6 |
|
–No Blocking returns committed version before this transaction starts SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
|
Time 7 |
|
COMMIT TRAN |
|
Time 8 |
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
SELECT JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = 1
|
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)
