Most database applications have to control concurrent access to data in order to prevent issues like data inconsistencies, dirty reads, and phantom reads. The two common ways of achieving data consistency and preventing concurrency issues are JPA Pessimistic Locking and Serializable Isolation Level. Both provide ways to control access to shared data, but they do it differently, and each has different performance and consistency trade-offs. This article explains the differences between these two approaches and when to use which of them.

What is JPA Pessimistic Locking?

In JPA (Java Persistence API), pessimistic locking is used to control access to specific rows in a database when multiple transactions might attempt to modify the same data concurrently. JPA pessimistic locks are scoped at the entity level (the specific rows associated with a JPA entity) and involve explicitly requesting locks within a transaction.

How JPA Pessimistic Locking Works

When using JPA's pessimistic locking with a query, the JPA will translate the locking instruction into database-specific SQL.

Here is how it works:

• Lock Modes: JPA provides three pessimistic lock modes:

  • PESSIMISTIC_READ: Allows reading data while preventing modifications by other transactions.
  • PESSIMISTIC_WRITE: Prevents both read and write by other transactions.
  • PESSIMISTIC_FORCE_INCREMENT: Forces an increment on a version column in the entity, effectively treating the entity as updated.

• SQL Translation: JPA translates these lock modes into SQL statements, such as SELECT... FOR UPDATE, that are compatible with the underlying database.

Example Using JPA Pessimistic Locking

Here is a sample usage of pessimistic locking at the JPA level in a repository method:

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT e FROM Entity e WHERE e.someCondition = :condition")
List<Entity> findWithPessimisticLock(@Param("condition") String condition);

In that case, PESSIMISTIC_WRITE will lock the rows returned by the query so that no other transactions can modify it before the transaction finishes.

Key Characteristics of JPA Pessimistic Locking

- Scope: It locks the rows that match the query criteria, so nobody else can modify those rows.
- Block: It would block any other transaction requesting a lock on the rows in conflict until this one commits or rolls back.
- Performance: Generally lighter on performance, as it only locks specific rows, but does not guarantee the prevention of phantom reads (new rows being added that match the condition after the query has been executed).

Serializable Isolation Level

Serializable isolation is the strictest isolation level available in databases, ensuring that transactions appear to execute sequentially, with no interference from each other. Serializable isolation prevents all concurrency issues, including dirty reads, non-repeatable reads, and phantom reads.

How Serializable Isolation Works

When a transaction is set to a Serializable isolation level, the database enforces strict ordering among transactions. This prevents other transactions from interacting with rows that might impact the results of the transaction, ensuring strict consistency.

There are two common implementations of Serializable isolation:

- Row-Level Locking: Databases like MySQL and Oracle use row locks, and sometimes additional range or gap locks, to control access to specific rows and ensure no new rows can interfere.
- Multi-Version Concurrency Control (MVCC): Databases like PostgreSQL use snapshots and track version conflicts to provide Serializable behavior without locking rows directly.

Example of Using Serializable Isolation

You can apply the Serializable isolation level at the transaction level in Spring, for example:

@Transactional(isolation = Isolation.SERIALIZABLE)
public List<Entity> findWithSerializableIsolation(String condition) {
    return entityManager.createQuery("SELECT e FROM Entity e WHERE e.someCondition = :condition", Entity.class)
                        .setParameter("condition", condition)
                        .getResultList();
}

In this example, the Isolation.SERIALIZABLE level enforces strict isolation, ensuring the query result is consistent with a fully isolated transaction.

Key Characteristics of Serializable Isolation

- Scope: Locks the rows involved and may apply additional range locks to prevent new rows that could impact the transaction’s result.
- Blocking: Other transactions trying to read or write rows impacted by the current transaction will typically be blocked until the transaction completes.
- Phantom Prevention: Prevents phantom reads by locking ranges, ensuring no other transactions can add rows that would affect the current transaction’s result.

Comparing JPA Pessimistic Locking and Serializable Isolation

Choosing the Right Approach

When deciding between JPA pessimistic locking and Serializable isolation, consider the specific requirements of your application:

Use JPA Pessimistic Locking when:

• You need to control concurrent access to specific rows without affecting broader queries.
• Performance is a priority, and you want minimal overhead from concurrency control.
• Phantom reads are not an issue for your application.

Use Serializable Isolation when:

• Strict consistency is required, with no room for concurrency anomalies (including phantom reads).
• You can tolerate higher performance costs due to the strict concurrency control.
• You are running a critical section of code where any data anomaly could be costly.

Both JPA pessimistic locking and Serializable isolation provide robust ways to manage concurrent access to data, but they serve different needs. Pessimistic locking in JPA gives fine-grained control over specific rows, allowing for better performance but with limited consistency guarantees. In contrast, Serializable isolation enforces strict isolation, treating transactions as if they run sequentially, which provides full consistency but may reduce concurrency.