Handle lock wait timeout

[ahmadruhaifi]

Hi, my application usually received this error:
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

Its hard to debug because cannot I replicate the issue at local. Only happens sometime at production.

How to debug the error? And what is the best way to fix or handle it?

Thanks.

[niden]

This is one of the most difficult bugs to find sadly, especially in large applications. From what I have seen before, a transaction opened somewhere and never committed or rolled back.

One way to do it but it is painful honestly especially in production, is to implement a query listener which will log all the transactions you create and any commits or rollbacks.

Events for the db layer: https://docs.phalcon.io/4.0/en/db-layer#events

You can create a listener class like this:

<?php

namespace MyNamespace;

use Phalcon\Db\Adapter\AdapterInterface;
use Phalcon\Events\Event;
use Phalcon\Logger\Logger;
use Phalcon\Di\Injectable;

/**
 * @property Logger   $logger
 */
class QueryListener extends Injectable
{
    /**
     * @param Event            $event
     * @param AdapterInterface $connection
     */
    public function beginTransaction(/Event $event, AdapterInterface $connection)
    {
        $this->logger->warning(
            sprintf(
                'BEGIN: %s - [%s]',
                $connection->getSQLStatement(),
                json_encode($connection->getSQLVariables())
            )
        );
    }

    /**
     * @param Event            $event
     * @param AdapterInterface $connection
     */
    public function commitTransaction(/Event $event, AdapterInterface $connection)
    {
        $this->logger->warning(
            sprintf(
                'COMMIT: %s - [%s]',
                $connection->getSQLStatement(),
                json_encode($connection->getSQLVariables())
            )
        );
    }

    /**
     * @param Event            $event
     * @param AdapterInterface $connection
     */
    public function rollbackTransaction(/Event $event, AdapterInterface $connection)
    {
        $this->logger->warning(
            sprintf(
                'ROLLBACK: %s - [%s]',
                $connection->getSQLStatement(),
                json_encode($connection->getSQLVariables())
            )
        );
    }
}

and attach the listener to your db connection.

$eventsManager = $container->getShared('eventsManager');
$eventsManager->attach(
    'db',
    new QueryListener(),
    90
);

The $logger variable above ($this->logger) can be a logger that you set up only for logging transactions. You can add this code with a config conditional coming from your .env file for instance that will enable it only when you want it. Then you can deploy this code in production and switch it on so to speak (using that env variable) and wait until the lockout happens. Then you can switch it off and analyze the log. Hopefully you can find where the transaction was left hanging.

[ahmadruhaifi]

Thank you @niden . This is good, I will try it.

[ahmadruhaifi]

Hi @niden , from the log created, how to analyse it? Is it I need to find transaction which don't have COMMIT after BEGIN?

[ahmadruhaifi]

Hi @niden is this correct? Got log 2 BEGIN & 1 COMMIT. Here is the screenshot [(https://ibb.co/CKPWX0K)].
And also got log without BEGIN, just COMMIT -> [https://ibb.co/6bkJYCX]

[niden]

It could be correct depending on what happens on your site. So for instance person A visits /invoices and that starts the first transaction, while at the same time person B visits /customers and that starts the second one. The log can show both at the same time and that is not a definitive indication of failure. I would suggest you keep checking it and making sure that everything seems ok so you have pairs of begin/commit.

Another thing to try is to find in your code all the spots where a transaction opens and then create tests that check if it closes, or even visual checks, dev testing etc.

[ahmadruhaifi]

OK @niden , thanks for the explanation. I understood. I will keep continue monitoring the log.

[ahmadruhaifi]

@niden For this statement "Another thing to try is to find in your code all the spots where a transaction opens and then create tests that check if it closes, or even visual checks, dev testing etc.", you mean for every $this->db->begin(), there must close with $this->db->commit(); right? So I need to check the whole codebase to make sure it is closed.

[ahmadruhaifi]

If that the case, I think every commit is already closed.
I search begin found 486 result https://ibb.co/ssXCmYt. And search commit also found 486 results https://ibb.co/g9z5SrC