oracle 从 JPA 级别选择更新跳过锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41434169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select for update skip locked from JPA level
提问by Dominik Kunicki
In my application - Oracle with JPA (EclipseLink), I'm using the following expression to lock the subset of the records in some tables:
在我的应用程序 - Oracle with JPA (EclipseLink) 中，我使用以下表达式来锁定某些表中的记录子集：
select * from MY_TABLE where MY_CONDITIONS for update skip locked
I run it throughout native query, but I have to write that query for all required entities.
我在整个本机查询中运行它，但我必须为所有必需的实体编写该查询。
Is there any way to skip locked records using pure JPA? Can I implement my own locking policy?
有没有办法使用纯 JPA 跳过锁定的记录？我可以实施我自己的锁定策略吗？
I don't mind changing JPA provider but I want to use JPA API.
我不介意更改 JPA 提供程序，但我想使用 JPA API。
采纳答案by Rémi Bantos
Hibernate provides the UPGRADE_SKIPLOCKED Lock mode.
Hibernate 提供了 UPGRADE_SKIPLOCKED 锁定模式。
Using JPA and Hibernate, to produce a "SKIP_LOCKED" as per Hibernate LockModedocumentation, you have to combine the PESSIMISTIC_WRITEJPA LockModeType:
使用 JPA 和 Hibernate，要根据Hibernate LockMode文档生成“SKIP_LOCKED” ，您必须结合PESSIMISTIC_WRITEJPA LockModeType：
entityManager.find(Department.class, 1, LockModeType.PESSIMISTIC_WRITE);
and the Lock timeout setting, like for example in persistence.xml for your persistence unit:
和锁定超时设置，例如在persistence.xml 中的持久化单元：
<properties>
<property name="javax.persistence.query.timeout" value="-2"/>
</properties>
(Note that you can configure this LockMode for complex query as well)
（请注意，您也可以为复杂查询配置此 LockMode）
SKIP LOCKED is not part of ANSI SQL. Some RDBMS such the following provide this as a specific feature:
SKIP LOCKED 不是 ANSI SQL 的一部分。某些 RDBMS（如下所示）将此作为特定功能提供：
So with pure JPA, it is not possible to specify a "SKIP LOCKED" in queries. Indeed, as documented in LockModeType, JPA 2.1 only supports the following:
因此，对于纯 JPA，不可能在查询中指定“SKIP LOCKED”。实际上，如LockModeType 中所述，JPA 2.1 仅支持以下内容：
- NONE
- OPTIMISTIC
- OPTIMISTIC_FORCE_INCREMENT
- PESSIMISTIC_FORCE_INCREMENT
- PESSIMISTIC_READ
- PESSIMISTIC_WRITE
- READ
- WRITE
- 没有任何
- 乐观的
- OPTIMISTIC_FORCE_INCREMENT
- 悲观_FORCE_INCREMENT
- 悲观_阅读
- 悲观_写
- 读
- 写
However, to enable SKIP LOCKED in your query you can use these alternatives:
但是，要在查询中启用 SKIP LOCKED，您可以使用以下替代方法：
- Use specific JPA implementation feature, such as Hibernate LockModewhich allows to specify the SKIP LOCKED via a JPA query, thanks to a combination of PESSIMISTIC_WRITELockModeType Lock Timeout specific setting as described above
- Create a native SQL query as you did
- 使用特定的 JPA 实现功能，例如Hibernate LockMode，它允许通过 JPA 查询指定 SKIP LOCKED，这要归功于上述PESSIMISTIC_WRITELockModeType 锁定超时特定设置的组合
- 像您一样创建本机 SQL 查询
回答by Pawel Zieminski
I know this post is a bit old, but for the record, just as the accepted answer stated, "javax.persistence.lock.timeout" (org.hibernate.cfg.AvailableSettings#JPA_LOCK_TIMEOUT) set to "-2" (org.hibernate.LockOptions#SKIP_LOCKED) with Hibernate results in "SKIP LOCKED". However, this can be done at run-time without having to set any global settings.
我知道这篇文章有点旧，但作为记录，正如公认的答案所述，“javax.persistence.lock.timeout”（org.hibernate.cfg.AvailableSettings#JPA_LOCK_TIMEOUT）设置为“-2”（org.hibernate.LockOptions#SKIP_LOCKED），Hibernate 导致“SKIP LOCKED”。但是，这可以在运行时完成，而无需设置任何全局设置。
Since 2.0 JPA allows to pass hintsalong like so
entityManager.find(MyType.class, id, LockModeType.PESSIMISTIC_WRITE, new HashMap<String, Object>() {{
put("javax.persistence.lock.timeout", "-2");
}});
回答by gregory
Oracle doesn't provide a read lock, since it doesn't need one; undo logs make it unnecessary. So, SELECT...FOR UPDATE is really just a WRITE lock for Oracle.
Oracle 不提供读锁，因为它不需要；撤消日志使其变得不必要。因此，SELECT...FOR UPDATE 实际上只是 Oracle 的一个 WRITE 锁。
Using JPA, you want to set the LockModeType as a PESSIMISTIC_WRITE.
使用 JPA，您希望将 LockModeType 设置为 PESSIMISTIC_WRITE。
