title: Hibernate Pessimistic Locking not working for Oracle Database?
body: <p>I have a transactional method that could be called by multiple threads. </p>
  
  <p>To avoid <code>StaleStateException</code> when concurrent calls happen, I used pessimistic locking in hibernate, but it's not working as I expected and I still got the <code>StaleStateException</code>. Then I looked at the log, found that the objects are not locked properly, here's my log:</p>
  
  <pre><code>2014-09-17_19:12:19.078 INFO  c.c.p.a.w.m.ImportExportManagerImpl - ************************Test Name: Requirement Coverage Test
  2014-09-17_19:12:19.079 INFO  c.c.p.a.w.m.ImportExportManagerImpl - ************************Test folder ID: 9312
  2014-09-17_19:12:19.525 INFO  c.c.p.a.w.m.ImportExportManagerImpl - Test Case Id: P072051933
  2014-09-17_19:12:19.615 WARN  org.hibernate.loader.Loader - HHH000444: Encountered request for locking however dialect reports that database prefers locking be done in a separate select (follow-on locking); results will be locked after initial query executes
  Hibernate: 
      select
          requiremen0_."RC_ITEM_ID" as RC_ITEM_ID1_1_,
          requiremen0_."RC_ENTITY_ID" as RC_ENTITY_ID2_1_,
          requiremen0_."RC_ENTITY_TYPE" as RC_ENTITY_TYPE3_1_,
          requiremen0_.RC_REQ_ID as RC_REQ_ID4_1_ 
      from
          "OME6500_OM65_OME6500_R9_2_DB"."REQ_COVER" requiremen0_ 
      where
          requiremen0_."RC_ENTITY_ID"=?
  Hibernate: 
      select
          requiremen0_."RQ_REQ_ID" as RQ_REQ_ID1_2_0_,
          requiremen0_."RQ_USER_03" as RQ_USER_2_2_0_ 
      from
          "OME6500_OM65_OME6500_R9_2_DB"."REQ" requiremen0_ 
      where
          requiremen0_."RQ_REQ_ID"=?
  Hibernate: 
      select
          "RC_ITEM_ID" 
      from
          "OME6500_OM65_OME6500_R9_2_DB"."REQ_COVER" 
      where
          "RC_ITEM_ID" =? for update
  
  2014-09-17_19:12:19.631 INFO  c.c.p.a.w.m.ImportExportManagerImpl - Requirement Coverages Size:1
  2014-09-17_19:12:19.631 INFO  c.c.p.a.w.m.ImportExportManagerImpl - Deleting requirement coverage id: 130967
  2014-09-17_19:12:19.634 INFO  c.c.p.a.w.m.ImportExportManagerImpl - Requirement in testcase table Size:1
  2014-09-17_19:12:19.669 INFO  c.c.p.a.w.m.ImportExportManagerImpl - Checking if requirement coverage exists: test case id: 51933, req id:  7760
  Hibernate: 
      delete 
      from
          "OME6500_OM65_OME6500_R9_2_DB"."REQ_COVER" 
      where
          "RC_ITEM_ID"=?
  </code></pre>
  
  <p>My transactional method is here:</p>
  
  <pre><code>@Transactional(readOnly = false)
      public void importTestCases(String domain, String project, List&lt;TestCase&gt; testCases,
                                  Boolean onlyUpdateReqCover, Boolean foldersExist)
              throws RequestFailureException, RESTAPIException, InvalidDataException,
              UnAuthorizedOperationException {
  
          setDBSchema(domain, project);
  
          for (TestCase testCase : testCases) {
              TestFolder testFolder = retrieveTestFolderFromPath(domain, project, testCase.getFolderPath(),
                                                                 foldersExist, testCase);
              Test test = new Test(testCase, testFolder);
              ALMEntity almEntity = null;
              LOGGER.info("************************Test Name: " + test.getName());
              LOGGER.info("************************Test folder ID: " + test.getParent_id());
              ...
              ...
              LOGGER.info("Test Case Id: " + existingTest.getQc_tcid());
  
              List&lt;RequirementCoverage&gt; requirementCoverages = requirementCoverageDao.findAllFromTestId(Integer
                      .parseInt(existingTest.getId()));
  
              LOGGER.info("Requirement Coverages Size:" + requirementCoverages.size());
  
              for (RequirementCoverage requirementCoverage : requirementCoverages) {
                  LOGGER.info("Deleting requirement coverage id: " + requirementCoverage.getId());
                  requirementCoverageDao.delete(requirementCoverage);
              }
  
             ...
             ...
             }
  }
  </code></pre>
  
  <p>Here's my DAO method with locking:</p>
  
  <pre><code>@Override
      public List&lt;RequirementCoverage&gt; findAllFromTestId(int testId) {
          List&lt;RequirementCoverage&gt; list = sessionFactory.getCurrentSession()
                  .createQuery("from RequirementCoverage where entityId = :testId")
                  .setLockOptions(new LockOptions(LockMode.PESSIMISTIC_WRITE)).setParameter("testId", testId)
                  .list();
          return list;
      }
  </code></pre>
  
  <p>As you can see from the log, <code>requirementCoverages</code> returned by <code>findAllFromTestId</code> are not locked. The lock is obtained for each <code>requirementCoverage</code>only after the <code>for (RequirementCoverage requirementCoverage : requirementCoverages)</code> start.</p>
  
  <p>So I think the cause of <code>StaleStateException</code> is that one thread gets the list <code>requirementCoverages</code> and trying to access each of the object, but at the same time <code>requirementCoverages</code> has been modified by another thread.</p>
  
  <p>Am I right? Is there a way to lock the list <code>requirementCoverages</code> blocking another thread to access it? Any help is appreciated.</p>