DDC-1846: Pessimistic lock does not retreive latest version of entity when entity is already in doctrine cache #2327

New Issue

admin · 2026-01-22T13:48:33+01:00

admin commented

2026-01-22 13:48:33 +01:00

Originally created by @doctrinebot on GitHub (May 30, 2012).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user bramklg:

When setting a pessimistic lock on an entity (e.g. a row lock) and retreiving an entity from the database, Doctrine returns the entity from cache if it has any. When updating a counter on an entity for example, it is important the row is locked, retreived, updated and then unlocked to guarantee the counter keeps in sync. Using $em->clear(); before $em->find(); is a work-around for this problem, but as requested by Benjamin Eberlei on the google groups thread (https://groups.google.com/forum/?fromgroups#!topic/doctrine-user/N8Xop2-XbTY) this bugreport is made to fix this without the need of clear().

In the next example, if the entity is previously retreived already, find() returns that version instead of retreiving the current version from the database after the rowlock is set. When $em->clear(); can be used to work-around the problem

// $em instanceof EntityManager

//$em->clear(); // Uncommenting this fixed the problem
$em->getConnection()->beginTransaction();

try {
    $entity = $em->find('Entity', $id, LockMode::PESSIMISTIC_WRITE);

    /** Update some fields, for example, decrease a counter **/
    $entity->setCounter($entity->getCounter() - 1);

    $em->persist($entity);
    $em->flush();
    $em->getConnection()->commit();
} catch ( \Exception $ex ) {
    $em->getConnection()->rollback();
}

Originally created by @doctrinebot on GitHub (May 30, 2012). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user bramklg: When setting a pessimistic lock on an entity (e.g. a row lock) and retreiving an entity from the database, Doctrine returns the entity from cache if it has any. When updating a counter on an entity for example, it is important the row is locked, retreived, updated and then unlocked to guarantee the counter keeps in sync. Using $em->clear(); before $em->find(); is a work-around for this problem, but as requested by Benjamin Eberlei on the google groups thread (https://groups.google.com/forum/?fromgroups#!topic/doctrine-user/N8Xop2-XbTY) this bugreport is made to fix this without the need of clear(). In the next example, if the entity is previously retreived already, find() returns that version instead of retreiving the current version from the database after the rowlock is set. When $em->clear(); can be used to work-around the problem ``` // $em instanceof EntityManager //$em->clear(); // Uncommenting this fixed the problem $em->getConnection()->beginTransaction(); try { $entity = $em->find('Entity', $id, LockMode::PESSIMISTIC_WRITE); /** Update some fields, for example, decrease a counter **/ $entity->setCounter($entity->getCounter() - 1); $em->persist($entity); $em->flush(); $em->getConnection()->commit(); } catch ( \Exception $ex ) { $em->getConnection()->rollback(); } ```

admin added the Bug label 2026-01-22 13:48:33 +01:00

admin closed this issue

2026-01-22 13:48:33 +01:00

admin commented

2026-01-22 13:48:34 +01:00

@doctrinebot commented on GitHub (May 30, 2012):

relates to DDC-2929: Pessimistic lock on Query does not update the entity with the DB values if it's already cached

@doctrinebot commented on GitHub (May 30, 2012): - relates to [DDC-2929: Pessimistic lock on Query does not update the entity with the DB values if it's already cached](http://www.doctrine-project.org/jira/browse/DDC-2929)

admin commented

2026-01-22 13:48:35 +01:00

@doctrinebot commented on GitHub (Jun 22, 2012):

Comment created by bramklg:

Any update or ETA on this one? The work-around is still in my production code and I would like to get it out to get it cleaned-up a bit.

@doctrinebot commented on GitHub (Jun 22, 2012): Comment created by bramklg: Any update or ETA on this one? The work-around is still in my production code and I would like to get it out to get it cleaned-up a bit.

admin commented

2026-01-22 13:48:35 +01:00

@doctrinebot commented on GitHub (Jul 4, 2012):

Comment created by @beberlei:

Why does the $this->_em->lock() in EntityRepository#find() don't work for you? It should grab the entity from cache, then do a SELECT 1 FROM table and do the pessemistic write lock on the row.

@doctrinebot commented on GitHub (Jul 4, 2012): Comment created by @beberlei: Why does the $this->_em->lock() in EntityRepository#find() don't work for you? It should grab the entity from cache, then do a SELECT 1 FROM table and do the pessemistic write lock on the row.

admin commented

2026-01-22 13:48:35 +01:00

@doctrinebot commented on GitHub (Jul 5, 2012):

Comment created by bramklg:

I don't exactly know why that does not work. I created this bugreport on your request (see: https://groups.google.com/forum/?fromgroups#!topic/doctrine-user/N8Xop2-XbTY) because I couldnt figure out what I was doïng wrong.

The script that updates the entity is a long running console script, allot of times the entity in question is already in the cache and did not get locked properly. There are two seperate processes that work on the same entity (processing jobs from a jobqueu) and when updating the counters at the same time, and both scripts (with the same locking code as described in the bugreport) have to update a counter, the counter is not updated properly (e.g. both scripts do for example 2-1 where the first script should do 2-1, the second script 1-1 since the counter is updated by the first script).

@doctrinebot commented on GitHub (Jul 5, 2012): Comment created by bramklg: I don't exactly know why that does not work. I created this bugreport on your request (see: https://groups.google.com/forum/?fromgroups#!topic/doctrine-user/N8Xop2-XbTY) because I couldnt figure out what I was doïng wrong. The script that updates the entity is a long running console script, allot of times the entity in question is already in the cache and did not get locked properly. There are two seperate processes that work on the same entity (processing jobs from a jobqueu) and when updating the counters at the same time, and both scripts (with the same locking code as described in the bugreport) have to update a counter, the counter is not updated properly (e.g. both scripts do for example 2-1 where the first script should do 2-1, the second script 1-1 since the counter is updated by the first script).

admin commented

2026-01-22 13:48:35 +01:00

@doctrinebot commented on GitHub (Jul 5, 2012):

Comment created by @beberlei:

Can you paste the SQL log that gets executed?

@doctrinebot commented on GitHub (Jul 5, 2012): Comment created by @beberlei: Can you paste the SQL log that gets executed?

admin commented

2026-01-22 13:48:36 +01:00

@doctrinebot commented on GitHub (Jul 5, 2012):

Comment created by bramklg:

Allright, I reverted the code back to my original code. Doctrine is updated to the latest dev version. Code looks like this:

$em = $this->getEntityManager();
#$em->clear(); // This fixes the locking problem
$em->getConnection()->beginTransaction();

try {
    // Lock entity in db and load it to update counters
    $entity = $em->find($this->getEntityName(), $this->getEppEntityId(), LockMode::PESSIMISTIC_WRITE);

    printf('Decreasing pendingjobs with 1. Current amount of pending jobs is %d', $entity->getPendingJobs());

    $entity->setPendingJobs($entity->getPendingJobs() - 1);

    $em->persist($entity);
    $em->flush();
    $em->getConnection()->commit();
} catch ( \Exception $ex ) {
    $em->getConnection()->rollback();
}

The output of the debug statement looks like this (e.g. each line is printed by a different process. Instead of the first script decreasing 2 to 1 and the second script decreasing 1 to 0, both scripts decrease 2 to 1):

[2012-07-05 11:31:08] Decreasing pendingjobs with 1. Current amount of pending jobs is 2
[2012-07-05 11:31:07] Decreasing pendingjobs with 1. Current amount of pending jobs is 2

I enabled SQL logging but the thing is huge and I dont really know where to look. There is data in it that I dont want publically availible on the internet, is it possible to mail the to you directly?

@doctrinebot commented on GitHub (Jul 5, 2012): Comment created by bramklg: Allright, I reverted the code back to my original code. Doctrine is updated to the latest dev version. Code looks like this: ``` none $em = $this->getEntityManager(); #$em->clear(); // This fixes the locking problem $em->getConnection()->beginTransaction(); try { // Lock entity in db and load it to update counters $entity = $em->find($this->getEntityName(), $this->getEppEntityId(), LockMode::PESSIMISTIC_WRITE); printf('Decreasing pendingjobs with 1. Current amount of pending jobs is %d', $entity->getPendingJobs()); $entity->setPendingJobs($entity->getPendingJobs() - 1); $em->persist($entity); $em->flush(); $em->getConnection()->commit(); } catch ( \Exception $ex ) { $em->getConnection()->rollback(); } ``` The output of the debug statement looks like this (e.g. each line is printed by a different process. Instead of the first script decreasing 2 to 1 and the second script decreasing 1 to 0, both scripts decrease 2 to 1): ``` [2012-07-05 11:31:08] Decreasing pendingjobs with 1. Current amount of pending jobs is 2 [2012-07-05 11:31:07] Decreasing pendingjobs with 1. Current amount of pending jobs is 2 ``` I enabled SQL logging but the thing is huge and I dont really know where to look. There is data in it that I dont want publically availible on the internet, is it possible to mail the to you directly?

admin commented

2026-01-22 13:48:36 +01:00

@doctrinebot commented on GitHub (Jul 7, 2012):

Comment created by @beberlei:

Fixed

@doctrinebot commented on GitHub (Jul 7, 2012): Comment created by @beberlei: Fixed

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jul 7, 2012):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jul 7, 2012): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jan 21, 2014):

Comment created by nail:

Sorry to comment on a closed ticket, but this is also happening when setting a pessimistic lock on Query objects (unless you set the Query::HINT_REFRESH hint)

Example:

        $em->beginTransaction();
        try {
            $bar = $em->createQuery('SELECT b FROM Foo:Bar b WHERE b.id = :id')
                        ->setParameter('id', 150)
                        ->getSingleResult();
            var_dump($bar->getAmount()); // Yields some positive value
            $bar->setAmount(0);
            $bar = $em->createQuery('SELECT b FROM Foo:Bar b WHERE b.id = :id')
                        ->setParameter('id', 150)
                        ->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE)
                        // ->setHint(\Doctrine\ORM\Query::HINT_REFRESH, true)
                        ->getSingleResult();
            var_dump($bar->getAmount()); // Yields 0

            $em->flush();
            $em->commit();
        } catch (\Exception $e) {
            $em->rollback();
        }

Is this the desired behaviour when using a query, or is it related to this bug?

(Tested on 2.4.0 and 2.4.1)

@doctrinebot commented on GitHub (Jan 21, 2014): Comment created by nail: Sorry to comment on a closed ticket, but this is also happening when setting a pessimistic lock on Query objects (unless you set the `Query::HINT_REFRESH` hint) Example: ``` $em->beginTransaction(); try { $bar = $em->createQuery('SELECT b FROM Foo:Bar b WHERE b.id = :id') ->setParameter('id', 150) ->getSingleResult(); var_dump($bar->getAmount()); // Yields some positive value $bar->setAmount(0); $bar = $em->createQuery('SELECT b FROM Foo:Bar b WHERE b.id = :id') ->setParameter('id', 150) ->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE) // ->setHint(\Doctrine\ORM\Query::HINT_REFRESH, true) ->getSingleResult(); var_dump($bar->getAmount()); // Yields 0 $em->flush(); $em->commit(); } catch (\Exception $e) { $em->rollback(); } ``` Is this the desired behaviour when using a query, or is it related to this bug? (Tested on 2.4.0 and 2.4.1)

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jan 22, 2014):

Comment created by jkavalik:

When this is revived, I noticed that $em->lock($entiy, PESSIMISTIC_WRITE); doesn't refresh too. If it is not supposed to, it might be noted in documentation or in http://docs.doctrine-project.org/en/2.0.x/reference/transactions-and-concurrency.html

@doctrinebot commented on GitHub (Jan 22, 2014): Comment created by jkavalik: When this is revived, I noticed that $em->lock($entiy, PESSIMISTIC_WRITE); doesn't refresh too. If it is not supposed to, it might be noted in documentation or in http://docs.doctrine-project.org/en/2.0.x/reference/transactions-and-concurrency.html

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jan 22, 2014):

Comment created by @ocramius:

[_{nail] [}jkavalik] can you please open separate (detailed) issues for those problems?

@doctrinebot commented on GitHub (Jan 22, 2014): Comment created by @ocramius: [<sub>nail] [</sub>jkavalik] can you please open separate (detailed) issues for those problems?

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jan 22, 2014):

Comment created by nail:

Done! See DDC-2929

@doctrinebot commented on GitHub (Jan 22, 2014): Comment created by nail: Done! See [DDC-2929](http://www.doctrine-project.org/jira/browse/DDC-2929)

admin commented

2026-01-22 13:48:37 +01:00

@doctrinebot commented on GitHub (Jan 23, 2014):

Comment created by jkavalik:

Done in DDC-2930

@doctrinebot commented on GitHub (Jan 23, 2014): Comment created by jkavalik: Done in [DDC-2930](http://www.doctrine-project.org/jira/browse/DDC-2930)

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2327