FAQ Item: What is the Concurrency Violation and how to handle it in ADO.NET? RRS feed

  • Question

  • What is the Concurrency Violation and how to handle it in ADO.NET?
    Sunday, June 20, 2010 3:12 PM


  • Concurrency Violation means that when multiple users attempt to modify data at the same time, one user's modifications from adversely may affect modifications from simultaneous users.


    The system of handling what happens in this situation is called concurrency control and we have the following ways of concurrency control.

    1.       Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.

    2.       Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.

    3.       "Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

    Pessimistic Concurrency:

    Pessimistic concurrency is typically used for two reasons. First, in some situations there is high contention for the same records. The cost of placing locks on the data is less than the cost of rolling back changes when concurrency conflicts occur. Pessimistic concurrency is also useful for situations where it is detrimental for the record to change during the course of a transaction. A good example is an inventory application. Consider a company representative checking inventory for a potential customer. You typically want to lock the record until an order is generated, which would generally flag the item with a status of ordered and remove it from available inventory. If no order is generated, the lock would be released so that other users checking inventory get an accurate count of available inventory.

    However, pessimistic concurrency control is not possible in a disconnected architecture. Connections are open only long enough to read the data or to update it, so locks cannot be sustained for long periods. Moreover, an application that holds onto locks for long periods is not scalable.


    Optimistic Concurrency:

    In optimistic concurrency, locks are set and held only while the database is being accessed. The locks prevent other users from attempting to update records at the same instant. The data is always available except for the exact moment that an update is taking place. For more information, see Using Optimistic Concurrency. (http://msdn.microsoft.com/en-us/library/aa0416cz(VS.80).aspx )

    When an update is attempted, the original version of a changed row is compared against the existing row in the database. If the two are different, the update fails with a concurrency error. It is up to you at that point to reconcile the two rows, using business logic that you create.


    Last in Wins:

    With "last in wins," no check of the original data is made and the update is simply written to the database. It is understood that the following scenario can occur:

    ·         User A fetches a record from the database.

    ·         User B fetches the same record from the database, modifies it, and writes the updated record back to the database

    ·         User A modifies the 'old' record and writes it back to the database.

    In the above scenario, the changes User B made were never seen by User A. Be sure that this situation is acceptable if you plan to use the "last in wins" approach of concurrency control.



    Related Resources:





    Related Threads,



    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 3:13 PM
    Sunday, June 20, 2010 3:13 PM