Auto Retry DB queries

I am currently victim to gap locks in MySQL: http://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html

Simply retrying could be a solution to my problem. I see that there is a separate parameter on transactions:

DB::transaction(function () { ... }, 5);

However I do not use transactions and the problem occurs with normal INSERT statement.

Is there some easy way to handle this globally? E.g. configure the driver to use retries on all queries?

Otherwise I'd have to wrap all queries like this:

for ($retries = 0; $retries <= MAX_NUM_RETIRES; $retries++) {
    try {
        // run query
    } catch (\Exception $e) {
        continue;
    }
    break;
}

chrismay

6 years ago

Level 7

Ok, I found a solution by changing a vendor file: vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOStatement.php

before:

    public function execute($params = null)
    {
        try {
            return parent::execute($params);
        } catch (\PDOException $exception) {
            throw new PDOException($exception);
        }
    }

after:

 public function execute($params = null)
    {
        $tries = 1;
        $maxTries = 3;
        while(true) {
            try {
                return parent::execute($params);
            } catch (\PDOException $exception) {
                if($tries >= $maxTries)
                    throw new PDOException($exception);
                $tries++;
            }
        }
    }

However I am not sure how to implement this change in my own project without forking the package and the requiring the changed version (I would like to stick to the vendor package and somehow override just the single method)

chrismay

6 years ago

Level 7

ok I found the solution for anyone interested: This in composer.json replaces the file. Don't forge to run dump-autoload.

"classmap": [
  "vendorOverrides/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php"
],
"exclude-from-classmap": [
  "vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php"
],

gonzalom

5 years ago

Level 4

As Laravel uses Doctrine Dbal libraries, I would use the \Doctrine\DBAL\Exception\RetryableException instead:

https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/transactions.html#error-handling

try {
    // process stuff
} catch (\Doctrine\DBAL\Exception\RetryableException $e) {
    // retry the processing
}

This way you will not retry wrong queries, for instance, or any other execution that can't be helped with a retry.

1 like

Please or to participate in this conversation.