jLaw jLaw - 4 months ago 48
SQL Question

Setting CommandTimeout to solve lock wait timeout exceeded try restarting transaction

I have an error like this


lock wait timeout exceeded try restarting transaction


Maybe I didn't understand it. But I've a solution if I set CommandTimeout=1000 or something higher. I didn't try it in production yet. But I'd like to hear any opinion on this.

// 40 lines of command.Parameters here
command.Parameters.AddWithValue("sample1", sam1);
command.Parameters.AddWithValue("sample2", sam2);
command.Parameters.AddWithValue("sample3", sam2);
try
{
command.ExecuteNonQuery();

}
catch (MySqlException mex)
{

Answer

I was receiving "lock wait timeout exceeded try restarting transaction" intermittently. Then I started wrapping everything in transactions and I stopped receiving those errors. This should prevent table locks from remaining after the query is executed.

(Assuming "conn" is a MySqlConnection, "iLevel" is the isolation level you want to use, and "query" contains your query as a string)

int rowCount = 0; // In case you want the number of rows affected

try
{
    if (conn.State != ConnectionState.Open)
        conn.Open();

    MySqlCommand command = new MySqlCommand(query, conn);   

    using(var transaction = conn.BeginTransaction(iLevel))
    {   
        command.Transaction = transaction;                 
        command.CommandTimeout = int.MaxValue;

        // Set parameters etc...

        try
        {
            rowCount = command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch(Exception ex)
        {
            transaction.Rollback();         
            // Handle query exception...            
        }
    }
}
catch(Exception ex)
{
    // Handle general exception...
}
Comments