Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
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:
A shared (read) lock blocks writers, allowing multiple readers to proceed
An exclusive (write) lock blocks both readers and writers, making all write operations be applied sequentially
Because the locking syntax is not part of the SQL Standard, each RDBMS has opted for a different syntax:
Database name
Shared lock statement
Exclusive lock statement
Oracle
FOR UPDATE
FOR UPDATE
MySQL
LOCK IN SHARE MODE
FOR UPDATE
Microsoft SQL Server
WITH (HOLDLOCK, ROWLOCK)
WITH (UPDLOCK, HOLDLOCK, ROWLOCK)
PostgreSQL
FOR SHARE
FOR UPDATE
DB2
FOR READ ONLY WITH RS
FOR UPDATE WITH RS
Java Persistence abstraction layer hides the database specific locking semantics, offering a common API that only requires two Lock Modes. The shared/read lock is acquired using the PESSIMISTIC_READ Lock Mode Type, and the exclusive/write lock is requested using PESSIMISTIC_WRITE instead.
All the following tests will use the same concurrency utility, emulating two users: Alice and Bob. Each test scenario will verify a specific read/write locking combination.
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_READ doesn't block PESSIMISTIC_READ
#Alice selects the Product entity
[Alice]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires a SHARED lock on the Product entity
[Alice]: Time:1 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_READ acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Bob acquires a SHARED lock on the Product entity
[Bob]: Time:1 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Bob]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_READ acquired
#Bob's transactions is committed
[Bob]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
In this scenario, there is no contention whatsoever. Both Alice and Bob can acquire a shared lock without running into any conflict.
Case 2: PESSIMISTIC_READ blocks UPDATE implicit lock requests
The second scenario will demonstrate how the shared lock prevents concurrent modifications. Alice will acquire a shared lock and Bob will attempt to modify the locked entity:
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_READ blocks UPDATE
#Alice selects the Product entity
[Alice]: Time:0 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires a SHARED lock on the Product entity
[Alice]: Time:0 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_READ acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
#Bob can acquire the Product entity lock, only after Alice's transaction is committed
[Bob]: Time:427 Query:{[
UPDATE product
SET description = ?,
price = ?,
version = ?
WHERE id = ?
AND version = ?
][USB Flash Memory Stick,12.99,1,1,0]}
[Bob]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Implicit lock acquired
#Bob's transactions is committed
[Bob]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
While Bob could select the Product entity, the UPDATE is delayed up until Alice’s transaction is committed (that’s why the UPDATE took 427ms to run).
Case 3: PESSIMISTIC_READ blocks PESSIMISTIC_WRITE lock requests
The same behavior is exhibited by a secondary PESSIMISTIC_WRITE lock request:
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_READ blocks PESSIMISTIC_WRITE
#Alice selects the Product entity
[Alice]: Time:0 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires a SHARED lock on the Product entity
[Alice]: Time:1 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_READ acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
#Bob can acquire the Product entity lock, only after Alice's transaction is committed
[Bob]: Time:428 Query:{[
SELECT id
FROM product
WHERE id = ?
AND version = ?
FOR UPDATE
][1,0]}
[Bob]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_WRITE acquired
#Bob's transactions is committed
[Bob]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
Bob’s exclusive lock request waits for Alice’s shared lock to be released.
Case 4: PESSIMISTIC_READ blocks PESSIMISTIC_WRITE lock requests, NO WAIT fails fast
Hibernate provides a PESSIMISTIC_NO_WAIT timeout directive, which translates to a database specific NO_WAIT lock acquire policy.
To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. Note that NOWAIT applies only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_READ blocks PESSIMISTIC_WRITE, NO WAIT fails fast
#Alice selects the Product entity
[Alice]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires a SHARED lock on the Product entity
[Alice]: Time:1 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_READ acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Bob tries to acquire an EXCLUSIVE lock on the Product entity and fails because of the NO WAIT policy
[Bob]: Time:0 Query:{[
SELECT id
FROM product
WHERE id = ?
AND version = ?
FOR UPDATE nowait
][1,0]}
[Bob]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 55P03
[Bob]: o.h.e.j.s.SqlExceptionHelper - ERROR: could not obtain lock on row in relation "product"
#Bob's transactions is rolled back
[Bob]: o.h.e.t.i.j.JdbcTransaction - rolled JDBC Connection
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
Since Alice already holds a shared lock on the Product entity associated database row, Bob’s exclusive lock request fails immediately.
Case 5: PESSIMISTIC_WRITE blocks PESSIMISTIC_READ lock requests
The next test proves that an exclusive lock will always block a shared lock acquire attempt:
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_WRITE blocks PESSIMISTIC_READ
#Alice selects the Product entity
[Alice]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires an EXCLUSIVE lock on the Product entity
[Alice]: Time:0 Query:{[
SELECT id
FROM product
WHERE id = ?
AND version = ?
FOR UPDATE
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_WRITE acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
#Bob can acquire the Product entity SHARED lock, only after Alice's transaction is committed
[Bob]: Time:428 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR share
][1,0]}
[Bob]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_WRITE acquired
#Bob's transactions is committed
[Bob]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
Bob’s shared lock request waits for Alice’s transaction to end so that all acquired locks are released.
Case 6: PESSIMISTIC_WRITE blocks PESSIMISTIC_WRITE lock requests
An exclusive lock blocks an exclusive lock as well:
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Test PESSIMISTIC_WRITE blocks PESSIMISTIC_WRITE
#Alice selects the Product entity
[Alice]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice acquires an EXCLUSIVE lock on the Product entity
[Alice]: Time:0 Query:{[
SELECT id
FROM product
WHERE id = ?
AND version = ?
FOR UPDATE
][1,0]}
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_WRITE acquired
#Alice waits for 500ms
[Alice]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - Wait 500 ms!
#Bob selects the Product entity
[Bob]: Time:1 Query:{[
SELECT lockmodepe0_.id AS id1_0_0_,
lockmodepe0_.description AS descript2_0_0_,
lockmodepe0_.price AS price3_0_0_,
lockmodepe0_.version AS version4_0_0_
FROM product lockmodepe0_
WHERE lockmodepe0_.id = ?
][1]}
#Alice's transactions is committed
[Alice]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
#Bob can acquire the Product entity SHARED lock, only after Alice's transaction is committed
[Bob]: Time:428 Query:{[
SELECT id
FROM product
WHERE id =?
AND version =? FOR update
][1,0]}
[Bob]: c.v.h.m.l.c.LockModePessimisticReadWriteIntegrationTest - PESSIMISTIC_WRITE acquired
#Bob's transactions is committed
[Bob]: o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection
Bob’s exclusive lock request has to wait for Alice to release its lock.
Online Workshops
If you enjoyed this article, I bet you are going to love my upcoming Online Workshops.
Relational database systems use locks for preserving ACID guarantees, so it’s important to understand how shared and exclusive row-level locks inter-operate. An explicit pessimistic lock is a very powerful database concurrency control mechanism and you might even use it for fixing an optimistic locking race condition.
2 Comments on “How do LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE work in JPA and Hibernate”
Great explanation and absolutely beautiful Testcases. After getting my brain around the concurrency/transaction topic those were exactly the testcases I wanted to write to see if I got everything right. But I couldn’t have done it better. Thx alot – no open questions left!
Great explanation and absolutely beautiful Testcases. After getting my brain around the concurrency/transaction topic those were exactly the testcases I wanted to write to see if I got everything right. But I couldn’t have done it better. Thx alot – no open questions left!
You’re welcome