Kuba Petržílka Kuba Petržílka - 1 year ago 150
MySQL Question

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.

Answer Source

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