Deadlock retry in a transaction

I have C# window service which talks to multiple databases on a MS SQL server. It is multi threaded and has many functions each with long list of database operations, each of these functions run under their own transaction. So a typical function is like

    public void DoSomeDBWork()
开发者_开发知识库    {
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
        {
            DatabaseUpdate1();
            DatabaseUpdate2();
            DatabaseUpdate3();
            DatabaseUpdate4();
            DatabaseUpdate5();

            DatabaseUpdate6();

        }
    }

Under heavy load we are experiencing deadlocks. My question is, if I write some C# code to automatically resubmit the DatabaseUpdate in case of a deadlock will it hold back resources for uncommitted operations? for example , if a deadlock exception occurs in DatabaseUpdate6() and i retry it 3 times with a wait of 3 seconds, during this time will all the uncommitted operations "DatabaseUpdates 1 to 5" hold on to their resources which might further increase chances of more deadlocks ? Is it even a good practice to retry in case of deadlocks.

---

You are barking up the wrong tree.

Deadlock means the entire transaction scope is undone. Depending on your application, you may be able to restart from the using block, ie. a new TransactionScope, but this is very very very unlikely to be correct. The reason you are seeing a deadlock is that someone else has changed data that you were changing too. Since most of these updated are applying the update to a value previously read from the database, the deadlock is a clear indication that whatever you've read was changed. So applying your updates w/o reading again is going to overwrite whatever was changed by the other transaction, thus causing lost updates. This is why deadlock can almost never be 'automatically' retried, the new data has to be reload from the db, if user action was involved (eg. form edit) then user has to be notified and has to re-validate the changes, and only then the update can be tried again. Only certain type of automatic processing actions can be retires, but they are never retried as in 'try to write again', but they always act in a loop of 'read-update-write' and deadlocks will cause the loop to try again, and since they always start with 'read'. They are automatically self-correcting.

That being said, your code deadlocks most likely because of abusing the serialization isolation level when not required: using new TransactionScope() Considered Harmful. You must overwrite the transaction options to use the ReadCommitted isolation level, serializable is almost never required and is a guaranteed way to achieve deadlocks.

Second issue is Why does serialization deadlock? It deadlocks because of table scans, which indicate you don't have proper indexes in place for your reads and your updates.

Last issue is that you use RequiresNew, which is again, 99% of the cases, incorrect. Unless you have real deep understanding of what's going on and a bulletproof case for requiring a standalone transaction, you should always use Required and enlist in the encompassing transaction of the caller.

---

This doesn't cover everything in your question but on the subject of retries. The idea of retrying transactions, database or not, is dangerous and you should not read this if the word "idempotent" means nothing to you (frankly, i don't know enough about it either but my management had the final word and off I went to write in retries for deadlocks. I spoke to a couple of the smartest guys I know in this area and they all came back to me with "BAD BAD" so I don't feel good about committing that source. disclaimer aside, had to do it so may as well make it fun..., here's something I wrote recently to retry MySql deadlocks a specified number of times before throwing and returning

Using anonymous method you only have to have one receiver that can dynamically handle method signatures and generic return types. You'll also need a similar one for void return that will just need to use Action() For MSSQL it'll look pretty much identical I think, minus the 'my'

1. The handler that does the retry:

   //

   private T AttemptActionReturnObject<T>(Func<T> action)
           {
               var attemptCount = 0;
   
               do
               {
                   attemptCount++;
                   try
                   {
                       return action();
                   }
                   catch (MySqlException ex)
                   {
                       if (attemptCount <= DB_DEADLOCK_RETRY_COUNT)
                       {
                           switch (ex.Number)
                           {
                               case 1205: //(ER_LOCK_WAIT_TIMEOUT) Lock wait timeout exceeded
                               case 1213: //(ER_LOCK_DEADLOCK) Deadlock found when trying to get lock
                                   Thread.Sleep(attemptCount*1000);
                                   break;
                               default:
                                   throw;
                           }
                       }
                       else
                       {
                           throw;
                       }
                   }
               } while (true);
           }
   

2. Wrap your method call with delegate or lambda

       public int ExecuteNonQuery(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters)
   {
       try
       {
           return AttemptActionReturnObject( () => MySqlHelper.ExecuteNonQuery(connection, commandText, commandParameters) );
       }
       catch (Exception ex)
       {
           throw new Exception(ex.ToString() + " For SQL Statement:" + commandText);
       }
   }
   

it may also look like this:

return AttemptActionReturnObject(delegate { return MySqlHelper.ExecuteNonQuery(connection, commandText, commandParameters); });

---

When SQL detects a deadlock, it kills one thread and reports an error. If your thread is killed it automatically rolls back any uncommitted transactions - in your case ALL of the DatabaseUpdate*() that were already ran during this most recent transaction.

The ways to deal with this depend entirely on your environment. If you have something like a control table, or a string table, which is not updated, but frequently read. You can use NOLOCK... cue kicking and screaming... It is actually quite useful when you aren't worried about time or transaction sensitive information. However when you are dealing with volatile or stateful information you cannot use NOLOCK because it will lead to unexpected behavior.

There are two ways to handle deadlocks that I use. Either straight up restart the transaction from the beginning when you detect a failure. Or you can read in your variables before you use them, and execute afterwards. The second is something of a resource hog and sees significant decrease in performance so it should not be used for high-volume functionality.

---

I think different database servers may respond to a deadlock differently, howerver with SQL Server if two transactions are deadlocked one is elected by the server to as the deadlock victim (error 1205) and that transaction is rolled back. This means of course that the other transaction is able to proceed.

If you're the deadlock victim, you will have to redo all your database updates, not just update6.

In response to comments about avoiding deadlocks with hints such as NOLOCK, I would strongly recommand against it.

Deadlocks are simply a fact of life. Imagine, two users each submitting a manual journal entry into an accounting system The first entry does a credit of the bank account & a debit of the receivables. The second entry does a debit of the ar & credit bank.

Now imagine both transactions play at the same time (something that rarely if ever happens in testing)

transaction 1 locks the bank account transaction 2 locks the a/r account.
transactions 1 tries to lock receivables and blocks waiting on transaction 2. transaction 2 tries to lock the bank and a deadlock is automatically and instantly detected. one of the transactions is elected as a victim of a deadlock and is rolled back. The other transaction proceeds as if nothing happened.

Deadlocks are a reality and the way to respond to them is quite straight forward. "please hang up and try your call again."

See MSDN for more information on Handling Deadlocks with SQL Server