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

Question

How to specify @Lock timeout 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'.

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=0 in LocalContainerEntityManagerFactoryBean.jpaProperties, there is no effect.

Answer 1

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 LockTimeoutException is 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 LockTimeoutException will 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 LockTimeoutException if it cannot immediately obtain a lock.

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

1. The argument to one of the EntityManager or Query methods.
2. The setting in the @NamedQuery annotation.
3. The argument to the Persistence.createEntityManagerFactory method.
4. The value in the persistence.xml deployment descriptor.

For Spring Data 1.6 or greater

@Lock is supported on CRUD methods as of version 1.6 of Spring Data JPA (in fact, there's already a milestone available). See this ticket for more details.

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.

On the other hand,

For previous version of Spring Data 1.6

The Spring Data pessimistic @Lock annotations 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 findByOnePessimistic method which calls findByOne with a pessimistic lock or you can change findByOne to always obtain a pessimistic lock.

If you wanted to implement your own solution you probably could. Under the hood the @Lock annotation is processed by LockModePopulatingMethodIntercceptor which does the following:

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 @MethodLockMode annotation 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.

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

---

Various Example of Pessimistic Lock Timeout

Setting a Pessimistic Lock

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

em.lock(employee, LockModeType.PESSIMISTIC_WRITE);

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

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

A LockTimeoutException is thrown if the requested pessimistic lock cannot be granted:

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

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.xml property:

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

For an EntityManagerFactory - using the createEntityManagerFacotory method:

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

For an EntityManager - using the createEntityManager method:

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

or using the setProperty method:

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

For a named query definition - using the hints element:

@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 setHint method (before query execution):

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

Resource Link:

1. Locking in JPA
2. Pessimistic Lock Timeout