Fotia Ltd

Fotia Ltd

Monday, 29 May 2006

Snapshot Isolation Part II

posted by Stefan Delmarco

Previously we started exploring the new row versioning / snapshot isolation features in SQL Server 2005. We saw how writers no longer block readers when the READ_COMMITTED_SNAPSHOT database option has been set. The rule of thumb is that read committed snapshot will be used when a shared (read) lock is being requested. Exclusive or update locks will never use row versioning. Depending on the logic of the TSQL, sometimes it is necessary to drop out of snapshot isolation and ensure that the row being read is current. However, bear in mind that any code which forces SQL Server to serialise access to a resource will become a contention hotspot.

In this instalment we'll continue our investigation into snapshot isolation and dig into its more advanced usage in the form of the snapshot transaction isolation level (i.e. read committed snapshot versus snapshot transaction isolation) . Whereas read committed snapshot was very specific in terms of only applying to shared locks, snapshot transaction isolation places a context around the entire transaction, affecting all locks. With read committed snapshot the latest version of a row was read by all connections as soon as the owning transaction committed. When a transaction utilising snapshot transaction isolation level begins, SQL Server notes when the transaction began relative to all other transactions executing in the database at that point-in-time. SQL Server uses this marker to determine which version of a row the transaction will read for its entire duration. This behaviour is best demonstrated with an example.

The following TSQL creates a database, enables snapshot isolation level, creates a table and adds some data:

create database DiscoverYukon;
go
alter database DiscoverYukon
set allow_snapshot_isolation on;
go

use DiscoverYukon;
create table someTable (
    id int not null primary key clustered,
    someData varchar(100));
go

insert someTable (id, someData)
select 1, 'One'
union all
select 2, 'Two'
union all
select 3, 'Three';
go

Again, we'll need two separate transactions. On connection #1 we start a transaction and update the table:

-- Connection #1...
use DiscoverYukon;
go

set transaction isolation level snapshot;
begin transaction;

update someTable
set someData = 'Three.One'
where id = 3;
go

Note the most obvious difference is the presence of SET TRANSACTION ISOLATION LEVEL. This option has always been present in SQL Server. It governs how transaction locks will be treated for this connection. However, previously the only possible values were READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. SNAPSHOT is new to SQL Server 2005. Setting the transaction isolation to snapshot has to be done explicitly, either on the connection or in the TSQL text . On connection #2 we now try read the row that was updated in connection #1. On connection #2 execute:

-- Connection #2...
use DiscoverYukon;
go

set transaction isolation level snapshot;
begin transaction;

select *
from someTable
where id = 3;

id someData
---- -----------
3 Three

As you probably guessed, this returns the previous version of the row, before connection #1 updated it. However, now let's try and update the row from connection #2.

-- Connection #2...
update someTable
set someData = 'Three.Two'
where id = 3;

Same as before, this update blocks as SQL Server will ensure that we only update the current version of a row. Let's commit the transaction on connection #1:

-- Connection #1...
commit transaction;

The query on connection #2 now completes with the following error:

Msg 3960, Level 16, State 2, Line 2 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.someTable' directly or indirectly in database 'DiscoverYukon' 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.

We got this error because when connection #2's transaction began, SQL Server noted that the row was already being updated by connection #1. With this knowledge, SQL Server ensured that connection #2 would not be allowed to modify any data that was being versioned when connection #2's transaction began. The reason for this behaviour will become apparent if we re-run the sample again. On connection #1 execute:

-- Connection #1...
set transaction isolation level snapshot;
begin transaction;

update someTable
set someData = 'Two.One'
where id = 2;

On connection #2 execute:

-- Connection #2...
set transaction isolation level snapshot;
begin transaction;

select *
from someTable
where id = 2;

id someData
---- -----------
2 Two

Again, this returns the versioned row as it was when connection #1 began its transaction (the update from the first sample). Now let's commit connection #1's transaction and re-read this row:

-- Connection #1...
commit transaction;

And on connection #2:

-- Connection #2...
select *
from someTable
where id = 2;

id someData
---- -----------
2 Two

Create another separate connection #3 and run the same SELECT:

-- Connection #3...
use DiscoverYukon;
go

select *
from someTable
where id = 2;

id someData
---- -----------
2 Two.One

Now it should become apparent what's going on. Connection #2's SELECT returns the same versioned row even though connection #1 has committed its transaction. The latest version of the row has someData set to 'Two.One' as can also be witnessed by the data returned by connection #3. SQL Server will ensure that any rows that are versioned when a transaction begins will only be available as versioned rows for the duration of that transaction. The transaction is essentially forbidden to update any rows that were under the influence of another transaction when the transaction began. This also includes not being able to modify any rows that are changed by another transaction after the transaction began.

For OLTP systems I can see read committed snapshot playing a far bigger role than snapshot isolation due to the update conflict complication with snapshot transaction isolation level. However, snapshot isolation will have a role to play when the probability of causing conflicts is low. For example, if a query needs to generate a report and must ensure that the data was consistent, at a point in time, when the report was run, snapshot transaction isolation level will be beneficial (the report does not update any data). In this manner it can act as a very lightweight alternative to REPEATABLE READ or SERIALIZABLE transaction isolation levels which typically pay a very high concurrency price. An acceptable pattern to follow when using snapshot isolation is to automatically retry in the event of an update conflict. Many large database systems already have data access layers that retry in the event of a deadlock.

As a taster for the next instalment, here is a very useful query using a DMV. It returns how long a row has been versioned for. Big numbers mean long running transactions (which are always bad!). Start a couple of transaction using either of the two snapshot isolation levels and observe the output. Use this to help find the long running culprits!

select session_id as [spid],
    elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by 2 desc;

Labels: ,

Previous Posts