问题描述
我偶然发现了Oracle DB中锁定行的问题。锁定的目的是防止多个事务从数据库读取数据，因为这些数据会影响新数据的生成并且在事务方面会发生变化。
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.
为了进行锁定，我将@Lock注释放在SpringData查找方法上，该方法检索参与事务的数据。
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);
或
entityManager.unwrap(Session.class).lock(userByIdWithLockOnReadWrite, LockMode.PESSIMISTIC_WRITE);
此问题未出现在MariaDB（MySQL）上。
The issue doesn't appear on MariaDB (MySQL).
也许有一些使用注释的特殊规则？
Maybe there are some special rules of using the annotation?
推荐答案
你说：
Oracle使用MVCC（多版本并发控制），因此读者不会阻止作家和作家不阻止读者。即使您使用Oracle获得行级锁定，并且在未提交的情况下修改该行，其他事务仍然可以读取最后提交的值。
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.
与此日志消息相关：
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
是由于甲骨文不能够应用该锁在进行Oracle 11g分页时，使用 DISTINCT 或 UNION ALL 。
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.
如果您正在使用Oracle 12i，那么您可以将Hibernate方言更新为 Oracle12cDialect ，并且分页和锁定将正常工作，因为Oracle 12使用SQL标准分页并且它不再需要派生表查询。
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.
这不会发生在MariaDB或任何其他数据库中。这只是Oracle 12之前的限制。
This does not happen in MariaDB or any other database. It's just an Oracle pre-12 limitation.
如果您使用的是Hibernate 5.2.1，我们添加了一个新提示即禁用这个机制。
If you are using Hibernate 5.2.1, we added a new hint HINT_FOLLOW_ON_LOCKING which disables this mechanism.
因此，您的Spring Data查询变为：
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);
您也可以手动申请：
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();
这篇关于如何使Hibernate @Lock注释适用于Oracle DB？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！