ok, but I'm assuming that what I'm trying IS using a different transaction.
I'm locking the row with a query that comes from DOCTRINE (that should be an SELECT FOR UPDATE) and, while this transaction is pending (I stopped the php execution with an xdebug breakpoint just after the lock, before the commit of the transaction), I'm trying to do another simple SELECT from the mysql cli for the same row. But the latter simple query is succeeding, while I was expected a failure for timeout. On 22 April 2016 at 19:18, Marco Pivetta <[email protected]> wrote: > Yes, if it is a different transaction, and therefore your child process > will wait forever, hoping for a lock to be released. > > Marco Pivetta > > http://twitter.com/Ocramius > > http://ocramius.github.com/ > > On 22 April 2016 at 20:13, 'francesco' via doctrine-user < > [email protected]> wrote: > >> I see your point, but I'm trying to query (SELECT) the interested row >> from the cli while the lock is applied by a php process. So I assume I >> should be using a different transaction. >> >> >> On Friday, 22 April 2016 18:58:28 UTC+1, Marco Pivetta wrote: >> >>> On 22 April 2016 at 19:42, 'francesco' via doctrine-user < >>> [email protected]> wrote: >>> >>>> Hi there, >>>> >>>> struggling trying to understand how the PESSIMISTIC_LOCK is working. >>>> >>>> I have this code: >>>> >>>> $this->getEntityManager()->getConnection()->beginTransaction(); >>>> >>>> $queryBuilder = $this->getEntityManager()->createQueryBuilder(); >>>> $query = $queryBuilder >>>> ->select('foo') >>>> ->from(Foo::class, 'foo') >>>> ->where('foo.id = :fooId') >>>> ->setParameter('fooId', $fooId) >>>> ->getQuery(); >>>> >>>> $query->setLockMode(LockMode::PESSIMISTIC_WRITE); >>>> >>>> >>>> inside my repository. >>>> >>>> If I try to do to update that row that I'm selecting with the lock >>>> during the transaction (so before the commit of the transaction), the lock >>>> works properly. >>>> >>> >>> >>> That seems about right to me. In general: >>> >>> 1. start transaction >>> 2. do something with a pessimistic lock (find) >>> 3. interact with the data (multiple reads allowed too) >>> 4. commit >>> >>> >>>> But if a try to SELECT the same row during the transaction, I'm not >>>> having any problem at retrieving data. I was expecting the same behaviour >>>> for read and write because of the doctrine documentation (here >>>> <http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html#pessimistic-locking> >>>> ): >>>> >>>> Pessimistic Write (Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE), locks >>>> the underlying database rows for concurrent *Read and Write* >>>> Operations. >>>> >>> >>> That seems correct: the DB-level transaction should be the owner of the >>> lock, so you can re-fetch the data as many times as you want. Once the >>> transaction is complete, other transactions should be able to access the >>> previously records. >>> >>> Marco Pivetta >>> >>> http://twitter.com/Ocramius >>> >>> http://ocramius.github.com/ >>> >> >> *This email is sent according to our standard email disclaimer >> <https://www.lendinvest.com/disclosures/email-disclaimer/>.* >> >> -- >> You received this message because you are subscribed to the Google Groups >> "doctrine-user" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at https://groups.google.com/group/doctrine-user. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to a topic in the > Google Groups "doctrine-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/doctrine-user/HVk2oOUZ17U/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/doctrine-user. > For more options, visit https://groups.google.com/d/optout. > -- Francesco Lo Franco - Backend Developer @LendInvest -- *This email is sent according to our standard email disclaimer <https://www.lendinvest.com/disclosures/email-disclaimer/>.* -- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/d/optout.
