Gathering some info on using Snapshot Isolation levels in SQL statements…
When Snapshot isolation is enabled on a database, versions of data are kept in tempdb whilst transactions are running in order to much reduce readers & writers from blocking each other.
However, if Read Committed Snapshot (RCSI) option is Off then statements wont use this info by default.
This means most transactions run in the default isolation level (Read Committed) unless otherwise specified:
SELECT
case transaction_isolation_level
when 0 then ‘Unspecified’
when 1 then ‘ReadUncomitted’
when 2 then ‘ReadCommitted’
when 3 then ‘Repeatable’
when 4 then ‘Serializable’
when 5 then ‘Snapshot’
end as transaction_isolation_level
,*
FROM sys.dm_exec_sessions
Usage
Traditionally, developers reach for the ‘with (no lock)’ table hint when they want to exec a long-running select statement on a busy table without causing, or waiting for, locks.
This uses the ‘Read Uncomitted’ isolation level and allows dirty reads of uncommitted updates.
Overall, its fine for quick & dirty results, but far from ideal for results that you want to act upon with a degree of confidence as you can get incorrect results from updates that get rolled back, or duplicate data from page splits
Using Snapshot isolation lets you query without the problems due to ‘read Uncommitted’ and with the benefits of increased concurrency. It allows data to be read consistently as it was at the start of the transaction, as previous versions of rows at that time are snapshotted in tempdb whilst updates take place.
In order to get consistency over a batch of reads from separate tables then you should wrap the statements in a transaction, even if you are only reading the data!
If you should need to prevent anyone from updating any table whilst you are running the transaction, then you can hint ‘with (UPDLOCK, ROWLOCK)’
If using Snapshot isolation when updating data, you need to be careful. The default ‘Read committed’ is like checking out a file in source control so that no one else can change it, Snapshot is like branching & merging. You can get update conflicts, or you can get results that are just different to those you would get normally. Needs consideration & testing.
example of conflict error, under Read Committed one transaction would block the other.
Msg 3960, Level 16, State 2, Line 15 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.tbl’ directly or indirectly in database ‘yourdbname’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
Pooled Connections
One thing to note is that Isolation levels can persist across statements in a pooled connection! So, if you specify a change to the default level then make sure to set back to default at the end of the statement to avoid inadvertently using snapshot level on a query where no level has been specified.
Table Hints
If you add table hints within a query eg with (nolock) then this will override the level set at statement level – so, be sure and remove these hints if you want to use snapshot isolation.
Conflict with Temp Tables
If you see an error message like this:
“Snapshot isolation transaction failed in database ‘tempdb’ because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.”
it can have a couple of sources: firstly if you are rebuilding indexes or actually changing the source tables with a DDL statement, or less obviously, if the statement includes a temporary table with a primary key. Havent found a way around this apart from skipping the Primary key/index.
References
My MSSQL snippets 