How to make Hibernate @Lock annotation work for Oracle DB?

Question

I stumbled upon a problem with locking row in Oracle DB. The purpose of the lock is to prevent more than one transaction reading data from the DB because this data influences the generation of new data and is changed in terms of a transaction.

In order to make the lock, I've put the @Lock annotation over SpringData find method which retrieves data that participates in the transaction.

@Lock(LockModeType.PESSIMISTIC_WRITE)
User findUserById(@Param("id") String operatorId);

After this code is implemented I get log message

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

Besides, it has no effect and causes

org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [insert into ...]

The issue can be solved when rewriting the lock using entity manager

entityManager.lock(userByIdWithLockOnReadWrite, LockModeType.PESSIMISTIC_WRITE);    

or

entityManager.unwrap(Session.class).lock(userByIdWithLockOnReadWrite, LockMode.PESSIMISTIC_WRITE);

The issue doesn't appear on MariaDB (MySQL).

Maybe there are some special rules of using the annotation?

Answer 1

You said that:

The purpose of the lock is to prevent more than one transaction reading data from the DB because this data influences the generation of new data and is changed in terms of a transaction.

Oracle uses MVCC (Multiversion Concurrency Control) so Readers don't block Writers and Writers don't block Readers. Even if you acquire a row-level lock with Oracle, and you modify that row without committing, other transactions can still read the last committed value.

Related to this log message:

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

The follow-on locking mechanism is due to Oracle not being able to apply the lock when doing Oracle 11g pagination, using DISTINCT or UNION ALL.

If you're using Oracle 12i, then you can update the Hibernate dialect to Oracle12cDialect and pagination and locking will work fine since Oracle 12 uses the SQL standard pagination and it no longer requires a derived table query.

This does not happen in MariaDB or any other database. It's just an Oracle pre-12 limitation.

If you are using Hibernate 5.2.1, we added a new hint HINT_FOLLOW_ON_LOCKING which disables this mechanism.

So, your Spring Data query becomes:

@QueryHints(value = { @QueryHint(name = "hibernate.query.followOnLocking", value = "false")}, forCounting = false)
@Lock(LockModeType.PESSIMISTIC_WRITE)
User findUserById(@Param("id") String operatorId);

you can also apply it manually:

User user = entityManager.createQuery(
    "select u from User u where id = :id", User.class)
.setParameter("id", id);
.unwrap( Query.class )
.setLockOptions(
    new LockOptions( LockMode.PESSIMISTIC_WRITE )
        .setFollowOnLocking( false ) )
.getSingleResult();