Doctrine2 ORM select for update

Question

Could you suggest an approach how to implement SELECT FOR UPDATE with Doctrine?

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

Answer 1

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.

Answer 2

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;');