[hibernate-dev] Follow on locking usability enhancement for Oracle

[Vlad Mihalcea]

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