How to retry transaction after a deadlock using Do

Question

I am writing a PHP function which store/updates large sets of data into a table and that may cause a deadlock. I tried investigating how to retry a failed transaction with Doctrine but sadly could not find any info online. I eventually wrote the following code

 $retry = 0;
 $done = false;
 while (!$done and $retry < 3) {
     try {

         $this->entityManager->flush();
         $done = true;

     } catch (\Exception $e) {
         sleep(1);

         $retry++;
     }
 }

 if ($retry == 3) {
     throw new Exception(
         "[Exception: MySQL Deadlock] Too many people accessing the server at the same time. Try again in few minutes"
     );
 }

My question: is there a chance this approach will insert duplicates in the database? if so, how can I force Doctrine to roll back the transactions?

Answer 1

Retry transaction snippet

• There's no mess about getting connection.
• While loop is properly handled

Code

$connection = ...
$retry = 0;
$maxRetries = 3;

while ($retry < $maxRetries) {
    try {
        $connection->beginTransaction();

        // do stuff

        $connection->commit();

        break;
    } catch (Throwable $exception) {
        $connection->rollBack();

        $retry++;

        if ($retry === $maxRetries) {
            throw $exception;
        }
    }
}

More about Doctrine transactions can be found at https://www.doctrine-project.org/projects/doctrine-dbal/en/2.7/reference/transactions.html

How to get connection:

Simply inject Doctrine's EntityManager (Interface) or Connection eg. via constructor

Connection

public function __construct(Connection $connection)
{
    $this->connection = $connection;
}

Or EntityManager

public function __construct(EntityManagerInterface $entityManager)
{
    $this->entityManager = $entityManager;
}

And get connection as $this->entityManager->getConnection()

Answer 2

This is how I am dealing with retrying failed transactions with Sf2.7 and doctrine 2.4.7:

use Doctrine\Bundle\DoctrineBundle\Registry;
use Doctrine\ORM\EntityManager;

class Foo
{
    /**
     * @var Registry
     */
    protected $doctrine;

    public function __construct(Registry $registry)
    {
        $this->doctrine = $registry;
    }

    protected function doSomething($entity, $attempt)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        try{
            $conn->beginTransaction();
            $entity->setBar("baz");
            $em->flush();
            $conn->commit();
        } catch(\PDOException $e){
            $conn->rollBack();
            $attempt++;
            if($attempt <= 3){
                $this->doSomething($repayment, $attempt);
            }
        }
    }

    /**
     * @return EntityManager
     */
    protected function getEntityManager()
    {
        /** @var EntityManager $em */
        $em = $this->doctrine->getManager();
        if(!$em->isOpen()){
            $this->doctrine->resetManager();
            $em = $this->doctrine->getManager();
        }

        return $em;
    }
}

Answer 3

If you are not using ORM then use that it will automatically managed deadlock situation.

Answer 4

A deadlock returns error 1213 which you should process on the client side

Note that a deadlock and lock wait are different things. In a deadlock, there is no "failed" transaction: they are both guilty. There is no guarantee which one will be rolled back.

You must use rollback, your style code will insert duplicate. for example you should :

$retry = 0;

$done = false;


$this->entityManager->getConnection()->beginTransaction(); // suspend auto-commit

while (!$done and $retry < 3) {

    try {

        $this->entityManager->flush();

        $this->entityManager->getConnection()->commit(); // commit if succesfull

        $done = true;

    } catch (\Exception $e) {

        $this->entityManager->getConnection()->rollback(); // transaction marked for rollback only

        $retry++;

    }

}

Hope this help.