Design for concurrency

May include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes

Most of this objective is covered in the previous post there’s simply too much overlapping in this part of the exam, as far as I can grab it. Anyway, there are some other interesting issues with locking and concurrency, so here’s a brief post on the remainders.

There are two data types related to concurrency – rowversion and timestamp. Rowversion is a binary data type which stores eight bytes, and increments automatically on every insert and update. Note that each table can have only one rowversion column, and you must give a name for it (you don’t have to specify a name for a timestamp value). Rowversion type is useful when you expecting multiple updates in roughly the same time, but I’ve stumbled into it when used LINQ to SQL along with WCF, too.

Timestamp is a synonym to rowversion, and MSDN advises to always use rowversion, so don’t be a renegade – use the right one.

SQL Server 2005 introduced a new isolation level, called READ_COMMITTED_SNAPSHOT. In order to use it, you need  to allow its usage, and turn it on – on a database level. The syntax is:

ALTER DATABASE Database name
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Database name
SET READ_COMMITED_SNAPSHOT ON

That’s the magic. This isolation level uses the tempdb system database to store transaction version information (and uses it a lot). It provides a consistent state of the database for everyone – the state when the transaction began (hence the name snapshot). During a snapshot transaction no locks are held, which lets other transactions complete without being blocked by each other, thus eliminating deadlocks.

Snapshot isolation level has many fans, but many enemies as well. For further information, dig around forums and MSDN.

One thought on “Design for concurrency

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s