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: ,

Tuesday, 9 May 2006

Snapshot Isolation Part I

posted by Stefan Delmarco

Our investigation into snapshot isolation will be split into a number of parts. We'll firstly introduce the basic concept of what snapshot isolation means and how it can be used in its most basic form. Then we'll have a look at the advanced use of snapshot isolation as a full-blown transaction isolation level.

At its core, the SQL Server engine shares many similarities with a source code version control system. In fact, many of the terms used in snapshot isolation level have direct counterparts with applications like Rational's ClearCase version control system. In ClearCase, every file in the repository has an associated version number. When developers wants to make changes to a file, they first need to 'check it out'. This prevents any other developer from being able to make any changes to that file while it is being modified. In reserved mode no other developer can check the file out till the file has been checked back in. This is the same behaviour as SQL Server 2000 and SQL Server 2005 without snapshot isolation. When a row is being inserted / updated / deleted, the row is essentially 'checked out' till the transaction that performed the 'check out' either commits or rolls back. No other connection can even look at the row till it is 'checked back in'. To change this behaviour TSQL allows you specify locking hints such as NOLOCK (look at the row anyway, even though the data may only be partly changed - be very wary of using this) or READPAST (pretend that the row isn't even there).

Back to the ClearCase analogy. Even though a file has been checked out, developers can still look at the previous version of a file. For example, if HelloWorld.cs was checked out at version 3, developers can still look at versions 1 through 2. Version 3 only becomes visible when the developer has checked HelloWorld.cs back in. Well, this is precisely what snapshot isolation does. SQL Server maintains a version history of all rows that are in the process of being created / changed / removed. Does this mean that you can have a look at all previous versions of a row since it was created? No. SQL Server only maintains the last version of a row while it is being modified. Where are these previous versions stored? Is my database size going to multiply? No, previous version of a row are transient and are stored in TempDb.

All that snapshot isolation is trying to do is to prevent readers from blocking writers. That is, if a reader is trying to SELECT a row that is currently being altered by another writer, SQL Server will return the last version of the row that was committed instead of forcing the reader to wait till the writer commits. No more blocking...most of the time anyway(read on!).

As mentioned earlier, there are two modes of operation with snapshot isolation. We'll be looking the most basic mode of operation, known as READ_COMMITTED_SNAPSHOT. All that we need to know at this stage is that this behaviour applies when the transaction is running under the READ COMMITTED transaction isolation level (as opposed to READ UNCOMMITTED, REPEATABLE READ or SERIALIZABLE). This mode of behaviour is best observed with an example.

create database DiscoverYukon;
go
use DiscoverYukon;
go

-- Enable snapshot isolation for read committed.
alter database DiscoverYukon
set read_committed_snapshot on;
go

-- Create a test table.
create table someTable (
    Id int not null primary key clustered,
    SomeData varchar(255));
go

READ_COMMITTED_SNAPSHOT needs to be enabled at the database level. The default in the Model database (the template used whenever creating a new database) has READ_COMMITTED_SNAPSHOT set to OFF.

We'll dig into snapshot isolation using two connections. On the first connection, execute the following TSQL to insert a new record into the test table. We wrap this in a transaction so that we can interact with it from another connection:

-- Connection #1...
begin transaction
insert someTable (Id, Somedata)
values (1, 'FirstVersion');

On the other connection we simulate a user trying to read the same row:

-- Connection #2...
select * from someTable;

This query immediately comes back with no records! In SQL Server 2000 the SELECT would have blocked till connection #1 committed (try it out!). But, what if connection #2 tries to insert a record with the same Id. What if we added some 'insert Id 1 if it does not exist'-type logic?

-- Connection #2...
if not exists (
    select top 1 1
    from someTable
    where id = 1) begin

    insert someTable (Id, Somedata)
    values (1, 'FirstVersion');
end

When running this query you'll notice that it has reverted back to the SQL Server 2000 behaviour. It blocks till connection #1 commits. However, when connection #1 commits something that may be unexpected happens. Connection #2's query fails with the following error:

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK__someTable__7C8480AE'. Cannot insert duplicate key in object 'dbo.someTable'.
The statement has been terminated.

Connection #2 was not blocking on the SELECT statement! That completed successfully. It was the INSERT that was blocking. SQL Server was not allowing a previous version of the row to be changed (in this case a row with the same Id to be created) even though snapshot isolation was enabled. What you read is not always what you change when snapshot isolation is in use. Therefore, care must be taken when trying to write this type of query. In this instance, the SELECT needs to use the new locking hint available in SQL Server 2005, namely READCOMMITTEDLOCK. This is not very intuitive as the existing lock hint is READCOMMITTED. The difference between these two is that READCOMMITTED will still honour the READ_COMMITTED_SNAPSHOT setting while READCOMMITTEDLOCK will not. READCOMMITTEDLOCK will not read previous versions of the row and will wait till it obtains a read lock on the actual row (it is oblivious to previous versions). Therefore the TSQL needs to be rewritten as:

-- Connection #2...
if not exists (
    select top 1 1
    from someTable with(readcommittedlock)
    where id = 1) begin

    insert someTable (Id, Somedata)
    values (1, 'FirstVersion');
end

In this case connection #2 will block on the SELECT till connection #1 commits. When it does, connection #2 completes normally and does not attempt to insert a duplicate row (the IF returns FALSE). Next we'll look at how UPDATEs are affected by snapshot isolation.

Labels: ,

Previous Posts