Bulk update optimistic locking with JPA and Hibernate

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.

Bulk update optimistic locking with JPA and Hibernate.https://t.co/OsBnhkiHQj pic.twitter.com/BFqe4uL7LS

— Java (@java) December 2, 2019

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.

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.

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.

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();

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.