Optimistic Versus Pessimistic Concurrency

for RuBoard

An old adage says: "If I can get something but it is tainted, it does me no good to get it in the first place."

Well, that's what managing concurrency is all about ”making sure you can update data along with the guarantee that what you are updating hasn't changed because someone else came along and updated it right out from under you. Let's first look at optimistic concurrency because it is the preferred approach in most .NET programming.

Optimistic concurrency allows for multiple users to read the same data row at the same time without having to lock it (for update). Then any one of the users can change (update) the data values at this point. An optimistic concurrency violation will occur when a user tries to update the previously read data, but another user has already updated it since the last time the first user read it. That can really be a disaster if not understood well or not handled properly.

What is so significant here is that by not locking the data resource (the data row) for update, the whole level of performance of your system is improved due to the major reduction of additional server resources needed to lock data. And, for an application to hold a lock on a data resource, it must also maintain a persistent connection to the database server that houses the data. Because this is not happening in optimistic concurrency (no persistent connection is needed and no data lock is being used), connections to the database server are available to handle many more clients in less time. This directly achieves the scalability and performance goals we mentioned earlier.

Looking back at Figure 14.1, let's describe optimistic concurrency in a multiuser situation with Client A and Web Client B:

1. Client A reads the Customer data row for customerID = 'ABC' at 9:14:32 AM. The data values are as follows :

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peach

2. Web Client B reads the same Customer data row at 9:14:34 AM. The data values are as follows:

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peach

3. Web Client B updates the ContactName value from "Peach" to "Donald Renato" at 9:14:40 AM. These updates are committed to the database in a single short transaction and Web Client B is finished. This update succeeds because the values in the database at the time of the update match the originally read values exactly. The data values in the database are now as follows:

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Donald Renato

4. Meanwhile, Client A updates the ContactName value from "Peach" (as they see it in their locally cached DataSet ) to the value of "Peter Johnson" at 9:14:42 AM. They then try to commit this change back to the database but have now encountered an optimistic concurrency violation. In other words, the values in the database no longer match the original values that Client A was expecting (or was working with). Client A must now decide whether they should make their update (overriding what Web Client B did) or cancel their current update.

In general, the optimistic concurrency approach is highly successful when the application's data has minimal contention situations (when the application's users rarely work on the same data at the same time) and when there are many, many users.

In contrast, pessimistic concurrency must lock rows at the data source to prevent other users from updating that data from under them. The lock must be held for the duration of their update intent time and will be released when they either (1) update the data and commit the changes or (2) just release the update intent. The issues here are enlisting the data source's lock manager to hold a lock on the intended data, keeping a persistent connection maintained during the entire intent and update time, and the extra coding that must establish explicit transactions and commit points. If we look at the same sequence of events described earlier, but utilizing a pessimistic concurrency approach, our example now changes:

1. Client A reads the Customer data row for customerID = 'ABC' at 9:14:32 AM. Must also "lock" this data for the length of time of their update intent. In addition, a persistent connection must be maintained with the data source in order to hold the lock for this user. The data values are as follows:

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peach

2. Web Client B reads the same Customer data row at 9:14:34 AM. The data can be read by others but not updated because of the lock being held on it. The data values are as follows:

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peach

3. Web Client B updates the ContactName value from "Peach" to "Donald Renato" at 9:14:40 AM. This update fails because the data is being locked by Client A. No changes are made to the data source. The data values in the database are still

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peach

4. Meanwhile, Client A updates the ContactName value from "Peach" to the value of "Peter Johnson" at 9:14:42 AM. They then try to commit this change back to the database and succeed (and their locks are released). The data values in the database are now

   CustomerID	CompanyName	ContactName
   ABC	ABC Company	Peter Johnson

Very different results from that of optimistic concurrency!

For the pessimistic concurrency approach, the cost of holding those locks, maintaining the persistent connections, and doing the extra coding needed is huge. The results may be what was desired, but at what price to scalability and performance? Using the pessimistic concurrency approach is much better suited for systems that have heavy contention for data, fewer users, and where the cost of protecting that data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

Remember, you are always at the mercy of the underlying database management system's locking granularity (row-level, page-level, table-level, key-range, and so forth). If the DBMS is using page-level (default) locking and if there are many data rows in a single page of a table that you are updating, you may be holding up others from updating their data even though you are not updating the same data. Their update will eventually succeed, but you have inadvertently slowed up that process. Good database design along with using the optimistic concurrency approach will usually minimize this situation.

for RuBoard