php - 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 ApplicationModelEntityMyEntity e
    WHERE e = :id');

$query->setParameter("id", $id);
$query->setLockMode(DoctrineDBALLockMode::PESSIMISTIC_WRITE);

Without DQL

<?php
$entity = $em->find('ApplicationModelEntityMyEntity', $id, DoctrineDBALLockMode::PESSIMISTIC_WRITE);

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