title: Two concurrent request were able to lock the same row in Postgres sql
body: <p><strong>when two concurrent request were made for the below code, both of the requests were able to acquire lock simultaneously and hence were able to execute the block of code</strong></p>
  <p>Sample code running in production:</p>
  <p>Sample Code for reference :</p>
  <pre><code>//Starting point for the request
  @Override
      public void receiveTransferItems(String argumet1, String refernceId, List&lt;Item&gt; items, long messageId)
              throws Exception {
          ParentDTO parent = DAO.lockByReferenceid(referenceId);
          if (parent == null) {
              throw new Exception(referenceId + &quot;does not exist&quot;);
          }
  
          updateData(parent);
  
          for (Item item : items) {
              receiveItem(td, td.getWarehouseId(), item.getItemSKU(), item.getItemStatus(), item.getQtyReceived(), messageId);
          }
  
      }
      
      
      private void updateData(ParentDTO td) throws DropShipException {
  
          //perform some logical processing and then execute update 
          DAO.update(td);
      }
      
      
      
       private void receiveItem(ParentDTO td, String warehouseId, String asin, String itemStatus, int quantity, long messageId)
              throws Exception {
      
          /**
           * perform some logical processing
           * 
           **/
          //call is being made to another class to do the rest of the processing
          service.receive(td, asin, quantity, condition, container, messageId);
      }
  </code></pre>
  <pre><code>@Override
      public void receive(
              ParentDTO parentDTO,
              String asin,
              int quantity,
              Condition condition,
              Container container,
              long messageId,
              DataAccessor accessor) throws Exception {
  
  
  
          List&lt;ChildDTO&gt; childDTOs =
              DAO.lockChildDTOItems(parentDTO.getReferenceId(), asin, condition,
                                                    CostInfoSource.MANIFEST);
  
          List&lt;ChildDTO&gt; filterItems = DAO
              .loadChildDTOItems(parentDTO.getReferenceId(), asin, condition.name());
  
          long totalExpectedQuantity = getTotalExpectedQuantity(filterItems);
          long totalReceivedQuantity = getTotalReceivedQuantity(filterItems);
  
          int quantityNormalReceived = 0;
          for (ChildDTO tdi : childDTOs) {
          
              int quantityReceived = 0;
              if (asinDropShipMsgAction != null) {
                  quantity -= asinDropShipMsgAction.getInitialQuantity();
                  quantityNormalReceived += asinDropShipMsgAction.getInitialQuantity();
              } else {
                  quantityReceived = new DBOperationRunner&lt;Integer&gt;(accessor.getSessionManager()) {
                      @Override
                      protected Integer doWorkAndReturn() throws Exception {
                          return normalReceive(tdi, quantityLeft, container, MessageActionType.TS_IN, messageId);
                      }
                  }.execute();
              }
            
          }
  
      }
      
      
      
      private int normalReceive(final ChildDTO childDTO,
                                int quantity,
                                final Container container,
                                final MessageActionType type,
                                long messageId)
          throws Exception {
  
          /**
           * perform some business logic
           * 
           * */
           
          DAO.update(childDTO);
          return someQuantity;
      }
  </code></pre>
  <p>Implementation for lockByReferenceId function:</p>
  <pre><code>@Override
      public ParentDTO lockByReferenceId(String referenceId) {
          Criteria criteria = getCurrentSession().createCriteria(ParentDTO.class)
              .add(Restrictions.eq(&quot;referenceId&quot;, referenceId)).setLockMode(LockMode.UPGRADE_NOWAIT);
          return (ParentDTO) criteria.uniqueResult();
      }
  </code></pre>
  <p>Implementation of DBOperationRunner class :</p>
  <pre><code>public T execute() throws Exception {
          T t = null;
          Session originalSession = (Session) ThreadLocalContext.get(ThreadLocalContext.CURRENT_SESSION);
          try {
              ThreadLocalContext.put(ThreadLocalContext.CURRENT_SESSION, sessionManager.getCurrentSession());
              sessionManager.beginTransaction();
              t = doWorkAndReturn();
              sessionManager.commit();
  
          } catch (Exception e) {
              try {
                  sessionManager.rollback();
              } catch (Throwable t1) {
                  logger.error(&quot;failed to rollback&quot;, t1);
              }
              throw e;
          } finally {
              ThreadLocalContext.put(ThreadLocalContext.CURRENT_SESSION, originalSession);
          }
  
          return t;
      }
  </code></pre>
  <p>Recently i observed one issue in production code in which two or more simultaneous requests were able to acquire lock on the same data at same time.
  I am using hibernate and criteria as a DB framework and c3p0 as a connection pooling framework and Postgres as DB.
  Note : This issue is intermittent and only observed for some random concurrent requests which is making it hard to debug.</p>
  <p>I am unable to understand how two concurrent request were able to lock the same rows simultaneously. Can you please help me in identifying what is going wrong in this case?</p>
  <p>Thanks in advance!!!!</p>