oracle 如何在spring数据jpa查询中指定@lock超时？

How to specify @lock timeout in spring data jpa query?

提问by user2501759

How to specify @Locktimeout for query? I am using Oracle 11g, I hope I can use something like 'select id from table where id = ?1 for update wait 5'.

如何指定@Lock查询超时？我正在使用 Oracle 11g，我希望我可以使用类似'select id from table where id = ?1 for update wait 5'.

I defined method like this:

我定义了这样的方法：

@Lock(LockModeType.PESSIMISTIC_WRITE)
Stock findById(String id);

It seems to lock forever. When I set javax.persistence.lock.timeout=0in LocalContainerEntityManagerFactoryBean.jpaProperties, there is no effect.

它似乎永远锁定。当我设置javax.persistence.lock.timeout=0时LocalContainerEntityManagerFactoryBean.jpaProperties，没有任何效果。

回答by SkyWalker

To lock entities pessimistically, set the lock mode to PESSIMISTIC_READ, PESSIMISTIC_WRITE, or PESSIMISTIC_FORCE_INCREMENT.

If a pessimistic lock cannot be obtained, but the locking failure doesn't result in a transaction rollback, a LockTimeoutExceptionis thrown.

Pessimistic Locking Timeouts

The length of time in milliseconds the persistence provider should wait to obtain a lock on the database tables may be specified using the javax.persistence.lock.timeout property. If the time it takes to obtain a lock exceeds the value of this property, a LockTimeoutExceptionwill be thrown, but the current transaction will not be marked for rollback. If this property is set to 0, the persistence provider should throw a LockTimeoutExceptionif it cannot immediately obtain a lock.

If javax.persistence.lock.timeoutis set in multiple places, the value will be determined in the following order:

1. The argument to one of the EntityManageror Query methods.
2. The setting in the @NamedQueryannotation.
3. The argument to the Persistence.createEntityManagerFactorymethod.
4. The value in the persistence.xmldeployment descriptor.

要悲观锁定机构，设置锁定模式 PESSIMISTIC_READ，PESSIMISTIC_WRITE或 PESSIMISTIC_FORCE_INCREMENT。

如果无法获得悲观锁，但锁定失败不会导致事务回滚，LockTimeoutException则抛出 a。

悲观锁定超时

可以使用 javax.persistence.lock.timeout 属性指定持久性提供者为获取数据库表上的锁定而应等待的时间长度（以毫秒为单位）。如果获取锁所花费的时间超过该属性的值， LockTimeoutException则会抛出a，但不会将当前事务标记为回滚。如果此属性设置为 0，并且持久性提供者LockTimeoutException无法立即获得锁，则它应该抛出。

如果javax.persistence.lock.timeout在多处设置，则按以下顺序确定值：

1. EntityManager或之一的参数Query methods。
2. @NamedQuery注释中的设置。
3. Persistence.createEntityManagerFactory方法的参数。
4. persistence.xml部署描述符中的值。

For Spring Data 1.6 or greater

对于 Spring Data 1.6 或更高版本

@Lockis supported on CRUD methods as of version 1.6 of Spring Data JPA (in fact, there's already a milestoneavailable). See this ticketfor more details.

@Lock从 Spring Data JPA 的 1.6 版开始，CRUD 方法支持 CRUD 方法（实际上，已经有一个里程碑可用）。有关更多详细信息，请参阅此票证。

With that version you simply declare the following:

使用该版本，您只需声明以下内容：

interface WidgetRepository extends Repository<Widget, Long> {

  @Lock(LockModeType.PESSIMISTIC_WRITE)
  Widget findOne(Long id);
}

This will cause the CRUD implementation part of the backing repository proxy to apply the configured LockModeType to the find(…)call on the EntityManager.

这将导致后备资源库代理的CRUD实现部分配置的LockModeType适用于find(…)在电话会议上EntityManager。

On the other hand,

另一方面，

For previous version of Spring Data 1.6

对于之前版本的 Spring Data 1.6

The Spring Data pessimistic @Lockannotations only apply (as you pointed out) to queries. There are not annotations I know of which can affect an entire transaction. You can either create a findByOnePessimisticmethod which calls findByOnewith a pessimistic lock or you can change findByOneto always obtain a pessimistic lock.

Spring Data 悲观@Lock注释仅适用于（如您所指出的）查询。我知道没有注释会影响整个事务。您可以创建一个使用悲观锁findByOnePessimistic调用的方法，findByOne也可以更改findByOne为始终获得悲观锁。

If you wanted to implement your own solution you probably could. Under the hood the @Lockannotation is processed by LockModePopulatingMethodIntercceptorwhich does the following:

如果您想实施自己的解决方案，您可能可以。在幕后，@Lock注释被处理，LockModePopulatingMethodIntercceptor它执行以下操作：

TransactionSynchronizationManager.bindResource(method, lockMode == null ? NULL : lockMode);

You could create some static lock manager which had a ThreadLocal<LockMode>member variable and then have an aspect wrapped around every method in every repository which called bindResource with the lock mode set in the ThreadLocal. This would allow you to set the lock mode on a per-thread basis. You could then create your own @MethodLockModeannotation which would wrap the method in an aspect which sets the thread-specific lock mode before running the method and clears it after running the method.

您可以创建一些静态锁管理器，它有一个ThreadLocal<LockMode>成员变量，然后在每个存储库中的每个方法周围都有一个方面，这些方法调用 bindResource 并在 ThreadLocal 中设置锁定模式。这将允许您在每个线程的基础上设置锁定模式。然后，您可以创建自己的@MethodLockMode注释，该注释会将方法包装在一个方面，该方面在运行该方法之前设置线程特定的锁定模式，并在运行该方法后清除它。

Resource Link:

资源链接：

1. How to enable LockModeType.PESSIMISTIC_WRITE when looking up entities with Spring Data JPA?
2. How to add custom method to Spring Data JPA
3. Spring Data Pessimistic Lock timeout with Postgres
4. JPA Query API

1. 使用 Spring Data JPA 查找实体时如何启用 LockModeType.PESSIMISTIC_WRITE？
2. 如何将自定义方法添加到 Spring Data JPA
3. 使用 Postgres 的 Spring Data 悲观锁超时
4. JPA 查询 API

---

---

Various Example of Pessimistic Lock Timeout

悲观锁超时的各种示例

Setting a Pessimistic Lock

设置悲观锁

An entity object can be locked explicitly by the lock method:

可以通过 lock 方法显式锁定实体对象：

em.lock(employee, LockModeType.PESSIMISTIC_WRITE);

The first argument is an entity object. The second argument is the requested lock mode.

第一个参数是一个实体对象。第二个参数是请求的锁定模式。

A TransactionRequiredExceptionis thrown if there is no active transaction when lock is called because explicit locking requires an active transaction.

TransactionRequiredException如果在调用 lock 时没有活动事务，则会抛出A，因为显式锁定需要活动事务。

A LockTimeoutExceptionis thrown if the requested pessimistic lock cannot be granted:

LockTimeoutException如果无法授予所请求的悲观锁，则抛出A ：

• A PESSIMISTIC_READlock request fails if another user (which is represented by another EntityManager instance) currently holds a PESSIMISTIC_WRITElock on that database object.
• A PESSIMISTIC_WRITElock request fails if another user currently holds either a PESSIMISTIC_WRITElock or a PESSIMISTIC_READlock on that database object.

• 甲PESSIMISTIC_READ如果另一个用户（这是由另一EntityManager实例表示）当前持有的锁请求失败 PESSIMISTIC_WRITE该数据库对象上的锁。
• 甲PESSIMISTIC_WRITE如果另一用户当前持有任一个锁定请求失败PESSIMISTIC_WRITE锁或PESSIMISTIC_READ该数据库对象上的锁。

Setting Query Hint (Scopes)

设置查询提示（范围）

Query hints can be set in the following scopes (from global to local):

可以在以下范围内设置查询提示（从全局到本地）：

For the entire persistence unit - using a persistence.xmlproperty:

对于整个持久性单元 - 使用persistence.xml属性：

<properties>
   <property name="javax.persistence.query.timeout" value="3000"/>
</properties>

For an EntityManagerFactory - using the createEntityManagerFacotorymethod:

对于 EntityManagerFactory - 使用createEntityManagerFacotory方法：

Map<String,Object> properties = new HashMap();
properties.put("javax.persistence.query.timeout", 4000);
EntityManagerFactory emf =
  Persistence.createEntityManagerFactory("pu", properties);

For an EntityManager - using the createEntityManagermethod:

对于 EntityManager - 使用createEntityManager方法：

Map<String,Object> properties = new HashMap();
properties.put("javax.persistence.query.timeout", 5000);
EntityManager em = emf.createEntityManager(properties);

or using the setProperty method:

或使用 setProperty 方法：

em.setProperty("javax.persistence.query.timeout", 6000);

For a named querydefinition - using the hintselement:

对于named query定义 - 使用hints元素：

@NamedQuery(name="Country.findAll", query="SELECT c FROM Country c",
    hints={@QueryHint(name="javax.persistence.query.timeout", value="7000")})

For a specific query execution - using the setHintmethod (before query execution):

对于特定的查询执行 - 使用setHint方法（在查询执行之前）：

query.setHint("javax.persistence.query.timeout", 8000);

Resource Link:

资源链接：

1. Locking in JPA
2. Pessimistic Lock Timeout

1. 锁定 JPA
2. 悲观锁超时

回答by Dragan Bozanovic

You can use @QueryHintsin Spring Data:

您可以@QueryHints在 Spring Data 中使用：

@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="5000")})
Stock findById(String id)

回答by arpit garg

For Spring Data 1.6 or greater,we can use @Lock annotation provided by spring data jpa.

对于 Spring Data 1.6 或更高版本，我们可以使用 Spring Data jpa 提供的 @Lock 注解。

Also, Lock time out can be set as well by using @QueryHints. Originally there was no support for query hint annotations in default CRUD methods but its been available after fix 1.6M1. https://jira.spring.io/browse/DATAJPA-173

此外，还可以使用@QueryHints 设置锁定超时。最初在默认 CRUD 方法中不支持查询提示注释，但在修复 1.6M1 后可用。 https://jira.spring.io/browse/DATAJPA-173

Below is an example of a Pessimistic Lock with PESSIMISTIC_WRITE mode type which is an exclusive lock.

下面是一个带有 PESSIMISTIC_WRITE 模式类型的悲观锁的例子，它是一个排他锁。

@Lock(LockModeType.PESSIMISTIC_WRITE) 
@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value   ="5000")}) 
Customer findByCustomerId(Long customerId);

回答by goyalshub1509

javax.persistence.lock.timeout doesn't to be working for me either when provided like below

javax.persistence.lock.timeout 也不适用于我，如下所示

@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout",value = "15000")})

But then I tried something else which worked. Instead of using @Repository and using CrudRepository, now I am configuring my hbernate using entity manager. Used createQuery along with lock and setting lock timeout. And this configuration is working as expected. I have two transaction running in parellel and trying to lock exact same row in DB. First transaction is able to acquire WRITE lock and holds the lock for around 10 secs before releasing lock. Meanwhile, second transaction tries to acquire lock on same row but since javax.persistence.lock.timeout is set to 15 secs, it waits for lock to be released and then acquires its own lock. Hence making the flow serialized.

但后来我尝试了其他有效的方法。现在我使用实体管理器配置我的 hbernate，而不是使用 @Repository 和 CrudRepository。使用 createQuery 以及锁定和设置锁定超时。并且此配置按预期工作。我有两个并行运行的事务，并试图在数据库中锁定完全相同的行。第一个事务能够获取 WRITE 锁并在释放锁之前保持锁约 10 秒。同时，第二个事务尝试获取同一行的锁，但由于 javax.persistence.lock.timeout 设置为 15 秒，它等待锁被释放，然后获取自己的锁。因此使流序列化。

@Component
public class Repository {

    @PersistenceContext
    private EntityManager em;

    public Optional<Cache> getById(int id){
        List<Cache> list = em.createQuery("select c from Cache c where c.id = ?1")
                            .setParameter(1, id)
                            .setHint("javax.persistence.lock.timeout", 15000)
                            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                            .getResultList();


        return Optional.ofNullable(list.get(0));
    }

    public void save(Cache cache) {
        cache = em.find(Cache.class, cache.getId());
        em.merge(cache);
    }
}