yii - catch and log MySQL deadlock errors

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.

Extend error handler class:

    class AppErrorHandler extends CErrorHandler {
        protected function handleException($exception) {
            /* 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)
            */
            //can we check $exception->getCode() ?
            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);
        }
    }

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

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

InnoDB Lock Modes

InnoDB Record, Gap, and Next-Key Locks

MySQL Forums :: Transactions :: Deadlock With DELETE/INSERT Queries

MySQL Forums :: InnoDB :: Explain deadlock locking the same index

profile for Boris Serebrov on Stack Exchange, a network of free, community-driven Q&A sites