When does EntityManager::lock() even have sense?

Question

Working with Symfony 5.4 and Doctrine, I have an entity which I need to update with concurrency safety in mind. I need to add balance to existing balance and avoid stale data if 2 requests come at the same time.

This is my controller:

#[Route('/{id}/add10ToBalance', name: 'cp_partners_resellers_edit')]
public function add10ToBalance(Request $request, Reseller $reseller): Response

and as you can see the Reseller entity is loaded from the URL.

Now that i have the entity, I do:

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

try {
    $this->entityManager->lock($reseller, LockMode::PESSIMISTIC_WRITE);

    $reseller->setBalance($reseller->getBalance() + 10);

    $this->entityManager->persist($reseller);
    $this->entityManager->flush();

    $this->entityManager->getConnection()->commit();

I confirmed from SQL log in debugger that this issues following sequence:

SELECT * FROM reseller WHERE id = ?
START TRANSACTION
SELECT 1 FROM reseller t0 WHERE t0.id = ? FOR UPDATE
UPDATE reseller SET balance = ? WHERE id = ?
COMMIT

The issue seems clear here - the lock() function does not actually refresh data from the DB. So between the SELECT and START TRANSACTION if the entity gets modified, I am now working with a locked version in DB, but the data I have in my Symfony process is stale.

The obvious solution is to use

$reseller = $this->entityManager->find(Reseller::class, $reseller->getId(), LockMode::PESSIMISTIC_WRITE);

on top, which leads to correct SQL order:

SELECT * FROM reseller WHERE id = ?
START TRANSACTION
SELECT * FROM reseller WHERE id = ? FOR UPDATE
UPDATE reseller SET balance = ? WHERE id = ?
COMMIT

But then it makes me wonder:

What is even the real use of the EntityManager::lock() method, if it leads to locking potentially stale data?

When does EntityManager::lock() even have sense?

0 Answers