Best practices in handling concurrency in ADO.net

In this article I’d like to present a discussion on concurrency handling, concurrency violations and the strategies involved to resolve concurrency conflicts.

Concurrency violations: when and why do they occur?

ADO.net can work in both connected and disconnected modes. Although the ability to work in disconnected modes has advantages, there are certain concerns too — concurrency violation is one amongst them. Concurrency handling may be defined as a technique using which you can detect and resolve conflicts that arise out of concurrent requests to the same resource. In essence, you can use this technique to maintain data integrity and data consistency when multiple users access the same resource concurrently. Concurrency violations can occur when you have interdependent transactions, i.e. transactions those are dependent on one another and they try to access the same resource.

Strategies for handling concurrency conflicts

Concurrency conflicts can be handled using one of these strategies: pessimistic concurrency control and optimistic concurrency control. Let’s now understand how each of these strategies work.

In the pessimistic concurrency handling strategy, a record in your database table will be made unavailable to the users from the time it was last fetched until the time it is updated in the database. So, when a particular record is being updated, all other concurrent updates on the same record will be put on hold until the current operation is complete and the control is relinquished back so that other concurrent operations can continue.

The optimistic concurrency mode operations on the “last saved wins” strategy — the last updated value is saved in the database. In other words, the last saved record, “wins.” Note that in the optimistic concurrency handling strategy, it is assumed that resource conflicts due to concurrent accesses to a shared resource are unlikely, but, not impossible. In this strategy, you need not check for concurrent updates to the same resource (i.e., a record in your database table) — the record is simply overwritten.

What happens when a concurrency violation occurs? Well, if a concurrency violation occurs you can the latest data is re-read from the database and the change is re-attempted. To check for concurrency violation, you would need to determine the changes to the record from the time it was last read by the application. In essence, for optimistic concurrency control to work your application should check the row version when an update operation is to be performed.

To handle concurrency conflicts and ensure data security, consistency and integrity in the connected mode of operation in ADO.net, you should take advantage of transactions. You can leverage the TransactionScope class present in the System.Transactions namespace for efficient transaction management when working with ADO.net.

The following code snippet illustrates how you can use this class to implement transaction scope for handling interdependent transactions and resolve concurrency conflicts. using (TransactionScope transactionScope = new TransactionScope()) { using (SqlConnection firstConnection = new SqlConnection( connectionString)) { //Some code } using (SqlConnection secondConnection = new SqlConnection(connectionString)) { //Some code } transactionScope.Complete(); }

The downside to this approach is that there is a performance overhead associated when using transactions as they require an open connection and tend to hold locks that may cause contention issues. So, you should ensure that your transaction blocks contain less code — they should be as short as possible.

If you would like to handle concurrency in the disconnected mode of ADO.net operation, you would need to check if the data being updated is the most recent one. If not, a concurrency violation is reported and the update or delete statement is aborted. To do this, you can have a TimeStamp column in each of your database tables. When you are trying to update a particular record, you can check whether the value of the TimeStamp column for a particular record has changed from the time the application last read the record from the database.

Best practices

You should ensure that the transaction blocks contain the minimal code and they are designed in such a way that the database connection is not kept open for a long period of time. You should avoid transactions that run for a long period of time and avoid transactions that need user input. You should also try to make proper utilization of isolation levels to reduce locking. It is not recommended to implement locks on the database tables or the records in the database tables to resolve concurrency conflicts in high data driven applications. The choice of the best strategy to be adopted for handling concurrency conflicts in your application depends on a few factors — you need to achieve a perfect balance between performance, scalability, security and data availability.

In my future posts here, I’ll discuss on how we can handling concurrency conflicts when working with LINQ to SQL and Entity Framework.