Hi Hibernate team,
I upgraded Hibernate from version 5.x to version 6.x, and I’m facing a problem that I need help with.
This happens even on the newest version of Hibernate. Version: 6.6.13.Final.
Problem:
I’m using locking on an entity like this:
Entity e = entityManager.find(Entity.class, 1L, LockModeType.PESSIMISTIC_WRITE, hints);
This Entity has connected another Entity2 that uses @Inheritance(strategy = InheritanceType.SINGLE_TABLE) and @DiscriminatorColumn
Hibernate will generate a query that fails on syntax parsing on MSSQL (any version).
Error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘with’.
Query:
select
…
from Entity1 e with (updlock, holdlock, rowlock)
left join (select
*
from
Entity2 t
where
t.DTYPE=‘entity2’) e2 with (updlock, holdlock, rowlock)
on 1.entity2_ID=e2.id )
where
e.ID=?
And MSSQL is complaining about the 2nd with (updlock, holdlock, rowlock) that cannot be used on subselects.
I know that in version 6, you came up with some speed optimization for these subselects.
But what to do in this case?
Rewriting to native query, giving up locking, or giving up inheritance is not a way for me.
Thanks a lot!