Concurrency in Doctrine

doctrine transaction
doctrine nested transactions
doctrine flush
symfony 3 doctrine transaction
doctrine deadlock exception
doctrine select for update
doctrine findby
doctrine atomic increment

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?

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.

Concurrency. Doctrine MongoDB ODM offers native support for pessimistic and optimistic locking strategies. This allows for very fine-grained control over what  Concurrency control becomes the partial responsibility of the application itself. Doctrine has integrated support for automatic optimistic locking via a version field.

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

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. Each time a RetryableException is thrown by Doctrine, the proper way to handle this is to retry the whole transaction. The doctrine of concurrence = “God from all eternity, did, by the most wise and holy counsel of His own will, freely, and unchangeably ordain whatsoever comes to pass: yet so, as thereby neither is God the author of sin, nor is violence offered to the will of the creatures; nor is the

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.

Yet, if you search for Transactions and Concurrency in the official documentation, you will find the first bricks to reach our goal. The Doctrine  I have an issue when selecting the next record with Doctrine when there is concurrency. I have installed supervisord inside a docker container that starts multiple processes on the same "dispatch" command. The dispatch commands basically gets the next job in queue in the db and sends it to the right executor.

Doctrine: Transactions and Concurrency (avoiding duplicate inserts). Hi,. Apologies first hand if this is the wrong place to post this but the doctrine subreddit is  The doctrine of divine concurrence is opposed to two extremes: deism, which admits creation by God as the origin of the universe at its first moment, but denies all subsequent influence of God on creatures, and occasionalism, which denies that creatures have any true causality of their own and holds that on the occasion of creatures' presence to one another, God as sole and total cause effects whatever comes into being.

with Symfony transactions and surprised that (as far as I can tell, please correct me if I'm wrong) that the documentation available is limited to http://doctrine Concurrency Doctrine MongoDB ODM offers native support for pessimistic and optimistic locking strategies. This allows for very fine-grained control over what kind of locking is required for documents in your application.

in your entity the field version. Automatically doctrine will manage concurrency throwing an Exceprion. use Doctrine\ORM\Mapping as ORM; . The isolationist position of the Monroe Doctrine was also a cornerstone of U.S. foreign policy in the 19th century, and it took the two world wars of the 20th century to draw a hesitant America

Comments
  • Are you planning on reading/writing from/to one single table, or several tables?
  • If you go for transactions, the problem gets too vast to be addressed in the general case. My best advice would be: read a lot about concurrency, know how MySQL locks/transactions work. You may want to describe in details the logic of your program (what do you insert, under which condition would you update which row).
  • @puty: very for the many edits. I misread your initial question in the first place. I didn't see your question was actually quite specific.