Setting CommandTimeout to solve lock wait timeout exceeded try restarting transaction

Question

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

lock means that a row/column/table is locked - and that can be caused by another insert/update statement — fubo, Feb 16 '16 at 14:25
if this error appears every time, you should search for "deadlock" - but it's not possible to answer this without knowing the statement — fubo, Feb 16 '16 at 14:46
I too am having this issue...however increasing my CommandTimeout did not solve the issue. The CommandTimeout is different than the lock wait timeout. The "innodb_lock_wait_timeout" setting in my.ini governs what that timeout is, and the default is 50 seconds. From: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout — AS7K, Jun 13 '16 at 16:39

score 1 · Answer 1 · edited May 23 '17 at 10:28

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

AS7K · Accepted Answer · 2016-07-20T12:04:52.293

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

Also I should mention I passed in "iLevel" to the BeginTransaction function. This is a variable that holds the isolation level you want to use. — AS7K, Jul 20 '16 at 12:04

Setting CommandTimeout to solve lock wait timeout exceeded try restarting transaction

2 Answers2