How to handle deadlock in Doctrine?

Question

I have a mobile application and server based on Symfony which gives API for the mobile app.

I have a situation, where users can like Post. When users like Post I add an entry in ManyToMany table that this particular user liked this particular Post (step 1). Then in Post table I increase likesCounter (step 2). Then in User table I increase gamification points for user (because he liked the Post) (step 3).

So there is a situation where many users likes particular Post at the same time and deadlock occurs (on Post table or on User table). How to handle this? In Doctrine Docs I can see solution like this:

<?php

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

but what should I do in catch part? Retry the whole process of liking (steps 1 to 3) for instance 3 times and if failed return BadRequest to the mobile application? Or something else?

I don't know if this is a good example cause maybe I could try to rebuild the process so the deadlock won't happen but I would like to know what should I do if they actually happen?

Hamon

Answer 1

I disagree with Stefan, deadlocks are normal as the MySQL documentation says:

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

See: MySQL documentation

However, the loop suggested by Stefan is the right solution. Except that it lacks an important point: after Doctrine has thrown an Exception, the EntityManager becomes unusable and you must create a new one in the catch clause with resetManager() from the ManagerRegistry instance.

When I had exactly the same concern as you, I searched the web but couldn't find any completely satisfactory answer. So I got my hands dirty and came back with an article where you'll find an implementation exemple of what I said above:

Thread-safe business logic with Doctrine

Noted

Answer 2

What I'd do is post all likes on a queue and consume them using a batch consumer so that you can group the updates on a single post.

If you insist on keeping you current implementation you could go down the road you yourself suggested like this:

<?php

for ($i = 0; $i < $retryCount; $i++) {
  try {
      // try updating
      break;
  } catch (\Doctrine\DBAL\Exception\RetryableException $e) {
      // you could also add a delay here
      continue;
  }
}

if ($i === $retryCount) {
  // throw BadRequest
}

This is an ugly solution and I wouldn't suggest it. Deadlocks shouldn't be "avoided" by retrying or using delays. Also have a look at named locks and use the same retry system, but don't wait for the deadlock to happen.

Doggone

Answer 3

The problem is that after Symfony Entity Manager fails - it closes db connection and you can't continue you work with db even if you catch the ORMException.

First good solution is to process your 'likes' async, with rabbitmq or other queue implementation.

Step-by-step:

1. Create message like {type: 'like', user:123, post: 456}
2. Publish it in queue
3. Consume it and update 'likes' count.

You can have several consumers that try to obtain lock on based on postId. If two consumers try to update same post - one of them will fail obtaining the lock. But it's ok, you can consume failed message after.

Second solution is to have special table e.g. post_likes (userId, postId, timestamp). Your endpoint could create new rows in this table synchronously. And you can count 'likes' on some post with this table. Or you can write some cron script, which will update post likes count by this table.

Billet

How do you deal with acknowledgements? E.g., User is on the form page. He/She saves the form, sending a request and performing an insert into the database. The data are immediately required on the next page in your application. Do you wait for rabbitMQ to finish all of its consumer jobs while the User is still waiting in the request? – Delacroix

You should use async processing carefully. If data are immediately required on the next page - may be you should give up using async rabbit queue. Another option is to make your second page dynamic, so it would check if data was processed showing some "loading" pic – Billet

I thought so. It's perfectly fine, though. Just wanted more clarification on that async storing isn't necessarily the best solution for every use case. – Delacroix

Answer 4

I've made a special class to retry on deadlock (I'm on Symfony 4.4).

Here it is :

class AntiDeadlockService
{
    /**
     * @var EntityManagerInterface
     */
    private $em;

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

    public function safePush(): void
    {
        // to retry on deadlocks or other retryable exceptions
        $connection = $this->em->getConnection();
        $retry = 0;
        $maxRetries = 3;
        while ($retry < $maxRetries) {
            try {
                if (!$this->em->isOpen()) {
                    $this->em = $this->em->create(
                        $connection = $this->em->getConnection(),
                        $this->em->getConfiguration()
                    );
                }
                $connection->beginTransaction(); // suspend auto-commit
                $this->em->flush();
                $connection->commit();
                break;
            } catch (RetryableException $exception) {
                $connection->rollBack();
                $retry++;
                if ($retry === $maxRetries) {
                    throw $exception;
                }
            }
        }
    }
}

Use this safePush() method instead of the $entityManager->push() one ;)

Bacterium