Concurrency conflicts in web application can be quite troublesome. They occur when the same record in database is selected and then updated by more than one user. They can cause changes loss and be very frustrating for your users. In this post I describe a few solutions for this problem.

What is Concurrency Conflict?

Let’s say two users want to edit the same record (that in database has 2 columns – field1 and field2) in your application. So your app selects the same record from database and then displays it in some form. One user changes field1 and the other user changes field2. Next, first user submits data to database and later so does the second user. When the second user saves his changes, ‘his’ record has a field1 with the old value (not the value changed by first user). First user’s changes are overwritten!

 

How to fix it? Optimistic Concurrency vs Pessimistic Concurrency

There are 2 solutions for this problem. One of them helps us avoid the pain, the second one heals the wounds ;).

Optimistic Concurrency is a cure for wounds and a pat on user’s back. It means that we allow concurrency conflicts happen. But we also (want to) believe that it will not happen :). And if it happens anyway, we react on it in some manner. It’s supported in Entity Framework – you have got concurrency exceptions to handle, you can add a column of  rowversion type (or timestamp in older SQL Servers) to database table and so on… It’s probably a good moment to stop and come back to the subject in separate post!

Pessimistic Concurrency is a ‘seatbelt in your car’ approach – we assume that concurrency conflicts will happen and we believe they will happen often. It locks database’s record for update access and other users can only access the record as read-only or have to wait for a record to be ‘unlocked’. Programming an app with pessimistic concurrency approach can be more complicated and complex in managing because of deadlocks’ risk.

 

Let’s have a look at other features of both approaches.

Optimistic Concurrency approach

Pessimistic Concurrency approach

Allow concurrency conflict happens and if it happens, we react on it in some manner   Best solution when concurrency possibility is rather low   Doesn’t lock records – to ensure record wasn’t changed in time between select & submit operations, it checks row version   Simple in designing and programming   Suits best when database has a lot of records and not too many (relatively) users

Protects system from concurrency conflict so it will not happen   Best solution when there is a lot of updates and concurrency possibility is high   Locks records so record selected for update will not be updated meantime by another user   More complex in designing and managing the programming part (deadlocks’ risk)   Suits well when we have a table with relatively small amount of records but a lot of update operations . Often transaction rollback would be an ‘effort waste’ [1]

 

 

References:

[1] http://www.ibm.com/support/knowledgecenter/en/SSPK3V_6.5.0/com.ibm.swg.im.soliddb.sql.doc/doc/pessimistic.vs.optimistic.concurrency.control.html

Image by Matt Lee (downloaded from https://unsplash.com/)