Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 14, 2012

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

By Arshad Ali

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM