Snapshot transaction isolation – optimistic concurrency made easy?

In a previous post we've looked at an example of how the principles of optimistic concurrency should be corrected in order to prevent possible harmful consequences that might come from uninhibited optimism. We've seen how to utilise a rowversion column to track data modifications at row level, and we've reached into the core of how to leverage data modification activities in a multi-tiered solution. No sophisticated locking and detection mechanisms were used in achieving our goal (and no principles of information management were violated in the process). We've also mentioned the fact that in SQL Server 2005 there is an alternative that further simplifies the implementation of the corrected principles of optimistic concurrency.

The subject of this post is how snapshot isolation (SI) introduced in SQL 2005 can be used to implement optimistic concurrency in data management.

SI in SQL Server 2005 is implemented at database level, it has two variations, each with its own characteristics. The foundations of SI lie in the row versioning framework which is an integral part of the database engine. More on the subject is, of course, available in Books Online.

There are two new database settings with the purpose of controlling SI:

• ALLOW_SNAPSHOT_ISOLATION – by default the setting is off, and needs to be turned on before SI can be used. The setting is transaction-scoped: the snapshot is created using data that was committed before the snapshot transaction started; and
• READ_COMMITTED_SNAPSHOT – also off by default; it allows the snapshots to be used with the (default) read committed isolation level – combining the essential characteristics of snapshot and read committed isolation levels. This setting is statement-scoped: the snapshot is created using data that was committed before each statement started.

Both settings can be modified using the ALTER DATABASE DDL statement. Which brings us to the first consideration – enabling and disabling SI. In accordance with the new SQL 2005 approach to database-level and server-level changes aimed at achieving as much as possible without the need to interrupt data access these two settings can be changed on a live system.

When enabling the READ_COMMITTED_SNAPSHOT setting there must be no other open connections – restricting database use (i.e. single-user mode) is not required, the operation simply waits for all other open connections to close. Which could be really long if for example there are many SSMS connections open at the time. ;)

When enabling the ALLOW_SNAPSHOT_ISOLATION setting special rules apply in order not to block users (follow the link above). The process waits for pending transactions to complete.

SI uses tempdb to store row versions, so make sure tempdb is large enough and maintained regularly.

The situation

Let's imagine two conflicting transactions – two different users attempting to modify the same row of data at the same time. If by any chance you have trouble imagining that, read this post and all will be revealed. ;)

Bottom line: by utilising rowversion columns and adapting data modification logic accordingly conflicting modifications can be prevented – the user is warned of potential conflicts. This should be handled on the database level.

Optimistic concurrency with the snapshot transaction isolation level

Snapshot transaction isolation means that each user who started a snapshot transaction has uninhibited read-access to the last committed version of the row. In snapshot transactions readers are not blocked by writers. Nonetheless, conflicting modifications are not possible – the engine itself prevents it!

The row versioning framework checks whether the row being updated has already been updated in a concurrent transaction after the snapshot transaction was started.

If this is true then the snapshot transaction will be terminated and an exception will occur:

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

What does this mean? Well, when using snapshot transaction isolation the database engine will automatically track data modification conflicts, prevent them and warn us when they occur. In other words – thanks to the row version framework the use of an extra rowversion column in tables and the necessary logic to support it is no longer needed, that is if the following conditions are met:

• Database compatibility level is 90 (or higher) – obviously;
• The ALLOW_SNAPSHOT_ISOLATION database setting is ON; and
• The transaction is started with the snapshot isolation level.

There are a few limitiations, though:

• Distributed transactions using SI are not supported;
• DDL operations against database objects may block SI transactions;
• Changing certain database setting will cause pending snapshot transactions to fail (e.g. setting the database to read-only, changing settings that lead to database recovery, etc.);
• Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.

Any other considerations? Cross-database transactions with the snapshot isolation level require all databases involved to have the ALLOW_SNAPSHOT_ISOLATION database setting turned on, unless the limitation is circumvened by using table hints. Attempts to violate this limitation will cause an exception:

Msg 3952, Level 16, State 1, Line 3
Snapshot isolation transaction failed accessing database '%database name%'
because snapshot isolation is not allowed in this database. Use ALTER
DATABASE to allow snapshot isolation.

To circumvene the SI limitation an appropriate table hint can be used when referencing tables in a database where SI is not allowed; e.g.:

set transaction isolation level snapshot

select <column list>
 from <database with SI ON>.<schema>.<table> SI_ON
  inner join <database with SI OFF>.<schema>.<table> SI_OFF with(readcommitted)
    on SI_OFF.<common key> = SI_ON.<common key>

A warning from Books Online:

Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <table_hint>, be used only as a last resort by experienced developers and database administrators.

Do SI limitations constitute such a case?

All things considered, using snapshot transaction isolation is a good way to implement optimistic concurrency in SQL 2005, as long as the limitations are taken into account and provided for. It's the simplest alternative, but not the only one.

Optimistic concurrency with the read committed transaction isolation level and snapshots

The key feature of SI is in the way data is read: all committed rows are available to any connection – readers are no longer blocked by writers. In business terms: data access (e.g. for reporting purposes) is instant and as accurate as possible (especially when considering the overly popular and just as questionable alternative: the nolock table hint). IMHO not blocking readers constitutes the principal benefit of the READ_COMMITTED_SNAPSHOT database setting.

Why? Again, there are a few limitations to consider, but the most critical one is the fact that data modification conflicts are not detected automatically by the database engine. This means that even with snapshots the read committed isolation level in SQL 2005 still requires additional logic in data management to properly support optimistic concurrency (e.g. rowversion & Co.) – however, with the prevailing benefit of readers not being blocked by writers.

There's another benefit: read committed transactions are not blocked by any DDL operations against database objects when READ_COMMITTED_SNAPSHOT is set. For instance: rebuilding indexes will cause snapshot transactions to fail, while read committed transactions will not be interrupted.

So, using the rowversion column when implementing optimistic concurrency remains a valid option in SQL Server 2005; even more so in situations where the limitations of the snapshot transaction isolation level rear their ugly head. However, if these aren't relevant to the case in hand then snapshot isolation prevails by being the least difficult of the two to implement.

Mental aggregation

Common facts
– read operations are not blocked by write operations;
– row versions are stored in tempdb.

	Conflict detection	Limitations	Snapshot taken
ALLOW_SNAPSHOT_ISOLATION	automatic (the transaction is aborted)	– DDL operations are fatal; – database recovery is fatal; – impossible to switch transaction isolation to snapshot from any other level; – cross-database queries require table hints if the setting is not turned on in all databases involved; – conflict detection cannot be disabled;	before transaction starts
READ_COMMITTED_SNAPSHOT	no	cannot be set for tempdb, msdb or master;	before each statement

Keeping an eye on things

How to check whether snapshot transaction isolation is allowed:

select sys.databases.snapshot_isolation_state
 ,sys.databases.snapshot_isolation_state_desc
 from sys.databases
 where (sys.databases.[name] = '<MyDatabase>')

How to check whether read committed transactions use snapshots:

select sys.databases.is_read_committed_snapshot_on
 from sys.databases
 where (sys.databases.[name] = '<MyDatabase>')

How to monitor resources related to snapshot isolation and row versioning?

Ten dynamic management views are available to help you monitor the activities of the row versioning framework in SQL 2005. Start here.

ML