Yii 1.1: How to catch and log MySQL deadlock errors

8 followers

This method allows to log InnoDB monitor output when deadlock error occured. This way we will have much more useful data to find and fix deadlock.

First extend error handler class:

class AppErrorHandler extends CErrorHandler {
    protected function handleException($exception) {
        //Exception example: 
        /* CDbCommand failed to execute the SQL statement: SQLSTATE[40001]:
        * Serialization failure: 1213 Deadlock found when trying to get lock;
        * try restarting transaction. The SQL statement executed was:
        * INSERT INTO `table_name` (`id`, `name`) VALUES (:yp0, :yp1)
        */
        if ($exception instanceof CDbException 
            && strpos($exception->getMessage(), 'Deadlock') !== false
        ) {
            $data = Yii::app()->db->createCommand('SHOW ENGINE INNODB STATUS')->query();
            $info = $data->read();
            $info = serialize($info);
            Yii::log('Deadlock error, innodb status: ' . $info,
                CLogger::LEVEL_ERROR,'system.db.CDbCommand');
        }
        return parent::handleException($exception);
    }
}

Then put it in application/protected/components and set in the config/main.php:

return array(
    ...
    'components' => array(
        'errorHandler' => array(
            'class' => 'AppErrorHandler',
        ),
    ),
    ...
);

Now you will have full information about the deadlock in the log file.

Total 1 comment

#17624 report it
Maurizio Domba Cerin at 2014/07/08 06:13pm
read() ?

read() gets only one row... shouldn't readAll() be used instead?

Leave a comment

Please to leave your comment.

Write new article
  • Written by: seb
  • Category: Tips
  • Yii Version: 1.1
  • Votes: +4
  • Viewed: 8,729 times
  • Created on: Mar 29, 2012
  • Last updated: never
  • Tags: database