title: Doctrine Pessimistic Lock
body: <p>Scenario:</p>
  
  <p>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</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Code in the symfony2 command where I made pessimistic lock:</p>
  
  <pre><code>    foreach ($this-&gt;cisOfferMetaData as $oldCisOfferMeta) {
          // calculate budget used for each timeframe case and save it
  
          // begin transaction and Lock cisOfferMEta Entity
          $this-&gt;em-&gt;getConnection()-&gt;beginTransaction();
          try {
              $cisOfferMeta = $this-&gt;em-&gt;getRepository('CroboCisBundle:CisOfferMeta')-&gt;find(
                  $oldCisOfferMeta-&gt;getId(),
                  LockMode::PESSIMISTIC_READ
              );
  
              $budget = $cisOfferMeta-&gt;getBudgetOffer();
  
              foreach (
                  generator(CisOfferMeta::getTypesArray(), CisOfferMeta::getTimeframesArray())
                  as $type =&gt; $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-&gt;setBudgetOffer($budget);
  
              $this-&gt;em-&gt;flush($cisOfferMeta);
              $this-&gt;em-&gt;getConnection()-&gt;commit();
          } catch (PessimisticLockException $e) {
              $this-&gt;em-&gt;getConnection()-&gt;rollback();
              throw $e;
          }
  
      }
  </code></pre>
  
  <p>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.</p>
  
  <p>Reading the Doctrine documentation is not clear if I should add versioning in the entity</p>