Unable to complete transaction; Lock wait timeout exceeded; try restarting transaction

Question

When I try to run the code below:

    $conBud = Propel::getConnection(MyTestBudgetPeer::DATABASE_NAME); // DATABASE_NAME = 'Budget'
    $conBud->beginTransaction();        
    $conIn = Propel::getConnection(MyTestInvoicePeer::DATABASE_NAME);  // DATABASE_NAME = 'Invoice'
    $conIn->beginTransaction();
    $idcl = '1235';
    try
    {
      // Do db udpates related to database Budget (here around 15 tables and 500 data rows are update)
       // budExModel is a table, primary id from this table is used to update InvoiceTest Table below
       $idtest = $budExModel->save($conBud);
       ...
      // Code to Update one table for database Invoice (only one table)
      // Create a Criteria object that will select the correct rows from the database
        $selectCriteria = new Criteria();
        $selectCriteria->add(InvoiceTestPeer::IDCL, $idcl, Criteria::EQUAL);            
        $selectCriteria->setDbName(InvoiceTestPeer::DATABASE_NAME);
        // Create a Criteria object includes the value you want to set
        $updateCriteria = new Criteria();
        $updateCriteria->add(InvoiceTestPeer::IDTEST, $idtest);
        // Execute the query
        BasePeer::doUpdate($selectCriteria, $updateCriteria, $conIn);

        $conBud->commit();
        $conIn->commit();           
    } catch (Exception $e)
    {
        $conBud->rollBack();
        $conIn->rollBack(); 
    }

I get error: ["Unable to execute UPDATE statement [UPDATEinvoice_testSETIDTEST=:p1 WHERE invoice_test.IDCL=:p2 ] [wrapped: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction]

Lock wait timeout exceeded; try restarting transaction

Error I am getting is for the table/db which has lesser data and only processes for one table.

Is this not allowed for mysql?

I already changed innodb_lock_wait_timeout and tried restarting mysql so they are not an option.

Edit: Here IDTEST I am trying to udpate for table invoice_test is an fk from Table Budget_test from database Budget.

Something like this- UPDATE invoice_test SET invoice_test.IDTEST=21 WHERE invoice_test.IDCL=2 — ro ko, Aug 09 '13 at 02:49
It's quite small, with only one row of data. Yes there is an index on IDCL. — ro ko, Aug 09 '13 at 02:55
For what it's worth http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded — ficuscr, Aug 09 '13 at 03:33
@ficuscr I already did try it. I also mentioned it in my question above that setting innodb_lock_wait_timeout is not an option. — ro ko, Aug 09 '13 at 04:11
All lock timeout I've experienced was due to a bad design of the query inducing in a deadlock (https://en.wikipedia.org/wiki/Deadlock), use @ficuscr suggest to find where the deadlock is, or at least **post the lock status** — Ivan Buttinoni, Aug 09 '13 at 06:16
lock wait timeout != deadlock -- MySQL will detect and rollback most deadlocks immediately, I would definitely not suspect a deadlock in the first place. A more common cause is a long-running query holding a lock on the table. Yes, please post the lock status (`SHOW ENGINE INNODB STATUS`) in order to find which concurrent transaction locks the table/row. — RandomSeed, Aug 09 '13 at 13:02

score 2 · Accepted Answer · answered Dec 02 '13 at 02:54

It seems that the reason behind the error was foreign key constraint on idtest.

Here $idtest is primary_key of newly saved row from table bud_ex; This retrieved from last_insert_id, this was the same id that is trying to be used in incoice_test table. Problem here is, I was trying to use $idtest, but the connection/transaction wasn't committed hence when trying to use this id, it threw an fk constraint error which in return lock time out exceeded.

To get this to work I had to run a query to set foreign key checks as false for invoice database.

set foreign_key_checks = 0;

Along with this I made certain few changes to the php code to make the try catch block more concrete.

$con1->beginTransaction();
try
{
    // Do stuff
    $con2->beginTransaction();
    try
    {
        // Do stuff
        $con2->commitTransaction();
    }
    catch (Exception $e)
    {
        $con2->rollbackTransaction();
        throw $e;
    }
    try
    {
        $con1->commitTransaction();
    }
    catch (Exception $e)
    {
        // Oops $con2 was already committed, we need to manually revert operations done with $con2
        throw $e;
    }
}
catch (Exception $e)
{
    $con1->rollbackTransaction();
    throw $e;
}

Unable to complete transaction; Lock wait timeout exceeded; try restarting transaction

1 Answers1