Implementing concurrency with timestamp (row versioning)

Having just written up some basic concurrency handling in the services I’m creating I thought I might post a few things I came across on the way.

1. timestamp != date or time

The timestamp data type in SQL server is not a date or time. In fact, it’s not even related. It’s a binary field that increments each time a row is changed. This means it’s really just what it’s synonym – rowversion suggests it is. A row version. If you want to use it, it maps to the .Net SqlBinary type. More on this later…

2. Error severity level

I found getting a description of the error severity levels notoriously difficult. Using the SQL RaiseError function with error level 1 causes the error to be swallowed as informational within the action, rather than raising as an exception to the framework. This can be quite frustrating at first but I found a good guide to error handling in SQL server here. This guide gave me just about everything I needed to know and I highly recommend it.

3. timestamp == SqlBinary

As mentioned, the timestamp type maps to the SqlBinary type (see here for a compleye type mapping list). If you don’t want to pass the Sql type around all over the place, use the .Value property to obtain the byte array that makes up the timestamp and keep it in your object instead. You could make this field read only as well, but considering most of the time if the user changes this field (unless they know exactly how many row changes between now and their update reaching the database) their change will cause the update to be rejected anyway.

You’ll notice in the MSDN list timestamp doesn’t map to a specific .Net framework type. Use rowversion synoym instead which maps to byte[]. I’ve read a few blogs and articles that recommend using rowversion instead, so that if the concepts diverge in future editions behaviour remains consistent. I think this is a good idea, and that the mappings chart provides a solid reason to trust these recommendations.

So there they are, a few little hints that I picked up on my way to implementing some kind of concurrency control through row versioning.

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s