Database Optimistic locking support for MS SQL Server by hannesb

The easiest way to implement optimistic locking or Optimistic concurrency control on a MS SQL Server table is to use a column with data type ROWVERSION.

In this article, I will demonstrate how to add optimistic locking to the Northwind Orders table

Add a rowversion column to orders table

using FluentMigrator;

namespace Serene_3_3_15.Migrations.NorthwindDB
{
    [Migration(20180128152500)]
    public class NorthwindDB_20180128_152500_OrderRowVersion : AutoReversingMigration
    {
        public override void Up()
        {
            IfDatabase("SqlServer", "SqlServer2000", "SqlServerCe")
                .Execute.Sql("ALTER TABLE Orders ADD RowVersion rowversion NOT NULL");
        }
    }
}

Define an interface class for tables with optimistic locking support

namespace Serenity.Data
{
    public interface IRowVersionRow : IEntity
    {
        IField RowVersionField { get; }
    }
}

Add interface + rowversion column to OrderRow.cs

It's important to add the 'Calculated' field flag

...
    public sealed class OrderRow : Row, IIdRow, INameRow, IRowVersionRow
...
        [DisplayName("Row Version"), NotNull, SetFieldFlags(FieldFlags.Calculated)]
        public byte[] RowVersion
        {
            get { return Fields.RowVersion[this]; }
            set { Fields.RowVersion[this] = value; }
        }

        IField IRowVersionRow.RowVersionField
        {
            get { return Fields.RowVersion; }
        }
...
        public class RowFields : RowFieldsBase
        {
...
            public ByteArrayField RowVersion;
...

Add a modified SaveRequestHandler

using Serenity.Data;
using System;
using System.Data;
using System.Globalization;
using System.Linq;

namespace Serenity.Services
{
    public class SaveRequestHandlerWithRowVersion<TRow> : SaveRequestHandler<TRow, SaveRequest<TRow>, SaveResponse>
        where TRow : Row, IIdRow, IRowVersionRow, new()
    {
        protected override void LoadOldEntity()
        {
            // Normaly the rowversion field (with flag 'Calculated') will not be loaded from database
            // with GetFirst() -> explicitly select this field
            var rowVersionField = (Field)Row.RowVersionField;
            if (!PrepareQuery().Select(rowVersionField).GetFirst(Connection))
            {
                var idField = (Field)(Row.IdField);
                var id = Request.EntityId != null ?
                    idField.ConvertValue(Request.EntityId, CultureInfo.InvariantCulture)
                    : idField.AsObject(Row);

                throw DataValidation.EntityNotFoundError(Row, id);
            }
        }

        private Int64? RowVersionToInt64(TRow row)
        {
            var rowVersionField = (Field)Row.RowVersionField;
            byte[] rv = (byte[])rowVersionField.AsObject(row);
            if (rv == null)
            {
                return null;
            }
            Array.Reverse(rv);
            return BitConverter.ToInt64(rv, 0);
        }

        protected override void ExecuteSave()
        {
            if (IsUpdate)
            {
                if (Row.IsAnyFieldAssigned)
                {
                    var idField = (Field)Row.IdField;
                    var rowVersionField = (Field)Row.RowVersionField;

                    // For now, I'm too lazy to implement updates on id field, I don't need this
                    if (idField.IndexCompare(Old, Row) != 0)
                    {
                        throw new Exception(LocalText.Get("Validation.IdUpdateNotSupported"));
                    }
                    var update = new SqlUpdate(Row.Table);
                    update.Set(Row);
                    // get the rowversion value that was sent to the form on load
                    Int64? formRowVersion = RowVersionToInt64(Row);
                    if (formRowVersion == null)
                    {
                        throw new Exception(LocalText.Get("Validation.RowVersionFieldEmpty"));
                    }
                    update.Where(
                        idField == new ValueCriteria(idField.AsObject(Old)) &
                        rowVersionField == new ValueCriteria(formRowVersion)
                        );
                    try
                    {
                        update.Execute(Connection, ExpectedRows.One);
                    }
                    catch (InvalidOperationException ex)
                    {
                        // if the update failed, throw a specific exception if the rowversions don't match
                        Int64? rowVersionIdDb = RowVersionToInt64(Old);
                        if (rowVersionIdDb != formRowVersion)
                        {
                            throw new Exception(LocalText.Get("Validation.RowVersionFieldChanged"));
                        }
                        else
                        {
                            throw ex;
                        }
                    }
                    Response.EntityId = idField.AsObject(Row);
                    InvalidateCacheOnCommit();
                }
            }
            else
            {
                base.ExecuteSave();
            }
        }
    }
}

Use the modified save handler in OrderRepository.cs,

...
    public class OrderRepository
...
        private class MySaveHandler : SaveRequestHandlerWithRowVersion<MyRow> { }
...

Add some local texts

using Serenity;

namespace Serene_3_3_15
{
    public static partial class Texts
    {
        public static partial class Validation
        {
            public static LocalText RowVersionFieldEmpty = "SaveRequestHandlerWithRowVersion: RowVersionField is empty! " +
                "Possible cause: Not included in form as (hidden) field.";
            public static LocalText IdUpdateNotSupported = "SaveRequestHandlerWithRowVersion: Updates of id field is not supported!";
            public static LocalText RowVersionFieldChanged = "The record has been changed by another user! " +
                "You have to close and reload the form without saving to see the changes!";
        }
    }
}

Include rowversion field as hidden field in orders form

...
    public class OrderForm
    {
        [Tab("General")]
        [Category("Order")]
        public String CustomerID { get; set; }
        [Hidden]
        public byte[] RowVersion { get; set; }
...

Thats it. To see how it works, open the web application twice, an load an arbitrary order with same id in each instance. Now change something in instance A, and save the changes. Try to do the same in instance B. You will get an error message and cannot save any changes.

Notes

• This does not (yet) work with detail rows on a MasterDetailRelation, because detail rows are handled by the SaveRequestHandler of the master row. With the right application design, this shouldn't be a problem though.
• The same concept should be valid for other db servers than MS SQL Server, as long as they support a data type with similar functionality as ROWVERSION.