CODEWITHSUNDEEP
c#mysql
jLaw
jLaw

Reputation: 302

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)
{

Upvotes: 1

Views: 1594

Answers (2)

AS7K
AS7K

Reputation: 457

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...
}

Upvotes: 1

profesor79
profesor79

Reputation: 9473

You could try (just for testing purposes) set transaction isolation level = "READ COMMITTED" and if this fails try set to "READ UNCOMMITTED" MySql reference check for dead lock:

"SHOW ENGINE INNODB STATUS" from the MySQL Command line client (not a query browser) will give you info on deadlocks.

Deadlocks can also be caused by uncommitted transactions (usually program bugs) and the person who is running the uncommitted transaction will not see the problem as they will be working fine (through their data will not be committed). Quote from here

Upvotes: 1

Related Questions