General error: 1205 Lock wait timeout exceeded on phalcon 2.0.13

Hi all,

We have a problem during DB saving on the API, here are the PHP log:

Error: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

0 [internal function]: PDOStatement->execute()

1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array)

2 [internal function]: Phalcon\Db\Adapter\Pdo->execute('UPDATE `OrderLi...', Array, Array)

3 [internal function]: Phalcon\Db\Adapter->update('OrderList', Array, Array, Array, Array)

4 [internal function]: Phalcon\Mvc\Model->_doLowUpdate(Object(Phalcon\Mvc\Model\MetaData\Memory), Object(Phalcon\Db\Adapter\Pdo\Mysql), 'OrderList')

5 .../models/BaseModel.php(16): Phalcon\Mvc\Model->save(NULL, NULL)

6 .../controllers/store/OrderListController.php(30320): ...\Models\BaseModel->save()

We have the following BaseModel extended by all models:

 abstract class BaseModel extends \Phalcon\Mvc\Model
 {
     public function save($data = null, $whiteList = null)
     {
         //Override save() method to handle possible relations recursion and cleanup related models bag after save.
         $related = $this->_related;
         try {
             $result = parent::save($data, $whiteList);
             if (!$result) {
                 $this->_related = $related;
             }
             return $result;
         } catch (\Exception $e) {
             $this->_related = $related;
             throw $e;
         }
     }

     protected function _preSaveRelatedRecords(\Phalcon\Db\AdapterInterface $conn, $related)
     {
         //Fix related models recursion problem. Fix savepoint exception when using nested transactions with model exceptionOnFailedSave.
         $this->_related = null;
         try {
             $txLevel = $conn->getTransactionLevel();
             return parent::_preSaveRelatedRecords($conn, $related);
         } catch (\Exception $e) {
             while ($conn->getTransactionLevel() > $txLevel) {
                 try {
                     $conn->rollback();
                 } catch (\PDOException $pdoEx) {}
             }
             throw $e;
         }
     }

     public function __call($method, $args = null)
     {
         //Add magic setter for related models.
         if (1 === count($args)
             && ($args[0] instanceof \Phalcon\MVC\ModelInterface || is_array($args[0]))
             && (0 === stripos($method, 'set'))
         ) {
             $related = substr($method, 3);
             $this->$related = $args[0];
             return $this;
         }
         return parent::__call($method, $args);
     }
 }

We found this class on the web and it fix our problems for a long time, since now.

Here are the software version:

  • PHP 5.6.40
  • Phalcon 2.0.13
  • MySQL 5.7.30

And the value of "innodb_lock_wait_timeout" are setted to 50, default value.

Anyone have had the same problem and fix it?

Please edit your post with proper code formatting so it's possible to read. Preferred format is explained here: https://forum.phalcon.io/help/markdown, under the Code Blocks heading.

edited Aug '20

Hi Dylan, thank you, I've edited the post, it's ok now?

Please edit your post with proper code formatting so it's possible to read. Preferred format is explained here: https://forum.phalcon.io/help/markdown, under the Code Blocks heading.

Much better. Thanks.

As for your problem - do you need all that stuff? Phalcon has built-in save functionality so you don't need to override anything.

Also, Phalcon is on v4 now (with v3.4 being the most popular I think), so you might want to upgrade your Phalcon installation. Finding help for such an old version might be difficult.

Thank you.

We used that abstract class because of problem during saving, phalcon try to save all the related models and sometimes it stop to work, here are the refs: https://forum.phalcon.io/discussion/1305/model-implementation-problems

About of the update to Phalcon 3.4 (or 4), we have some backward incompatibility (https://blog.phalcon.io/post/phalcon-3-0-0-released, specifically "Backwards compatibility from openssl to mcrypt is problematic if not impossible. We had to remove several methods that are no longer applicable. Additionally the rijndael-256 from mcrypt is no longer valid in openssl. The default encryption algorithm is AES-256-CFB")

Much better. Thanks.

As for your problem - do you need all that stuff? Phalcon has built-in save functionality so you don't need to override anything.

Also, Phalcon is on v4 now (with v3.4 being the most popular I think), so you might want to upgrade your Phalcon installation. Finding help for such an old version might be difficult.

I don't know then. You could turn on a query log and check it to see what's actually being passed. This sounds like multiple processes are being run and the second process times out because the first one is taking too long to execute. Honestly though, you're dealing with an old version of Phalcon, so the problem might be fixed in newer versions.

As for mcrypt compatibility - there's nothing about Phalcon that prohibits you from using built-in PHP code. If I were in your place, I'd upgrade, then shim in the parts of Phalcon that don't work with old mcrypt.