CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdeadlock
StockBreak
StockBreak

Reputation: 2895

Doctrine Exception: Deadlock found when trying to get lock

I have a Symfony app which exposes a collection of JSON web services used by a mobile app.
On the last few days we are having many concurrent users using the app (~5000 accesses per day) and a Doctrine error started to "randomly" appear in my logs. It appears about 2-3 times per day and this is the error:

Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'UPDATE fos_user_user SET current_crystals = ?, max_crystals = ?, updated_at = ? WHERE id = ?' with params [31, 34, "2017-12-19 09:31:18", 807]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction" at /var/www/html/rollinz_cms/releases/98/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115

It seems it cannot get the lock while updating the users table. The controller code is the following:

/**
 * @Rest\Post("/api/badges/{id}/achieve", name="api_achieve_badge")
 */
public function achieveAction(Badge $badge = null)
{
    if (!$badge) {
        throw new NotFoundHttpException('Badge not found.');
    }

    $user      = $this->getUser();
    $em        = $this->getDoctrine()->getManager();
    $userBadge = $em->getRepository('AppBundle:UserBadge')->findBy(array(
        'user'  => $user,
        'badge' => $badge,
    ));

    if ($userBadge) {
        throw new BadRequestHttpException('Badge already achieved.');
    }

    $userBadge = new UserBadge();
    $userBadge
        ->setUser($user)
        ->setBadge($badge)
        ->setAchievedAt(new \DateTime())
    ;
    $em->persist($userBadge);

    // sets the rewards
    $user->addCrystals($badge->getCrystals());

    $em->flush();

    return new ApiResponse(ApiResponse::STATUS_SUCCESS, array(
        'current_crystals'    => $user->getCurrentCrystals(),
        'max_crystals'        => $user->getMaxCrystals(),
    ));
}

I looked into MySQL and Doctrine documentation but I couldn't find a reliable solution. Doctrine suggests retrying the transaction but it doesn't show an actual example:

https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html

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

This posts suggests retrying the transaction. How can I do it?

Could it be a server problem (too many accesses) and I must boost the server or the code is wrong and I must explicitly handle the deadlock in my code?

Upvotes: 4

Views: 8519

Answers (3)

wonich
wonich

Reputation: 1

On base of @albert (thx)

  1. Add use to the top of file use Doctrine\DBAL\Exception\DeadlockException;
  2. Add this function to a class (ex. Service)
    public function retryflush($maxretry = 3)
    {
        $em = $this->doc->getManager();

        $retry=0;
        while ($retry >= 0) {
            try {
                $em->flush();
                $retry = -1;
            } catch (DeadlockException $e) {
                $retry++;
                if($retry>$maxretry) { throw $e; }
                sleep(513); //optional
            }
        }
        return $retry;
    }
  1. Redefine
$em = $this->doc->getManager(); // use your own entitymanager.
  1. Run it like
$classname->retryflush(5) // retry 5 times

Upvotes: 0

bfav
bfav

Reputation: 151

Albert's solution is the right one but you also must recreate a new EntityManager in the catch clause using resetManager() of your ManagerRegistry. You'll get exceptions if you continue to use the old EntityManager and its behavior will be unpredictable. Beware of the references to the old EntityManager too.

This issue will be hopefully corrected in Doctrine 3: See issue

Until then, here is my suggestion to handle the problem nicely: Custom EntityManager

Upvotes: 2

albert
albert

Reputation: 4468

This is a MySQL issue. Multiple simultaneous transactions blocking the same resources.

Check if you have cronjobs that may block the records for long times.

Otherwise is just concurrent requests updating the same data, you may have better knowledge where this data gets updated.

Dirty attempt for a retry in php:

$retry=0;
while (true) {
    try {
      // some more code
      $em->flush();
      return new ApiResponse(ApiResponse::STATUS_SUCCESS, array(
         'current_crystals'    => $user->getCurrentCrystals(),
         'max_crystals'        => $user->getMaxCrystals(),
      ));
    } catch (DriverException $e) {
       $retry++;
       if($retry>3) { throw $e; }
       sleep(1); //optional
    }
}

Upvotes: 3

Related Questions