Understanding SQL Server Snapshot Transaction Isolation Level

by Granville Bonyata on September 30, 2012

By Nick Geiger

In search of a firmer understanding of how SQL Server 2008 treats transactions with the snapshot transaction isolation level (we’re thinking of using it to reduce locking issues), we tried some experiments. To perform the experiments, I created a database and a table with the following scripts.

create database SnapshotDB;

use SnapshotDB;

alter database SnapshotDB
set allow_snapshot_isolation on;

create table dbo.Number
Number nvarchar(1024),
PK_Number int primary key identity(1, 1) ); go

with TenNumbers as
select Number
from (values(1),
(10)) as Numbers(Number) ), ThousandNVarcharNumbers as (
select cast(row_number() over (order by n1.Number asc) as
nvarchar(10)) as Number
from TenNumbers n1
cross join TenNumbers n2
cross join TenNumbers n3
cross join (values(1), (2), (3)) n4(n)
insert dbo.Number(Number)
select replicate('0', 1024 - len(n.Number)) + n.Number as Number from ThousandNVarcharNumbers n;

One of the abilities that I knew I would need was the ability to see the current state of Version Store, a part of tempdb that enables SQL Server to provide the Snapshot transaction isolation level. When I found the System table sys.dm_os_performance_counters, I found that ability among some of the counters. The two counters (rows) that I cared about most in this were “Version Store Size (KB)” and “Free Space in tempdb (KB)”. The script I used to get these counters and some others follows.

— Version Store related counters.

select *
from sys.dm_os_performance_counters c
where c.counter_name like 'Version%'
or c.counter_name like 'Free Space in tempdb (KB)%';

I found that whenever a single row was updated or deleted, a set of row versions were generated in tempdb in the Version Store if they did not already exist there. If there was an active transaction that needed the row version because it had read from it or had caused that row version to be generated, the row version would not be removed from tempdb. About every minute, SQL Server would remove the row version sets that were no longer needed. This is the life of generated row versions.

When using the transaction isolation level snapshot, instead of issuing locks on rows, pages, or tables to ensure that only committed rows are read, the transaction will read the actual row when another transaction has not changed it and will read its generated row version in tempdb when another transaction has changed the row. Since the row version will be kept in tempdb while the transaction needs it, it will allow the transaction to read the rows as they were just before they were first read in the transaction. I would not state that this transaction isolation level is exactly like a “snapshot since the beginning of the transaction”, since rows inserted by other transactions after a transaction begins will be found by that transaction if it has not attempted to read it before but it is close to that.

I wanted to determine from these experiments what SQL Server did when tempdb got full and more row versions needed to be generated. To test this, I used SSMS to set the restricted file growth of Rows Data file of tempdb to 5 MB and updated more than enough rows to fill tempdb. I found that when the transaction updated under these conditions, the excess row versions were merely not generated, and no error was raised. However, when another transaction attempts to read row versions that ought to have been generated, the transaction raised error 3961 which states “Snapshot isolation transaction failed in database ‘SnapshotDB’ 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.”

For additional information, I found the following links to be useful:


Previous post:

Next post: