One in a series of posts about Microsoft’s In-Memory OLTP Functionality

In the previous post in this series, I discussed three validation errors that are unique to Hekaton and their common causes:

• Error 41302 – Generated when two concurrent sessions update the same record.
• Error 41303 – Generated when a concurrent update causes a repeatable read failure.
• Error 41325 – Generated when a concurrent insert causes a serializable failure.

There is also error 41301 that I will address later in this series.

Applications that access databases containing In-Memory tables need to be aware of these errors and be able to respond gracefully when they occur.

There is one error condition that has been around for a long time in SQL Server that closely parallels these three errors, and that is the deadlock (error 1205). Deadlocks, however, are largely not accounted for in error handling because they tend to be rare, and can largely be avoided by good coding practices (specifically, consistent order of access to tables and other resources, when at all possible). On the other hand, the Hekaton errors will, in practice, be more commonplace, and I consider them to be “normal” events in practice, especially as the throughput on the system becomes heavy.

The appropriate response, in most situations, is to retry the operation when this error is raised. Microsoft has an example of handling errors in stored procedures, but applications also need to address these errors.

Here is a stub of a data access class that demonstrates how this can be done.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HekatonErrorTrapping
{
    class Dal : IDisposable
    {
        private SqlConnection _connection;

        public Dal()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @".";
            sb.InitialCatalog = "Hekaton";
            sb.IntegratedSecurity = true;

            _connection = new SqlConnection(sb.ToString());
            _connection.Open();
        }

        public SqlTransaction StartTransaction()
        {
            return _connection.BeginTransaction();
        }

        public void UpdateProductUnitPrice(int productId, decimal newPrice, SqlTransaction transaction)
        {
            string sql = @"update dbo.Product with (snapshot) set UnitPrice = @NewPrice where ProductID = @ProductId;";
            SqlParameter idParameter = new SqlParameter("@ProductId", productId);
            SqlParameter priceParameter = new SqlParameter("@NewPrice", newPrice);

            using (SqlCommand command = new SqlCommand(sql, _connection, transaction))
            {
                command.Parameters.Add(idParameter);
                command.Parameters.Add(priceParameter);

                int retryCount = 5;
                bool isComplete = false;
                while (isComplete == false)
                {
                    try
                    {
                        command.ExecuteNonQuery();
                        isComplete = true;
                    }
                    catch (SqlException ex)
                    {
                        switch (ex.Number)
                        {
                            // 41302: Updated a record that has been updated since the txn started.
                            case 41302:

                            // 41305: Repeatable read validation failure.
                            case 41305:

                            // 41325: Serializable validation failure.
                            case 41325:

                            // 41301: Commit dependency on a transaction that aborted.
                            case 41301:

                            // 1205: Deadlock (not applicable to in-memory tables, but same principles apply).
                            case 1205:
                                retryCount--;
                                if (retryCount <= 0)
                                {
                                    throw;
                                }

                                // Take a very brief pause -- mostly needed in cases of very high write contention.
                                System.Threading.Thread.Sleep(1);
                                break;

                            default:
                                throw;
                        }
                    }
                }
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
        }
    }
}

And here is an example of how to call this code.

using (Dal dal = new Dal())
{
    using (SqlTransaction transaction = dal.StartTransaction())
    {
        dal.UpdateProductUnitPrice(productId, newPrice, transaction);
        transaction.Commit();
    }
}

Now if one of these errors is generated during the update, the system will pause for one millisecond (just in case there is a lot of write contention going on; this pause may need to be longer on some systems), and retry the operation up to five times. (Of course, these values should be configurable and not hard-coded.)

There is one HUGE caveat to this, however. When one of these errors is thrown, the entire transaction is rolled back. In this simple example, the transaction is just a single update statement, but if the transaction spans a dozen statements, they all get rolled back.

This means that at the very least the application needs to be aware that the full transaction will go away, and it should wrap transaction appropriately. Better yet, the application will know how to replay the full transaction up to the point of failure and will gracefully retry the operation a given number of times before giving up.