Doctrine2 ORM select for update

问题:

Please could you suggest me some approach how to implement SELECT FOR UPDATE with Doctrine?

I need to read some counter value, use it in PHP code and immediately increment the value before someone else (from another process) uses the same value.

回答1:

Locking support

Doctrine 2 implements Locking support for entities:

<?php
use Doctrine\DBAL\LockMode;
use Doctrine\ORM\OptimisticLockException;

$theEntityId = 1;
$expectedVersion = 184;

try {
    $entity = $em->find('User', $theEntityId, LockMode::OPTIMISTIC, $expectedVersion);

    // do the work

    $em->flush();
} catch(OptimisticLockException $e) {
    echo "Someone else has already changed this entity. Apply the changes again!";
}

Native sql

Also, you can do it throws execute raw SQL:

$em->getConnection()->exec('LOCK TABLES table_name WRITE;'); //lock for write access

and then

$em->getConnection()->exec('UNLOCK TABLES;');

回答2:

Apparently, Doctrine 2 uses LOCK IN SHARED MODE with Pessimistic read lock for MySQL, which is not the same as SELECT FOR UPDATE.

Looking at the sources of the current stable release, it seems that there is no native way of doing so in Doctrine (I'm not sure why the Doctrine team chose that type of lock for MySQL).

I used native SQL as workaround, which can be mapped to the traditional entities, as it would be with DQL:

<?php
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('Model_Record_Delivery', 'u');
$query = $this->_em->createNativeQuery("SELECT * FROM delivery WHERE id = :id FOR UPDATE", $rsm);
$query->setParameter("id", $id);
$result = $query->getOneOrNullResult();

Update

As Benjamin has pointed out, PESSIMISTIC_WRITE is what you are looking for.

With DQL

<?php
$query = $this->em->createQuery('SELECT e
    FROM Application\Model\Entity\MyEntity e
    WHERE e = :id');

$query->setParameter("id", $id);
$query->setLockMode(\Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE);

Without DQL

<?php
$entity = $em->find('Application\Model\Entity\MyEntity', $id, \Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE);

Also, you have to use the statement inside a transaction to make it work.