Handling RowVersion for optimistic concurrency during update

[RaringCoder]

Hi,

The database we are operating against uses optimistic concurrency via the MS SQL TIMESTAMP column type. This derails using Update without some complete faff of custom SQL to handle the situation.

I have forked the repo and done a first-pass implementation of supporting a new RowVersion attribute here: https://github.com/RaringCoder/Dapper

It is bundled in with some aesthetic changes, an update to how property caching works, and some code de-duplication.

The current implementation simply adds the version to the WHERE clause on update, resulting in nothing updated if the version differs. This does what I need, crudely. RowVersionAttribute can be applied to any columns, but so far I only support one. Column type doesn't matter. It is automatically excluded from inserts (possibly overridden by Write(true) if the database doesn't handle version for you?).

Before I go for a full PR, I want some advice on how better to handle a concurrency collision other than simply not updating any rows (the current implementation). My prevailing theory is something akin to how Insert currently works in that, per adaptor, we do a query to select any database-generated Ids. We could do something similar and select a flag out when checking if the version changed:

SELECT COUNT(1) AS VersionHasChanged FROM Table WHERE Key = @key AND Version <> @version

I will be using my fork locally to make progress on the app I'm working on, but I'd like to get this baked in to Contrib properly.

[NickCraver]

I wouldn't do a PR for this on top of current, but instead take a look at DapperLib/Dapper#722 and pitch this into the mix. I think somehow integrating this into a Column attribute would be the best approach - thoughts?

[giorgiozoppi]

Does Dapper support optimistic lock out of the box?

[RaringCoder]

Yeah I'd be happy to bundle it. The challenge as I see it isn't so much how to decorate the types, it's how to transparently handle the optimistic checks for inserts, deletes, and updates, across each underlying provider. In the end I didn't need this as we have a heavily normalised data model (not under control of our app) so life is easier with something like EF Core for performing mutating operations based on an object graph.

[waynebloss]

This article shows different optimistic concurrency strategies that are used in NHibernate.

In summary:

• The dirty method: Check if the target field values changed before updating a row.
• The all method: Check if any field values have changed before updating a row.
• The versioned method: Use an int, timestamp/rowversion or datetime field to track if a row changed.

Using a datetime field for versioning is less safe, but if you are doing any type of auditing you will have one anyway - however you may not wish to use it for locking.

In my own databases, I prefer to use an int version column because it is portable to any database and simpler to serialize to JSON and other formats. Then if I plan on doing any type of auditing, I have a separate datetime field for created and modified time.

Off-topic: I also use a separate int or tinyint column to track row-status (0: created, 1: deleted, 2: archive) if doing soft-deletes. Alternatively, you can put all of the auditing columns in a separate history table but if you ever want to display created/changed time, it's better to just put them in the target table (and the history table if you have one IMO).

[grahambunce]

Can I just ask... does dapper support any optimistic concurrency at all ? I don't care if it's an int version, string etag, SQL Server Row Version, timestamp.... just anything to trap this scenario?

I thought it did through Contrib Update but it doesn't appear to.

@RaringCoder Assuming Dapper doesn't do this, then really I think the only way to handle this is to throw an exception. It's up to the caller to decide how to handle this... usually they would just try again with the new state as a starter

[waynebloss]

@grahambunce Dapper was built in a way that makes it really easy to extend yourself with this type of functionality. Originally, Dapper was not even published as a stand-alone library. Instead it was just another *.cs file that you included in your project.

Here's what I do for my projects where I want a more flexible Dapper experience: Instead of using Dapper straight from NuGet, I create my own Class Library project called Dapper. Then I clone the Dapper repo into a temporary location and copy the files that I want into my project. (SqlBuilder.cs, SqlMapper.cs, SqlMapperAsync.cs, SqlMapperExtensions.cs and SqlMapperExtensionsAsync.cs) In project properties->Build I type ASYNC in as a conditional compilation symbol, required by Dapper code to enable async methods. Now the library is ready to use.

So now to add the functionality that you want, it's going to take maybe a couple lines of code changes to the Update method. The code there is just building an UPDATE statement with a StringBuilder and all you have to do is add a new condition with a known column name. Maybe something like this:

if (foundVersionProperty) {
    adapter.AppendColumnNameEqualsValue(sb, MY_VERSION_COLNAME);
}

[waynebloss]

The article I linked to above shows how your UPDATE statement should look when you're using an int Version field to track changes in a table:

UPDATE People
SET
    Version = 2,
    Name = 'other'
WHERE
    Id = 1
    AND Version = 1

So, in addition to the condition column added at the end - you'll have to add a few more lines of code to actually increment your Version column in the SET portion of your UPDATE statement.

[waynebloss]

A cleaner method of doing this would be to write your own UPDATE extension that does what you want and don't change any Dapper code, so that you can continue to get updates from this repo easily.

However, then you will have to duplicate a bunch of code from SqlMapperExtensions.cs.

So yet another option would be to still create your own Dapper project with a copy of the source files and just add your own cs file to that project.

[grahambunce]

@waynebloss thanks for the quick update and suggestions. I'm very familiar with NHibernate, but tried Dapper for something faster. I was surprised that optimistic checking isn't in Dapper or Dapper Contrib out of the box. In the end, I've copied the Dapper Contrib C# to my solution and hacked it in. Really not very elegant but I'm on a time-box here