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 ``` entity…

Accepted Answer

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();
```

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

Solution

Problem