Why does Hibernate execute 2 SQL query when using a PESSIMISTIC_WRITE LockMode on a JPQL query?

Hibernate ORM
1

I am using hibernate-entitymanager-5.0.8.Final running against an Oracle database.

What I do is

entityManagerProvider.getEntityManager().createQuery("select mt from MyTableEntity mt where mt.attribut1 = :attribut1")
                .setParameter("attribut1", attribut1)
                .setLockMode(LockModeType.PESSIMISTIC_WRITE).setHint("javax.persistence.lock.timeout", 0)
                .getResultList();

and had expected that this results to something like that:

select * from TBL_MY_TABLE where attribut1=? for update nowait

Instead i must see that this results in two queries against the database:

Hibernate: 
    /* select
        mt 
    from
        MyTableEntity mt 
    where
        and mt.attribut1 = :attribut1 */ select
            mytableent0_.id as id1_1_,
            mytableent0_.ATTRIBUT1 as ATTRIBUT2_1_,
            mytableent0_.ATTRIBUT2 as ATTRIBUT3_1_,
         from
            TBL_MY_TABLE mytableent0_ 
        where
            and mytableent0_.ATTRIBUT1=? 
Hibernate: 
    /* PESSIMISTIC_WRITE lock myCompany.MyTableEntity */ select
        id 
    from
        TBL_MY_TABLE 
    where
        id =? for update
            nowait

Is there a way to workapound this unnecessary roundtrip with the database?
Any hint is welcome!
Thanks in advance
Martin

2

I tested it with Hibernate 5.4, and ran this JPQL query:

List<PostComment> comments = entityManager
.createQuery(
	"select pc " +
	"from PostComment pc " +
	"join fetch pc.post p ", PostComment.class)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setHint("javax.persistence.lock.timeout", 0)
.getResultList();

And this the query generated by Hibernate:

DEBUG [Alice]: Query:["
SELECT lockmodepe0_.id AS id1_1_0_,
       lockmodepe1_.id AS id1_0_1_,
       lockmodepe0_.post_id AS post_id4_1_0_,
       lockmodepe0_.review AS review2_1_0_,
       lockmodepe0_.version AS version3_1_0_,
       lockmodepe1_.body AS body2_0_1_,
       lockmodepe1_.title AS title3_0_1_,
       lockmodepe1_.version AS version4_0_1_
FROM post_comment lockmodepe0_
INNER JOIN post lockmodepe1_ ON lockmodepe0_.post_id=lockmodepe1_.id
FOR UPDATE OF lockmodepe1_, lockmodepe0_ 
NOWAIT
"]

Here’s the test case which you can run on your local machine.

What HIbernate ORM version are you using?

3

I am using JBoss EAP 7.0 and found the following there:

hibernate-search-orm-5.5.2.Final-redhat-1.jar

Hope that answers your question,
Martin

4

That’s the Hibernate Search dependency. Hibernate ORM does not have a 5.5 branch.

5

I also found:
hibernate-core-5.0.9.Final-redhat-1.jar and
hibernate-entitymanager-5.0.9.Final-redhat-1.jar
Does that answer your question?
Thanks in advance
Martin

6

Hibernate 5.0.9 might have this issue. I couldn’t replicate it with 5.4, so you should upgrade the Hibernate version.