Concurrency in Doctrine

Question

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?

Answer 1

A table-wide LOCK is guaranteed to work in all situations. But they are quite bad because they kind of prevent concurrency, rather than deal with it. However, if your script holds the locks for a very short time frame, it might be an acceptable solution.

If your table uses InnoDB engine (no support for transactions with MyISAM), transaction is the most efficient solution, but also the most complex.

For your very specific need (in the same table, first INSERT, second SELECT, third UPDATE dependending on result of SELECT query):

1. Start a transaction
2. INSERT your records. Other transactions will not see these new rows until your own transaction is committed (unless you use a non-standard isolation level)
3. SELECT your record(s) with SELECT...LOCK IN SHARE MODE. You now have a READ lock on these rows, no one else may change these rows. (*)
4. Compute whatever you need to compute to determine whether or not you need to UPDATE something.
5. UPDATE the rows if required.
6. Commit
7. Expect errors at any time. If a dead-lock is detected, MySQL may decide to ROLLBACK you transaction to escape the dead-lock. If another transaction is updating the rows you are trying to read from, your transaction may be locked for some time, or even time-out.

The atomicity of your transaction is guaranteed if you proceed this way.

(*) in general, rows not returned by this SELECT may still be inserted in a concurrent transaction, that is, the non-existence is not guaranteed throughout the course of the transaction unless proper precautions are taken

Answer 2

Transactions won't prevent thread B to read the values thread A has not locked

So you must use locks to prevent concurrency access.

@Gediminas explained how you can use locks with Doctrine. But using locks can result in dead locks or lock timeouts. Doctrine renders these SQL errors as RetryableExceptions. These exceptions are often normal if you are in a high concurrency environment. They can happen very often and your application should handle them properly.

Each time a RetryableException is thrown by Doctrine, the proper way to handle this is to retry the whole transaction.

As easy as it seems, there is a trap. The Doctrine 2 EntityManager becomes unusable after a RetryableException and you must recreate a new one to replay your whole transaction.

I wrote this article illustrated with a full example.

Answer 3

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.