How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements

Introduction

Relational database systems employ various Concurrency Control mechanisms to provide transactions with ACID property guarantees. While isolation levels are one way of choosing a given Concurrency Control mechanism, you can also use explicit locking whenever you want a finer-grained control to prevent data integrity issues.

As previously explained, there are two types of explicit locking mechanisms: pessimistic (physical) and optimistic (logical). In this post, I’m going to explain how explicit pessimistic locking interacts with non-query DML statements (e.g. insert, update, and delete).

Continue reading “How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements”

Advertisements

How does CascadeType.LOCK works in JPA and Hibernate

Introduction

Having introduced Hibernate explicit locking support, as well as Cascade Types, it’s time to analyze the CascadeType.LOCK behavior.

A Hibernate lock request triggers an internal LockEvent. The associated DefaultLockEventListener may cascade the lock request to the locking entity children.

Since CascadeType.ALL includes CascadeType.LOCK too, it’s worth understanding when a lock request propagates from a Parent to a Child entity.

Continue reading “How does CascadeType.LOCK works in JPA and Hibernate”

How do LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE work in JPA and Hibernate

Introduction

Java Persistence API comes with a thorough concurrency control mechanism, supporting both implicit and explicit locking. The implicit locking mechanism is straightforward and it relies on:

  • Optimistic locking: Entity state changes can trigger a version incrementation
  • Row-level locking: Based on the current running transaction isolation level, the INSERT/UPDATE/DELETE statements may acquire exclusive row locks

While implicit locking is suitable for many scenarios, an explicit locking mechanism can leverage a finer-grained concurrency control.

In my previous posts, I covered the explicit optimistic lock modes:

In this post, I am going to unravel the explicit pessimistic lock modes:

Continue reading “How do LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE work in JPA and Hibernate”

How does LockModeType.PESSIMISTIC_FORCE_INCREMENT work in JPA and Hibernate

Introduction

In my previous post, I introduced the OPTIMISTIC_FORCE_INCREMENT Lock Mode and we applied it for propagating a child entity version change to a locked parent entity. In this post, I am going to reveal the PESSIMISTIC_FORCE_INCREMENT Lock Mode and compare it with its optimistic counterpart.

Continue reading “How does LockModeType.PESSIMISTIC_FORCE_INCREMENT work in JPA and Hibernate”

How does LockModeType.OPTIMISTIC_FORCE_INCREMENT work in JPA and Hibernate

Introduction

In my previous post, I explained how OPTIMISTIC Lock Mode works and how it can help us synchronize external entity state changes. In this post, we are going to unravel the OPTIMISTIC_FORCE_INCREMENT Lock Mode usage patterns.

With LockModeType.OPTIMISTIC, the locked entity version is checked towards the end of the current running transaction, to make sure we don’t use a stale entity state. Because of the application-level validation nature, this strategy is susceptible to race-conditions, therefore requiring an additional pessimistic lock .

The LockModeType.OPTIMISTIC_FORCE_INCREMENT not only it checks the expected locked entity version, but it also increments it. Both the check and the update happen in the same UPDATE statement, therefore making use of the current database transaction isolation level and the associated physical locking guarantees.

It is worth noting that the locked entity version is bumped up even if the entity state hasn’t been changed by the current running transaction.

Continue reading “How does LockModeType.OPTIMISTIC_FORCE_INCREMENT work in JPA and Hibernate”