TransactionScope and Optimistic concurrency

Question

I would like to use optimistic concurrency with TransactionScope. Here's the code I come up so far:

var options = new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted};
using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
{
    using (var connection = new SqlConnection(_connectionString))
    {
        // ... execute some sql code here

        // bump up version
        var version = connection.ExecuteScalar<DateTime>(@"
            DECLARE @version datetime2 = SYSUTCDATETIME();
            UPDATE [Something].[Test]
                SET [Version] = @version
                WHERE Id = @Id
            SELECT @version
        ", new {Id = id});

        // ... execute more sql code here

        // check if version has not changed since bump up
        // NOTE: version is global for the whole application, not per row basis
        var newVersion = connection.ExecuteScalar<DateTime>("SELECT MAX([Version]) FROM [Something].[Test]");
        if (newVersion == version) scope.Complete(); // looks fine, mark as completed
    }
} // what about changes between scope.Complete() and this line?

Unfortunately this code has one serious issue. Between version check and transaction commit there might be some change in the database. It's a standard time of check to time of use bug. The only way I can see to resolve it is to execute version check and transaction commit as a single command.

Is it possible to execute some SQL code along with transaction commit using TransactionScope? If no then what other solution could be used?

EDIT1: Version needs to be per application, not per row.

EDIT2: I could use serializable isolation level, but it is not an option due to performance issues this would cause.

Answer 1

Unfortunately this code has one serious issue. Between version check and transaction commit there might be some change in the database.

This is simply not true. The default constructor of TransactionSope, as in the code you posted, uses the Serializable isolation level. While this is arguably a problem, it does have the side effect of preventing any modification to any row you queried. It is pessimistic concurrency control.

You are right that you should use optimistic concurrency control, though. You need to use a TransactionScope constructor that accepts TransactionOptions and pass in the option to use a more decent isolation level, eg. read committed. As for the row version, use a simple int that you increment with each write in the app.

UPDATE [Something].[Test]
 SET ..., [Version] = @new_version
 OUTPUT Inserted.Id
 WHERE Id = @Id AND [Version] = @old_version;

@old_version is the version you found on the record when you query it. @new_version is @old_version+1. If the row was modified after you read it then the WHERE will not find it and your result will be an empty set, so you know you have to read, refresh and try again (a conflict occurred). This is a well known optimistic control scheme.

Note though that optimistic concurrency control makes more sense where the read and the write span two different transactions (eg. read in T1, display to user form, then write in T2). When the read and the write occur in the same transaction then you better leave it to the engine. I would simply use snapshot isolation level which solves the problem out-of-the-box.