Optimistic Concurrency

Locking pessimistically a record in database could be a solution at times but mostly is itself a problem

In the multiuser environment we often have to lock some data for a single user and make sure no other user accesses and changes that data at the same time. Such situations may arise when same record (a row in a table) is accessed by more than one users to make changes because the second user may end up over writing the changes of the first user. Let’s consider the following example

From an online order receiving queue, different sales person can pick an order for processing. When an order is received its status is New. A sales person reviews an order using a Review form within our app, marks its status as processing, adds some comments and Saves it. The order is assigned to that sales person.

Let say an order with id 123987 is received

Select OrderId, AssginedTo, ClientDetails, Status, Comments from Orders where OrderId =123987

    class Order
    {
        public int OrderId { get; set; }

        public string AssignedTo { get; set; }

        public string ClientDetails { get; set; }

        public string Status { get; set; }

        public string Comments { get; set; }
    }

For saving the information entered by sales person, back to the database we execute this command..

Update Orders 
  Set Assigned = [LoggedInSalesPerson], Status = 'Processing', Comments = '[Comments Entered]' 
  where OrderId = [objOrder.OrderId]

Note: While building the command we will substitute [objOrder.OrderId] with objOrder.OrderId. We are not focusing on building the command here :)

Let see the following scenario

Sales Person A sees a New order and picks for reviewing using the order review form. Before the Person A save his changes, Sales Person B sees the same New order in the queue so he also starts reviewing the order.

Now if you observe the situation closely, you will see a problem. When Person A commits/saves his changes the state of Order in database would be

After few moments Person B who was also reviewing the same order, saves his changes. The state of the order becomes

Update Orders 
  Set Assigned = [LoggedInSalesPerson], Status = 'Processing', Comments = '[Comments Entered]' 
  where OrderId = [objOrder.OrderId] 
        and AssignedTo = [objOrder.AssignedTo] 
        and Status = [objOrder.Status] 
        and Comments = [objOrder.Comments]

Note: While building the command we will substitute [objOrder.OrderId] with objOrder.OrderId and likewise. We are not focusing on building the command here :)

If you worried about too many conditions in the where clause, we can also use a timestamp column in the table which always gets updated with any change to the row. Row versioning is an alternate to the timestamp provided lately. If we have a timestamp column in the above table

Our update command would be simpler

Update Orders 
  Set Assigned = [LoggedInSalesPerson], Status = 'Processing', Comments = '[Comments Entered]' 
  where OrderId = [objOrder.OrderId] 
      and TimeStamp = [objOrder.TimeStamp]

This makes sure when SalesPerson A commits his changes, they only get saved to the database if the state of the hasn’t changed since the order data was read from database for him. For our above scenario, Person A’s changes would be saved but when Person B would try to save his changes, following conditions would evaluate to false for him AssignedTo = [objOrder.AssignedTo] and Status = [objOrder.Status] and Comments = [objOrder.Comments] since the order state has changed since he read from database. In this case we will show an alert to Person B “Sorry someone else has already picked this order”.

Locking mechanism can also be used where we apply lock to a record in database when one person starts working on that record. This approach has its down sides like what if a person holds an order for longer period and then cancels that at the end. In this case no other sales person would be able to pick that order for that period.