Hibernate Pessimistic Locking not working for Oracle Database?

Question

I have a transactional method that could be called by multiple threads.

To avoid StaleStateException when concurrent calls happen, I used pessimistic locking in hibernate, but it's not working as I expected and I still got the StaleStateException. Then I looked at the log, found that the objects are not locked properly, here's my log:

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"=?

My transactional method is here:

@Transactional(readOnly = false)
    public void importTestCases(String domain, String project, List<TestCase> 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<RequirementCoverage> 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);
            }

           ...
           ...
           }
}

Here's my DAO method with locking:

@Override
    public List<RequirementCoverage> findAllFromTestId(int testId) {
        List<RequirementCoverage> list = sessionFactory.getCurrentSession()
                .createQuery("from RequirementCoverage where entityId = :testId")
                .setLockOptions(new LockOptions(LockMode.PESSIMISTIC_WRITE)).setParameter("testId", testId)
                .list();
        return list;
    }

As you can see from the log, requirementCoverages returned by findAllFromTestId are not locked. The lock is obtained for each requirementCoverageonly after the for (RequirementCoverage requirementCoverage : requirementCoverages) start.

So I think the cause of StaleStateException is that one thread gets the list requirementCoverages and trying to access each of the object, but at the same time requirementCoverages has been modified by another thread.

Am I right? Is there a way to lock the list requirementCoverages blocking another thread to access it? Any help is appreciated.

Answer 1

There is another option using LockMode.PESSIMISTIC_FORCE_INCREMENT, check out this solution.

Answer 2

I solved this problem with restructure my code.

Instead of use pessimistic locking on a named query of a few entities, I apply the pessimistic locking on the parent entity of these entities and lock it when it's read.

Then do the update on these sub entities.

At last, release the parent entity.