CriteriaQuery and Pessimistic Locking

Oct 13, 2025 by talynt.org 45 views

Introduction

In this article, we will discuss the issue of Hibernate ignoring the UPGRADE_SKIPLOCKED hint when used with PESSIMISTIC_WRITE in a JpaRepository. We will explore the problem, its causes, and possible solutions.

Problem Statement

When using PESSIMISTIC_WRITE in a JpaRepository, Hibernate ignores the UPGRADE_SKIPLOCKED hint. This can lead to unexpected behavior and errors in the application.

Causes of the Issue

The issue is caused by the way Hibernate handles query hints in JpaRepository. When using @QueryHint in a JpaRepository, Hibernate does not propagate the hint to the underlying CriteriaQuery. This is because JpaRepository uses a different query execution mechanism than CriteriaQuery.

CriteriaQuery and Pessimistic Locking

CriteriaQuery is a powerful tool for building queries in Hibernate. It allows for complex query construction and provides a high degree of flexibility. Pessimistic locking is a feature of CriteriaQuery that allows for locking rows in the database to prevent other transactions from modifying them.

JpaRepository and Pessimistic Locking

JpaRepository is a simplified interface for building queries in Hibernate. It provides a more straightforward way of building queries than CriteriaQuery. However, JpaRepository does not support pessimistic locking out of the box.

UPGRADE_SKIPLOCKED Hint

The UPGRADE_SKIPLOCKED hint is used to skip locked rows in a query. It is particularly useful when dealing with pessimistic locking.

Ignoring UPGRADE_SKIPLOCKED Hint in JpaRepository

When using PESSIMISTIC_WRITE in a JpaRepository, Hibernate ignores the UPGRADE_SKIPLOCKED hint. This is because JpaRepository does not propagate the hint to the underlying CriteriaQuery.

Solution 1: Use CriteriaQuery

One possible solution to this issue is to use CriteriaQuery instead of JpaRepository. CriteriaQuery provides a more flexible way of building queries and supports pessimistic locking.

Example Code

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MyEntity> query = cb.createQuery(MyEntity.class);
Root<MyEntity> root = query.from(MyEntity.class);
query.where(cb.equal(root.get("id"), 1));
query.setHint("org.hibernate.query.criteria.Ordering", "UPGRADE_SKIPLOCKED");
query.setHint("javax.persistence.lock.timeout", 10000);
query.setHint("javax.persistence.lock.mode", "PESSIMISTIC_WRITE");
query.setHint("javax.persistence.query.hints", "UPGRADE_SKIPLOCKED");
query.select(root);

Solution 2: Use @QueryHint in CriteriaQuery

Another possible solution is to use @QueryHint in CriteriaQuery. This will allow you to specify the UPGRADE_SKIPLOCKED hint directly in the query.

Example Code

@QueryHints(value = @QueryHint(name = "org.hibernate.query.criteria.Ordering", value = "UPGRADE_SKIPLOCKED"))
public List<MyEntity> findMyEntities() {
    return em.createQuery("SELECT e FROM MyEntity e", MyEntity.class).getResultList();
}

Conclusion

Best Practices

When dealing with pessimistic locking and query hints, it is essential to follow best practices to avoid unexpected behavior and errors.

Use CriteriaQuery instead of JpaRepository when possible.
Specify query hints directly in the query using @QueryHint.
Use the UPGRADE_SKIPLOCKED hint to skip locked rows in a query.
Set the javax.persistence.lock.timeout hint to specify the lock timeout.
Set the javax.persistence.lock.mode hint to specify the lock mode.

Q&A

Q: What is the purpose of the UPGRADE_SKIPLOCKED hint in Hibernate? A: The UPGRADE_SKIPLOCKED hint is used to skip locked rows in a query. It is particularly useful when dealing with pessimistic locking.

Q: Why is the UPGRADE_SKIPLOCKED hint ignored when used with PESSIMISTIC_WRITE in a JpaRepository? A: The UPGRADE_SKIPLOCKED hint is ignored when used with PESSIMISTIC_WRITE in a JpaRepository because Hibernate does not propagate the hint to the underlying CriteriaQuery.

Q: What are the possible solutions to this issue? A: Two possible solutions to this issue are to use CriteriaQuery instead of JpaRepository or to use @QueryHint in CriteriaQuery.

Q: How can I use CriteriaQuery instead of JpaRepository? A: You can use CriteriaQuery instead of JpaRepository by creating a CriteriaBuilder and a CriteriaQuery, and then specifying the query hints directly in the query.

Q: How can I use @QueryHint in CriteriaQuery? A: You can use @QueryHint in CriteriaQuery by annotating the query method with @QueryHints and specifying the query hints directly in the query.

Q: What are the benefits of using CriteriaQuery instead of JpaRepository? A: The benefits of using CriteriaQuery instead of JpaRepository include:

More flexibility in building queries
Support for pessimistic locking
Ability to specify query hints directly in the query

Q: What are the benefits of using @QueryHint in CriteriaQuery? A: The benefits of using @QueryHint in CriteriaQuery include:

Ability to specify query hints directly in the query
Simplified query construction
Improved performance

Q: How can I specify the UPGRADE_SKIPLOCKED hint using @QueryHint? A: You can specify the UPGRADE_SKIPLOCKED hint using @QueryHint by annotating the query method with @QueryHints and specifying the UPGRADE_SKIPLOCKED hint directly in the query.

Q: What is the syntax for specifying the UPGRADE_SKIPLOCKED hint using @QueryHint? A: The syntax for specifying the UPGRADE_SKIPLOCKED hint using @QueryHint is as follows:

@QueryHints(value = @QueryHint(name = "org.hibernate.query.criteria.Ordering", value = "UPGRADE_SKIPLOCKED"))
public List<MyEntity> findMyEntities() {
    return em.createQuery("SELECT e FROM MyEntity e", MyEntity.class).getResultList();
}

Q: What are the possible values for the javax.persistence.lock.timeout hint? A: The possible values for the javax.persistence.lock.timeout hint are:

A positive integer value representing the lock timeout in milliseconds
A string value representing the lock timeout in a format such as "10s" or "1m"

Q: What are the possible values for the javax.persistence.lock.mode hint? A: The possible values for the javax.persistence.lock.mode hint are:

"PESSIMISTIC_READ"
"PESSIMISTIC_WRITE"
"OPTIMISTIC"

Q: How can I specify the javax.persistence.lock.timeout hint using @QueryHint? A: You can specify the javax.persistence.lock.timeout hint using @QueryHint by annotating the query method with @QueryHints and specifying the javax.persistence.lock.timeout hint directly in the query.

Q: What is the syntax for specifying the javax.persistence.lock.timeout hint using @QueryHint? A: The syntax for specifying the javax.persistence.lock.timeout hint using @QueryHint is as follows:

@QueryHints(value = @QueryHint(name = "javax.persistence.lock.timeout", value = "10000"))
public List<MyEntity> findMyEntities() {
    return em.createQuery("SELECT e FROM MyEntity e", MyEntity.class).getResultList();
}

Q: How can I specify the javax.persistence.lock.mode hint using @QueryHint? A: You can specify the javax.persistence.lock.mode hint using @QueryHint by annotating the query method with @QueryHints and specifying the javax.persistence.lock.mode hint directly in the query.

Q: What is the syntax for specifying the javax.persistence.lock.mode hint using @QueryHint? A: The syntax for specifying the javax.persistence.lock.mode hint using @QueryHint is as follows:

@QueryHints(value = @QueryHint(name = "javax.persistence.lock.mode", value = "PESSIMISTIC_WRITE"))
public List<MyEntity> findMyEntities() {
    return em.createQuery("SELECT e FROM MyEntity e", MyEntity.class).getResultList();
}

Conclusion

In conclusion, the UPGRADE_SKIPLOCKED hint is ignored when used with PESSIMISTIC_WRITE in a JpaRepository. This is due to the way Hibernate handles query hints in JpaRepository. Two possible solutions to this issue are to use CriteriaQuery instead of JpaRepository or to use @QueryHint in CriteriaQuery. By following the best practices outlined in this article, you can ensure that your application behaves correctly and efficiently when dealing with pessimistic locking and query hints.