If you have an application that is used by more than one user and it stores its data in a shared database, you have to deal with concurrent actions of your users on the same data. This means for instance that if two users are updating account information for client X at the same time the application must decide which user’s changes should be stored. There are two models for concurrency: optimistic and pessimistic concurrency.

Optimistic concurrency

With optimistic concurrency no locking mechanisms are used. The idea is that the chances are small that users are editing the same data at the same time. So why bother with an resource expensive mechanism like locking? Because it could happen. An application must have some way of detecting this.

ADO.Net helps a developer out by offering support for the optimistic concurrency model. It tracks changes to data it is updating by including the original data in the where clause of the update statement. If the data was not changed one row was updated, contrary to zero rows updated rows. A DBConcurrencyException is throw in the latter case. A simple but elaborate mechanism.

A disadvantage of optimistic concurrency is that the user that first updates the data wins. All other users loose: maybe they had been working for ten minutes on the data and now they cannot save the changes!

Pessimistic concurrency

Pessimistic concurrency works different. It uses locks to make it impossible for users to change other users data. Of course the idea is that the chance that two or more users are updating the same data is small. So the chances are slim also that the lock bothers other users. But locks do affect scalability.

How can an application support pessimistic concurrency? A developer could implement it him-/herself. ADO.Net does help here. That is a difficult job. What about death locks for instance? Luckily databases help us out. Most databases support different kinds of locking and transactions. For pessimistic concurrency to work however the lock must be sustained for the whole period a user is working on the data. So what happens when this user goes for coffee or goes home. Too bad for everybody else? It is a big disadvantage. Isn’t it?

The conclusion

Looking at the the advances and disadvantages of both concurrency models I think is best to use optimistic concurrency as the main mechanism. Pessimistic concurrency should be used when changes to data must be ACID. But both methods can and generally are used by applications at the same time.