What pattern to employ for locking SQL Server record while editing its data

Question

I'm having trouble developing the right strategy for opening connections, beginning transactions, and committing/rolling back/closing connections. The context is an ASP.NET WebForms application. A client can bring up a record to make edits, and I'd like other clients to be locked out of updating that record during the edit operation.

Other clients should be able to read the last committed version of that record. The approach I'm using right now for an edit operation is to open a connection and begin a transaction at IsolationLevel.RepeatableRead, which is doing what I want in terms of locking.

However, I'm not immediately closing the connection...instead, I keep the connection and transaction open while the client is actively editing the values in the record so that I hold the lock. Once the edits are done and the client either commits or rolls back the changes, then I close the connection.

Here's the bones of the class that represents a record in the database:

public class DBRecord : IDisposable
{
    private OleDbTransaction tran; // holds the open transaction
    private Dictionary<string, object> values = new Dictionary<string, object>();
    private bool disposedValue = false;

    public DBRecord (bool forUpdate) {
        OleDbConnection conn = new OleDbConnection(<connection string>);

        try {
            conn.Open();
            tran = conn.BeginTransaction (forUpdate ? IsolationLevel.RepeatableRead : IsolationLevel.ReadCommitted);
            OleDbCommand comm = new OleDbCommand("SET XACT_ABORT ON", conn, tran);
            comm.ExecuteNonQuery();
            comm = new OleDbCommand("SELECT * FROM " + DBTable + " WHERE KEY = ?", conn, tran);
            comm.Parameters.Add(new OleDbParameter("@Key", Key));

            using (OleDbDataReader rdr = comm.ExecuteReader())
            {
                while (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        mvoValues.Add(rdr.GetName(i), rdr.GetValue(i));
                    }
                }
            }
        } catch {
            conn.Close();
            throw;
        }

        if (!forUpdate) {
            // don't need to keep the record locked
            tran.Commit();
            conn.Close();
        }
    }

    public UpdateField(string field, object newValue) {
        // this is only called if the object was instantiated with forUpdate true
        OleDbCommand comm = new OleDbCommand("UPDATE " + DBTable + " SET " + field + " = ? WHERE " + KeyField + " = ?", tran.Connection, tran);
        comm.Parameters.Add(new OleDbParameter("@Value", newValue));
        comm.Parameters.Add(new OleDbParameter("@Key", Key));

        try {
            oCommand.ExecuteNonQuery();
        } catch {
            OleDbConnection conn = tran.Connection;
            tran.Rollback();
            conn.Close();
        }
    }

    public void Commit()
    {
        OleDbConnection conn = tran.Connection;
        tran.Commit();
        conn.Close();
    }

    public void Rollback()
    {
        OleDbConnection conn = tran.Connection;
        tran.Rollback();
        conn.Close();
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                if ((tran != null) && (tran.Connection != null))
                {
                    try
                    {
                        OleDbConnection conn = tran.Connection;
                        /// release rowlocks we acquired at instantiation
                        tran.Rollback();
                        conn.Close();
                    }
                    catch (Exception)
                    {
                        // since  we're disposing of the object at this point, there's not much 
                        // we can do about a rollback failure, so silently ignore exceptions
                    }
                }
            }

            disposedValue = true;
        }
    }
}

The database has "Allow Snapshot Isolation" and "Is Read Committed Snapshot On" set to true, and the connection string specifies MARS Enabled is true.

It's pretty clear to me that this isn't the right approach:

• It goes against everything I've read about connections--open as late as possible and close as quickly as possible. However, I don't know of another way to keep the record locked from updates while a client is making edits.

• The bigger problem I'm having is when I terminate the browser while in the middle of an edit. Right now I'm in development, so I'm using VS 2015 and IIS Express. When I close the browser, the IIS Express process closes also. It appears that my Dispose() is never being called, because the processes remain open in my SQL Server Express instance. Clearly I need to somehow guarantee that the connections will close regardless of whether the server stops.

In a previous question related to databases I received a suggestion to cache a copy of the record data in local storage, track updates on that, then execute a new transaction when ready to commit the changes. That would definitely make my connections shorter and the update operation more atomic, but the record would not be locked. The solution there might be to create a "I'm being updated" field that a client could test-and-set at the beginning of an edit operation, but that seems like a hack, especially given that the database engine provides mechanisms for locking.

Intuitively what I'm trying to do here seems like a common enough use case that there should be a pattern already, but I'm apparently not asking Google the right questions because all I'm getting in my searches is how to employ a using block. Can anyone point me to the right way to do this? Thanks.

Answer 1

The correct pattern name is pessimistic offline locking for what you're looking for, https://dzone.com/articles/practical-php-patterns/practical-php-patterns-13 , which is what you were referring to about having a column with "I'm being updated".

You can review other solutions for locking, https://petermeinl.wordpress.com/2011/03/14/db-concurrency-control-patterns-for-applications/ .

It's generally recommended that you use optimistic locking instead of pessimistic locking for multiple reasons. It does not require you to update the row prior to editing, it does not require you to leave a connection open like some of the pessimistic locking solutions, and it does not require implementing lock timeouts. It does however have the side effect that it's possible when the user goes to save their changes that they will be prompted if they want to overwrite or merge with changes that have happened since they began their edit.

These are not really the same as the locking built into SQL Server, although some of SQL Server's features can be used to help implement them.