CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Averias
Averias

Reputation: 991

Doctrine Pessimistic Lock

Scenario:

I am implementing an application in Symfony2 running a command every five minutes (cronjob) that goes through a MySql table and in each record, first read a json_array field, performs a series of calculations and finally saves the array with new data in the same field. Moreover there is a web application where a user can edit and save this data in the same table

To avoid concurrency, if the command accesses a record I make a pessimistic lock so in that way, if user, at that exact moment, changes the data have to wait until the end of the transaction and when it finishes user data are saved.

But when user saves the data, randomly there is a bug, and user data are not saved and the web applications shows previous data, that tells me that the lock is not successful.

Code in the symfony2 command where I made pessimistic lock:

    foreach ($this->cisOfferMetaData as $oldCisOfferMeta) {
        // calculate budget used for each timeframe case and save it

        // begin transaction and Lock cisOfferMEta Entity
        $this->em->getConnection()->beginTransaction();
        try {
            $cisOfferMeta = $this->em->getRepository('CroboCisBundle:CisOfferMeta')->find(
                $oldCisOfferMeta->getId(),
                LockMode::PESSIMISTIC_READ
            );

            $budget = $cisOfferMeta->getBudgetOffer();

            foreach (
                generator(CisOfferMeta::getTypesArray(), CisOfferMeta::getTimeframesArray())
                as $type => $timeframe
            ) {
                if (isset($budget[$type][$timeframe]['goal'])) {
                    // if type=budget we need revenue value, if type=conversion, conversions value
                    $budget[$type][$timeframe]['used'] =
                        ($type === 'conversion')
                            ? intval($allTimeframes[$key]['conversions'][$timeframe])
                            : round($allTimeframes[$key]['revenue'][$timeframe], 2);

                    $budget[$type][$timeframe]['percent_reached'] =
                        ($budget[$type][$timeframe]['used'] == 0.0)
                            ? 0.0
                            : round(
                            $budget[$type][$timeframe]['used'] / intval($budget[$type][$timeframe]['goal']) * 100,
                            2
                        );
                }
            }

            $budget['current_conversions'] = $allTimeframes[$key]['conversions'];
            $budget['current_revenue'] = $allTimeframes[$key]['revenue'];

            $cisOfferMeta->setBudgetOffer($budget);

            $this->em->flush($cisOfferMeta);
            $this->em->getConnection()->commit();
        } catch (PessimisticLockException $e) {
            $this->em->getConnection()->rollback();
            throw $e;
        }

    }

Am I doing something wrong? I guess since the transaction is started until changes are commited, if a user attempts to read or update data, have to wait until the lock is released from that entity which is blocked.

Reading the Doctrine documentation is not clear if I should add versioning in the entity

Upvotes: 2

Views: 5909

Answers (1)

Averias
Averias

Reputation: 991

Finally this code worked properly and made the pessimistic lock, the problem was in a Listener that read data prior to this lock and then flushed without changes after lock was released.

Upvotes: 1

Related Questions