问题描述
我想使用 Spring Data JPA 在 Oracle 上执行 SKIP LOCKED
查询,所以我尝试了以下操作:
I want to execute a SKIP LOCKED
query on Oracle using Spring Data JPA, so I tried the following:
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query(value = "SELECT * FROM User WHERE ID=?1 FOR UPDATE SKIP LOCKED", nativeQuery = true)
User findOne(UUID id);
我尝试了上面的方法,发现生成的查询包含FOR UPDATE
,但不包含SKIP LOCKED
(以下是从日志生成的查询):
I tried the above and found that the generated query contains FOR UPDATE
, but not SKIP LOCKED
(below is the generated query from logs):
select ent0_.column1 as name, ent0_.CREATED_DATE as CREATED_2_33_0_ from TABLE_NAME alias_name where ent0_.column1=? for update
如果我从查询方法中删除@Lock
,生成的查询甚至没有FOR UPDATE
.
If I remove @Lock
from the query method, the generated query does not even have FOR UPDATE
.
请建议我如何根据需要使用 FOR UPDATE SKIP LOCKED
生成查询.
Please suggest how I can generate a query with FOR UPDATE SKIP LOCKED
, as required.
推荐答案
您可以将 -2
分配给超时值,以便尽可能使用跳过锁定".
You can assing -2
to timeout value so that 'skip locked' will be used whenever possible.
PESSIMISTIC_WRITE,javax.persistence.lock.timeout 设置为 -2
UPGRADE_SKIPLOCKED
UPGRADE_SKIPLOCKED
锁获取请求会跳过已经锁定的行.它使用一个SELECT ... FOR UPDATE SKIP LOCKED 在 Oracle 和 PostgreSQL 9.5 中,或SELECT ... with (rowlock, updlock, readpast) 在 SQL Server 中.
The lock acquisition request skips the already locked rows. It uses a SELECT … FOR UPDATE SKIP LOCKED in Oracle and PostgreSQL 9.5, or SELECT … with (rowlock, updlock, readpast) in SQL Server.
public interface MyRepository extends CrudRepository<MyEntity, Long> {
/**
* The lock acquisition request skips the already locked rows.
* It uses a SELECT … FOR UPDATE SKIP LOCKED in Oracle and PostgreSQL 9.5,
* or SELECT … with (rowlock, updlock, readpast) in SQL Server.
*/
String UPGRADE_SKIPLOCKED = "-2";
@Lock(value = LockModeType.PESSIMISTIC_WRITE) // adds 'FOR UPDATE' statement
@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value = UPGRADE_SKIPLOCKED)})
MyEntity findFirstByStatus(String status);
}
通过这样做,选择查询将具有 select ... for update skip locked
By doing like this, the select query will have select ... for update skip locked
https://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/chapters/locking/Locking.html
这篇关于Spring 数据 JPA 本机查询跳过锁定的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!