locked
Concurrency violation while updating the DB by two different application RRS feed

  • Question

  • Hi,

    In my project I have two applications named DBManager and RTManager. Both applications read and update DB. Both apps have seperate connections to DB. The is single threaded app. While updating the DB from DBManager  I get following error

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    Here are my questions
    1] Is updating the DB by two processes is the real reason of this exception?
    2] Though two different application using different DB connections try to update DB can we still get Concurrency violation exception
    3] Using the global named mutex is valid solution to the problem?
    4] any other solution to this problem

    Thanks in advance

    • Moved by Chris Robinson- MSFT Wednesday, July 1, 2009 9:33 PM likely connection provider issue (From:ADO.NET DataSet)
    Wednesday, July 1, 2009 5:44 PM

Answers

  • > 1] Is updating the DB by two processes is the real reason of this exception?

    It could be due to an incorrectly written UPDATE statement, but you've probably ruled that out.  Normally, this is caused by two database connections attempting to update the same record at nearly the same time.

    > 2] Though two different application using different DB connections try to update DB can we still get Concurrency violation exception

    To get a picture of what is happening, consider the following:
    1.  Connection A reads the row into a DataSet.
    2.  Connection B reads the row into a DataSet.
    3.  Connection B updates the database.
    4.  Connection A's update to the database fails because A's row in the DataSet is no longer in sync with the database (B's update got ahead).

    3] Using the global named mutex is valid solution to the problem?

    The global mutex may be feasible if both applications are running on the same machine.  You need to hold the lock on the mutex during the entire read and write, as one block of code.

    4] any other solution to this problem

    If using SQL Server, you could perform the read and write in the same transaction.  Use the WITH (UPDLOCK) hint during the read to block the other connection from reading the row until your transaction completes.  WITH (UPDLOCK) informs SQL Server that the SELECT is with the intent to UPDATE.  As an alternative to UPDLOCK, there are also sp_getapplock and sp_setapplock.  These also require a transaction and work like the global mutex with the benefit that it is in the database server so is not limited to the single machine. Other database systems generally have similiar techniques; you need to take care not to replace a problem of concurrency violations with a problem of deadlocks, though.

    Note that the best solution for these kinds of problems is often to catch and recognize the exception, reread the data, and try the update again.  If both apps are updating the same rows too often, however, the repeated collisions may lead to performance problems in which case you'll need to reconsider a locking solution.
    Wednesday, July 1, 2009 10:37 PM