Geek asks and answers

Transaction retry error

I have a C # window service that speaks to multiple databases on an MS SQL server. It is multi-threaded and has many functions, each of which has a long list of database operations, each of which performs its own transaction. So a typical function is like

    public void DoSomeDBWork()
    {
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
        {
            DatabaseUpdate1();
            DatabaseUpdate2();
            DatabaseUpdate3();
            DatabaseUpdate4();
            DatabaseUpdate5();

            DatabaseUpdate6();

        }
    }

. : - # DatabaseUpdate , ? , DatabaseUpdate6() , 3 3 , "DatabaseUpdates 1 to 5" , ? , .

+3
4

.

, . using, .. TransactionScope, , . , , , - , . , , , , , . , , , . "" , db, (, ), , . , , " ", "read-update-write", , "". .

, , , - , : TransactionScope() . ReadCommitted, .

: ? - , , .

, RequiresNew, , 99% , . , , , , Required .

+10

, . , , , , "idempotent" ( , , . , , " ", , . ..., -, , MySql ,

, , . void return, Action() MSSQL , , 'my'

  • , :

    //

    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);
        }


  •     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);
    }
}

:

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

, -: howerver SQL Server, , ( 1205) . , , .

, , update6.

, NOLOCK, .

- . , , . ar .

, (-, )

1 2 a/r.
1 , 2. 2 , . . , .

- , . ", ".

. MSDN SQL Server

+1

SQL , . , - DatabaseUpdate*(), .

. - , , . NOLOCK... ... , , . , , NOLOCK, .

There are two ways to handle deadlocks that I use. Or, immediately restart the transaction from the very beginning when you find a failure. Or you can read your variables before using them and execute them later. Secondly, it is something like a resource swamp and significantly reduces productivity, so it cannot be used to work with large volumes.

+1
source

More articles:


All Articles