如何使用 Doctrine 在死锁后重试事务?

2022-01-03 00:00:00 database deadlock php mysql doctrine-orm

我正在编写一个 PHP 函数，它将大量数据存储/更新到一个表中，这可能会导致死锁.我尝试调查如何使用 Doctrine 重试失败的交易，但遗憾的是在网上找不到任何信息.我最终写了下面的代码

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"
     );
 }

我的问题:这种方法是否有可能在数据库中插入重复项?如果是这样，我如何强制 Doctrine 回滚事务?

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?

推荐答案

死锁返回错误 1213，您应该在客户端处理

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.

您必须使用rollback，您的样式代码将插入重复项.例如你应该:

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++;

    }

}

希望对您有所帮助.

相关文章