Handling concurrency issues for asp.net C# / SQL Server application

Question

How can I handle concurrency issues for an asp.net 4.5 web application (VS2012 and C#, SQL Server)

For example:
My scenario is a user might have a chance that is never able to process a queue item. See the following:

1. User A opens the queue item (a), review it carefully
2. User B opens the queue item (a), processes it without reviewing
3. User A decides to process (a), but it's locked, so jumps to item (b)
4. User C opens the queue item (b), processes it immediately
5. User A decides to process (b), but it's locked again
6. the same thing happens forever towards user A

That will not be ideal for the user A, even though the percentage of this possibility could be really small

I am using transactions at the C# code level as well as at the SQL Server stored procedure level. Also I make use of ADO.net to communicate with database

My questions are:

• do you think creating transactions will take care of concurrency issues ?
• I am guessing ADO.net has built in concurrency prevention in place? Am I correct ?
• do I need to do anything else to prevent concurrency issues other than creating transactions and using ADO.net?

Answer 1

In short: You may resolve concurrency issues by using traditional method called timestamp column.

The timestamp column of a table can be used to determine whether any value in the table row has changed since the last time the table was read. This article describes a way to use the timestamp column of a table for optimistic concurrency control in Microsoft SQL Server 2005.

To get more detailed explanation of this approach, follow this article

• How to use the timestamp column of a table for optimistic concurrency control in SQL Server 2005.

In addition, you may also look at step-by-step implementation guidelines of Implementing Optimistic Concurrency with SQL Timestamps.