How to retry transaction after a deadlock using Doctrine?

database, deadlock, doctrine-orm, mysql, php

Solution

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.

Problem

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?

Original source