Bulk update optimistic locking with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how we can adjust a bulk update statement so that it takes optimistic locking into consideration.

While Hibernate has been supporting versioned HQL queries for a very long time, it’s actually very easy to achieve this goal even with standard JPQL or JPA Criteria API.

Domain Model

Let’s consider we have a Post entity that has a status attribute that can take three possible values: PENDING, APPROVED, and SPAM. For this reason, the status attribute is mapped by the PostStatus Enum type.

Post entity with version and status attributes

For more details about the best way to map an Enum type when using JPA and Hibernate, check out this article.

The Post entity is mapped as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @Enumerated(EnumType.ORDINAL)
    @Column(columnDefinition = "tinyint")
    private PostStatus status = PostStatus.PENDING;

    @Version
    private short version;

    public Long getId() {
        return id;
    }

    public Post setId(Long id) {
        this.id = id;
        return this;
    }

    public String getTitle() {
        return title;
    }

    public Post setTitle(String title) {
        this.title = title;
        return this;
    }

    public PostStatus getStatus() {
        return status;
    }

    public void setStatus(PostStatus status) {
        this.status = status;
    }

    public short getVersion() {
        return version;
    }

    public Post setVersion(short version) {
        this.version = version;
        return this;
    }
}

If you wonder why the version property is mapped to a short Java primitive type, instead of int or long, then this article will explain to you why short is most often a much better alternative.

Now, let’s assume we have a number of Post entities that must be marked as spam`:

for (long i = 1; i <= SPAM_POST_COUNT; i++) {
    entityManager.persist(
        new Post()
            .setId(i)
            .setTitle(String.format("Spam post %d", i))
    );
}

Notice that the Java setter methods return the current Post Object reference, therefore allowing us to build the Post entity instance the Fluent-style API. For more detail about this topic, check out this article.

Bulk update

Now, using plain JPQL, a bulk update statement would look as follows:

int updateCount = entityManager
.createQuery(
    "update Post " +
    "set status = :newStatus " +
    "where " +
    "   status = :oldStatus and " +
    "   lower(title) like :pattern")
.setParameter("oldStatus", PostStatus.PENDING)
.setParameter("newStatus", PostStatus.SPAM)
.setParameter("pattern", "%spam%")
.executeUpdate();

assertEquals(SPAM_POST_COUNT, updateCount);

When executing the JPQL statement above, Hibernate generates the following SQL statement:

UPDATE post
SET 
  status = 2
WHERE 
  status = 0 AND  
  lower(title) LIKE '%spam%'

However, the version column is simply ignored, and, if there are pending transactions that have already loaded a Post entity, they are going to lose this update if we don’t increment the version property, as illustrated in the diagram below.

Bulk Update Lost Update

In the diagram above, Alice selects a Post entity. Afterward, Bob runs a bulk update changing the state of all post records containing the word spam. If Alice’s UPDATE that comes afterward is allowed to run, then she will never acknowledge Bob’s change. This is how the Lost Update anomaly occurs.

If you want to learn more about the lost update anomaly and what are the best ways to prevent it, then check out this article.

Bulk update with optimistic locking

Now, avoiding the Lost Update is actually very easy with JPQL, as all we need to do is increment the version entity attribute:

int updateCount = entityManager
.createQuery(
    "update Post " +
    "set " +
    "   status = :newStatus," +
    "   version = version + 1 " +
    "where " +
    "   status = :oldStatus and " +
    "   lower(title) like :pattern")
.setParameter("oldStatus", PostStatus.PENDING)
.setParameter("newStatus", PostStatus.SPAM)
.setParameter("pattern", "%spam%")
.executeUpdate();

assertEquals(SPAM_POST_COUNT, updateCount);

Now, when executing the JPQL query above, Hibernate generates the following SQL UPDATE statement:

UPDATE post
SET 
  status = 2,
  version = version + 1
WHERE 
  status = 0 AND  
  lower(title) LIKE '%spam%'

By incrementing the version column, Alice’s UPDATE will not succeed as the WHERE clause will not match the post record, and an OptimisticLockException will be thrown this time.

Bulk Update Optimistic Locking

Hibernate versioned bulk update

Hibernate supports a simplified version of the previous JPQL bulk update statement that incremented the version column. Instead of incrementing the version column, you just have to provide the versioned keyword after the update one.

int updateCount = entityManager
.createQuery(
    "update versioned Post " +
    "set status = :newStatus " +
    "where " +
    "   status = :oldStatus and " +
    "   lower(title) like :pattern")
.setParameter("oldStatus", PostStatus.PENDING)
.setParameter("newStatus", PostStatus.SPAM)
.setParameter("pattern", "%spam%")
.executeUpdate();

assertEquals(SPAM_POST_COUNT, updateCount);

Criteria API bulk update optimistic locking

Updating the version column in a bulk update statement is not limited to JPQL or HQL. If you want to build the bulk update statement dynamically, then Criteria API is a much better alternative than concatenating query string fragments, which can lead to SQL Injection attacks.

The previous JPQL bulk update statement that was incrementing the version attribute can be translated to Criteria API as follows:

CriteriaBuilder builder = entityManager
.getCriteriaBuilder();

CriteriaUpdate<Post> update = builder
.createCriteriaUpdate(Post.class);

Root<Post> root = update.from(Post.class);

Expression<Boolean> wherePredicate = builder
.and(
    builder.equal(
        root.get("status"), 
        PostStatus.PENDING
    ),
    builder.like(
        builder.lower(root.get("title")), 
        "%spam%"
    )
);

Path<Short> versionPath = root.get("version");
Expression<Short> incrementVersion = builder
.sum((short) 1, versionPath);

update
.set(root.get("status"), PostStatus.SPAM)
.set(versionPath, incrementVersion)
.where(wherePredicate);

int updateCount = entityManager
.createQuery(update)
.executeUpdate();

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

If you’re using an optimistic locking strategy to prevent the Lost Update anomalies, then Hibernate can automatically increment the version column whenever the entity is updated. However, for bulk update statements, this is not the case, and you have to adjust the bulk update statements so that the version column is incremented accordingly.

FREE EBOOK

6 Comments on “Bulk update optimistic locking with JPA and Hibernate

  1. Yeah, it makes sense and I agree with you… But it could imply a previous read either, I mean, if, for some reason, the developer decides to update the entity state via bulk UPDATE instead of the EntityManager, likely for the matter of performance or as a workaround for some database trigger logics.

    what do you think?

    • In that case, it can use batch updates. The problem is that not all rows matched by the bulk update are expected to have the same version number. Batch updates address this issue better than bulk updates.

      • sure, you’re right! I was just thinking about a simple bulk UPDATE to change a single entity row, hehe 😉

        thanks again, Vlad!

      • Bulk implies more than just one entity, hence the reasoning for not including the version info in the filtering criteria.

  2. Vlad, very good article!

    Don’t you think the bulk UPDATE should (or could) verify the version number on WHERE clause in the same Hibernate does? So that if multiple users trigger the same bulk UPDATE they might get “no rows updated” result then avoiding the lost update scenarios.

    thanks,

    • The bulk update does not imply a previous read, hence it cannot use the version in the WHERE clause. That’s needed only for read-modify-write data access patterns.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.