Row Versions: What’s that all about?

Intro

I did some work with a customer recently to understand what the different Isolation Levels they could use in SQL Server 2005 meant to their system. Of course, we’ve all seen the standard isolation levels that have been around for ages, but SQL 2005 introduces two new options;

· Read Committed Isolation using Row Versioning

· Snapshot Isolation

Both of these new isolation levels use Row Versioning to function, and both sound very similar initially, but there are subtle differences.

Background

Basically what these isolation levels do is keep track of changes to data rows, and store the history of changes in tempdb for as long as they are needed. When another client comes along that needs to read the same piece of data, the database engine may choose to serve up a previous version from tempdb.

Why is this useful? Because even if the row that I want to read is locked by a client that is currently writing to it, I can still read the previous row version.

For more info on how all this works check out the documentation online. Pay particular attention to “Choosing Row Versioning Based Isolation Levels”.

Enabling READ_COMMITTED_SNAPSHOT

To enable this type of Row Versioning, you must apply a change to the database itself;

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

This now means that any connection to the database that asks to use the old Read Committed isolation level will actually use the new Row Versioning based implementation. For example, if the following command is issued against a database with this setting turned on, it will use Row Versioning automatically (emphasis is mine, not SQL servers J);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

SELECT * FROM MyTable

GO

Therefore, exercise some caution when enabling this setting on existing systems.

Enabling Snapshot Isolation

Snapshot Isolation is a brand new isolation level. To use it you must enable it at the database level;

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

And then explicitly use the new isolation level in SQL (emphasis is mine again!);

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

GO

SELECT * FROM MyTable

GO

So what’s the difference?

There are quite a few little differences, which are detailed in the documentation I linked to earlier, but there is one really key one that isn’t immediately obvious and people often skim read without taking in the implications. To quote the docs;

· “Read committed isolation using row versioning provides statement-level read consistency”

· “Snapshot isolation provides transaction-level read consistency”

What this means, is that when you are using Read Committed isolation with Row Versioning you will receive the latest version of each row available when your statement started to execute. However, when using Snapshot Isolation, you will receive the latest version of each row available when the current transaction started to execute.

Consider the following diagram;

This shows a “control” transaction running concurrently with one of five other experimental transactions, with time running vertically. These should demonstrate the differences. What we are interested in is whether the select statement in each of our experimental transactions returns Row 1 including the changes applied in our control transaction, or Row 1 as it was before the control transaction updated it.

These results are illustrated in the following table;

· “Old” implies we receive a row version that predates the control transaction’s update.

· “New” implies we receive a row version that includes the control transaction’s update.

I hope that illustrates the point. The only experiment that retrieved the latest version of the row when running under snapshot isolation was the one when the transaction started after the control transaction had committed. Read committed, however, fetches the latest committed row version irrespective of when the current transaction started.

Summary

To summarise, I’ve re-phrased the statements from the docs that we saw earlier to try and make them even more explicit;

Read Committed with Row Versioning will return the latest committed row version when the current statement started to execute.

Snapshot Isolation will return the latest committed row version when the current transaction started to execute.