SQL Server 2005 transaction isolation levels -- how to handle data concurrency through versioning.

When comparing RDBMS features one of the more discussed features is how the databaseserver handles concurrency; ie how do we make sure that data is handled in such a way thatwe get high performant throughput but also so users can not view uncommitted changes andso on.

In RDBMS systems there are mainly to different ways of solving this issue:

In this article, we will look at these two ways to handle concurrency issues and alsosee what new features SQL Server 2005 introduces.

Data locking

Locking of data has been SQL Server's preferred way of handling concurrency. While,for example, an update statement executes against some data, that data is being locked andno one else can read it (based upon isolation levels). Then, dependent upon how much datathat is affected, SQL Server can decide to use a row-lock, page lock or table lock.

Above, I mentioned isolation levels. The isolation level determines how long a read lockis being held (in SQL Server a write lock is always being held to the end of thetransaction). The default isolation level in SQL Server is Read Committed: a readoperation can only read committed data. If data is being updated while you read, that datais being locked and you won't be able to view the data until the transaction that updatesthe data has committed (or rolled back).

From a concurrency standpoint this is very good, you are ensured that you can only readcorrect data. From a throughput perspective it is not that good because your readoperation won't return until the write locks has been released.

In order to enhance the throughput other database vendors are using versioning insteadof locking.

Data Versioning

In a system using versioning, data is not being locked but instead read operationshappen against an older version of the data being manipulated. It is important to noticethat the read operation happens against an earlier committed version of the data, we arenot reading uncommitted data. Dependent upon isolation levels, the read operation theneither read the latest committed data or the data as it was when the read operationstarted.

Using versioning seems like the ideal solution; we can always read data (no locking),and we are always reading committed data! Yes, that is absolutely true. However,versioning doesn't come without a cost. First of all, when an update operation takes placeagainst the data, the data being touched is copied to a separate storage area. This incursa performance penalty, but also be aware of the impact of volume of data being copied;ie, do not use versioning if you are doing large batch updates. Read operations willalso incur a performance penalty as you potentially have to read through differentversions of the data.

Having looked at the principles behind locking and versioning, let's take a look atwhat is new in SQL Server 2005.

SQL Server 2005 and Versioning

For developers used to database servers using versioning, SQL Server's locking may seemlike a step back. In SQL Server 2005 therefore, Microsoft has included the ability to useversioning in addition to locking!

Versioning in SQL Server 2005 is implemented as two new isolation levels:

The difference between these two isolation levels is how reads are handled inside thesame transaction. For example, if you have a piece of T-SQL code looking like so:

use pubs
go
BEGIN TRAN

COMMIT TRAN

...and in between the first and second read from authors, the record we're reading havebeen updated, the behavior would be different. For read committed snapshot you would seethe last committed value, whereas for transaction isolation level snapshot the valuewould be as it was for the first read.

Having seen an example of this, the question is how do we enable these new isolationlevels (they are no on by default)? You are enabling them on a database by database level,and you do it through the ALTER DATABASE syntax.

Read committed snapshot is enabled like so:
use master;
go
ALTER DATABASE pubs
SET READ_COMMITTED_SNAPSHOT ON

Enabling read commited snapshot replaces the default read committed isolation levelwith read committed snapshot, so in order to use it you do not have to do anything inparticular after having enabled it.

Transaction isolation level snapshot is enabled like this:

use master;
go
ALTER DATABASE pubs
SET ALLOW_ISOLATION_LEVEL_SNAPSHOT ON

As opposed to read committed snapshot transaction isolation level snapshot has to beexplicitly enabled for the session or statement where you want to use it. The followingcode snippet shows how to enable it:

use pubs
go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN

COMMIT TRAN

Summary

Versioning in SQL Server 2005 gives the application developer new means to createapplications with great throughput for read operations. It also gives SQL Server the samecapabilities that competing database systems have had for quite a while.

Be aware, however, that versioning doesn't come without a cost. When enabling versioningin a database and, for example, updating records, the whole record will be copied toTempDb (which is where the version store is) plus an additional 14 bytes. In addition toincreased size of TempDb read operations will also be slower, as it has to read againstTempDb and potentially through quite a few versions unti it finds the version of therecord it wants.