Hi, After reviewing the PR for https://hibernate.atlassian.net/browse/HHH-9486, I realized we could indeed improve the follow on locking for Oracle.
The issue with Oracle is partly explained in the Oracle docs: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#sthref7465 The basic idea is that FOR UPDATE does not work with GROUP BY, DISTINCT, and also nested selects which is what we use for pagination. For those, the setMaxResults works, but not setFirstResult. Also, ORDER BY causes some issues too. This way, we could do something like this: 1. In Dialect, we deprecate useFollowOnLocking and add a new method to take QueryParameters @Deprecated public boolean useFollowOnLocking() { return useFollowOnLocking( null ); } public boolean useFollowOnLocking(QueryParameters parameters) { return false; } 2. In Oracle82Dialect: @Override public boolean useFollowOnLocking(QueryParameters parameters) { String lowerCaseSQL = parameters.getFilteredSQL().toLowerCase(); return parameters.hasRowSelection() && ( parameters.getRowSelection().getFirstRow() != null || lowerCaseSQL.contains( "distinct" ) || lowerCaseSQL.contains( "group by" ) || lowerCaseSQL.contains( "order by" ) ); } 3. We could also add a way for the user to override this behavior in Dialect if he knows that the underlying statement works. Although we include all those safety checks, we might miss some use case, and this way the user can have a better control on the follow on locking approach. For this we could add a setFollowOnLocking on LockOptions. List<Product> products = session.createQuery( "select p from Product p order by p.id", Product.class ) .setLockOptions( new LockOptions( LockMode.PESSIMISTIC_WRITE ).setFollowOnLocking( true ) ) .setMaxResults( 10 ) .getResultList(); If the user specified an explicit setFollowOnLocking, then we ignore the Dialect logic and just go with his option even if that means that he might get an exception. What do you think fo this? Vlad _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
