Deadlock management RRS feed

  • Question

  • Hello,

    I am experiencing deadlocks and would appreciate advice on how to debug them, defend against them and automagically handle them. The only information that I have been able to capture so far is from the exception raised "System.Data.SqlClient.SqlException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction".

    I have two processes which are updating the same records.

    The first process dispatches a message to an external interface and then does multiple database updates in a new TransactionScope().
    using (TransactionScope txn = new TransactionScope())
      * select one delivery record
      * delivery.status = submitted;

      * if (select notification returns a record) notification.attempt ++;

      * SubmitChanges()
      * txn.complete();

    The second process is receiving the acknowledgements from the external interface. When it receives the acknowledgement it also does multiple database updates in a new TransactionScope().
    using (TransactionScope txn = new TransactionScope())
      * select same delivery record
      * delivery.status = delivered;

      * select same notification
      * DeleteOnSubmit (notification);

      * SubmitChanges()
      * txn.complete();

    So the processes are a bit different but hit the same records in both tables. The external interface is responding quickly enough that my two processes are managing to deadlock at least one of the records.

    So the questions:

    1) Is it possible to detect that an SqlException is caused by a deadlock?

    2) How do I tell which records are deadlocked?

    3) Can I extend the period that the process waits for the deadlock to be cleared? (What is the default?)

    4) Is there anything else that I can do do defend against this situation arising?



    Tuesday, March 16, 2010 1:22 PM


  • To answer my own question ... I have succeed in resolving my problem and now have a better understanding of what to do to defend against deadlocks and also automatically resolve them.

    Because LINQ is designed only for optimistic locking and there is no way to mandate a pessimistic lock during the select, both processes were able to select and change both records before calling SubmitChanges. The deadlock only occurs when the SubmitChanges are called in unison.

    I made the following changes:

    1. Firstly I made the TransactionScope "Serializable". This may be the default behaviour in the parameterless TransactionScope constructor but the documentation isn't explicit.
    2. Next I added an earlier SubmitChanges() as soon as I modified the delivery record. This forces a pessimistic lock on that record until the end of the TransactionScope and forces the other process to wait until the lock is released.
    3. Next I wrapped the whole thing in a loop around the outer try/catch block, so when I detect an SqlException, I retry the transaction.

    I noted that a deadlock takes 4 to 6 seconds to resolve (I couldn't find any way to modify that) so it is better to defend against them rather than resolve them.

    One final observation: Watch out for LINQ's change tracker stopping SubmitChanges() doing any changes. My first attempt at modifying a record was to do field++ followed by field-- but the change tracker is clever enough to know that the end result is no change so doesn't lock the record.

    Can anyone tell me why LINQ doesn't allow locks to be taken during select? I can understand the preference for optimistic locking but it shouldn't be to the detriment of all functionality.

    • Marked as answer by John T. Angle Thursday, March 25, 2010 10:12 AM
    Tuesday, March 23, 2010 8:50 AM