Concurrency in Doctrine

concurrency, doctrine-orm, mysql, php, transactions

Solution

The answer from @YaK is actually a good answer. You should know how to deal with locks in general.

Addressing Doctrine2 specifically, your code should look like:

$em->getConnection()->beginTransaction();
try {
    $toUpdate = $em->find('Entity\WhichWillBeUpdated', $id,  \Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE);
    // this will append FOR UPDATE http://docs.doctrine-project.org/en/2.0.x/reference/transactions-and-concurrency.html
    $em->persist($anInsertedOne);
    // you can flush here as well, to obtain the ID after insert if needed
    $toUpdate->changeValue('new value');
    $em->persist($toUpdate);
    $em->flush();
    $em->getConnection()->commit();
} catch (\Exception $e) {
    $em->getConnection()->rollback();
    throw $e;
}

The every subsequent request to fetch for update, will wait until this transaction finishes for one process which has acquired the lock. Mysql will release the lock automatically after transaction is finished successfully or failed. By default, innodb lock timeout is 50 seconds. So if your process does not finish transaction in 50 seconds it will rollback and release the lock automatically. You do not need any additional fields on your entity.

Problem

I have an application, running on php + mysql plattform, using Doctrine2 framework. I need to execute 3 db queries during one http request: first INSERT, second SELECT, third UPDATE. UPDATE is dependent on result of SELECT query. There is a high probability of concurrent http requests. If such situation occurs, and DB queries get mixed up (eg. INS1, INS2, SEL1, SEL2, UPD1, UPD2), it will result in data inconsistency. How do I assure atomicity of INS-SEL-UPD operation? Do I need to use some kind of locks, or transactions are sufficient?

Original source