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:

Readers–writer lock

A database system is a highly concurrent environment, therefore many concurrency theory idioms apply to database access as well. Concurrent changes must be serialized to preserve data integrity, so most database systems use a two-phase locking strategy, even if it’s usually supplemented by a Multiversion concurrency control mechanism.

Because a mutual exclusion locking would hinder scalability (treating reads and writes equally), most database systems use a readers-writer locking synchronization scheme, so that:

  • 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, 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.

PostgreSQL row-level lock modes

For the next test cases, we are going to use PostgreSQL for it supports both exclusive and share explicit locking.

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.

private void testPessimisticLocking(ProductLockRequestCallable primaryLockRequestCallable, ProductLockRequestCallable secondaryLockRequestCallable) {
	doInTransaction(session -> {
		try {
			Product product = (Product) session.get(Product.class, 1L);
			primaryLockRequestCallable.lock(session, product);
			executeAsync(
				() -> {
					doInTransaction(_session -> {
						Product _product = (Product) _session.get(Product.class, 1L);
						secondaryLockRequestCallable.lock(_session, _product);
					});
				},
				endLatch::countDown
			);
			sleep(WAIT_MILLIS);
		} catch (StaleObjectStateException e) {
			LOGGER.info("Optimistic locking failure: ", e);
		}
	});
	awaitOnLatch(endLatch);
}

Case 1: PESSIMISTIC_READ doesn’t block PESSIMISTIC_READ lock requests

The first test will check how two concurrent PESSIMISTIC_READ lock requests interact:

@Test
public void testPessimisticReadDoesNotBlockPessimisticRead() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_READ doesn't block PESSIMISTIC_READ");
	testPessimisticLocking(
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		},
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		}
	);
}

Running this test, we get the following output:

[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 a concurrent modification. Alice will acquire a shared lock and Bob will attempt to modify the locked entity:

@Test
public void testPessimisticReadBlocksUpdate() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_READ blocks UPDATE");
	testPessimisticLocking(
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		},
		(session, product) -> {
			product.setDescription("USB Flash Memory Stick");
			session.flush();
			LOGGER.info("Implicit lock acquired");
		}
	);
}

The test generates this output:

[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 transaction is committed (that’s why the UPDATE took 427ms to run).

Case 3: PESSIMISTIC_READ blocks PESSIMISTIC_WRITE lock requests

The same behaviour is exhibited by a secondary PESSIMISTIC_WRITE lock request:

@Test
public void testPessimisticReadBlocksPessimisticWrite() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_READ blocks PESSIMISTIC_WRITE");
	testPessimisticLocking(
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		},
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).lock(product);
			LOGGER.info("PESSIMISTIC_WRITE acquired");
		}
	);
}

Giving the following output:

[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.

The PostgreSQL NO WAIT directive is described as follows:

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.

@Test
public void testPessimisticReadWithPessimisticWriteNoWait() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_READ blocks PESSIMISTIC_WRITE, NO WAIT fails fast");
	testPessimisticLocking(
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		},
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).setTimeOut(Session.LockRequest.PESSIMISTIC_NO_WAIT).lock(product);
			LOGGER.info("PESSIMISTIC_WRITE acquired");
		}
	);
}

This test generates the following output:

[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 blocks a shared lock acquire attempt:

@Test
public void testPessimisticWriteBlocksPessimisticRead() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_WRITE blocks PESSIMISTIC_READ");
	testPessimisticLocking(
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).lock(product);
			LOGGER.info("PESSIMISTIC_WRITE acquired");
		},
		(session, product) -> {
			session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
			LOGGER.info("PESSIMISTIC_READ acquired");
		}
	);
}

Generating the following output:

[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:

@Test
public void testPessimisticWriteBlocksPessimisticWrite() throws InterruptedException {
	LOGGER.info("Test PESSIMISTIC_WRITE blocks PESSIMISTIC_WRITE");
	testPessimisticLocking(
			(session, product) -> {
				session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).lock(product);
				LOGGER.info("PESSIMISTIC_WRITE acquired");
			},
			(session, product) -> {
				session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).lock(product);
				LOGGER.info("PESSIMISTIC_WRITE acquired");
			}
	);
}

The test generates this output:

[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.

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

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.

Code available on GitHub.

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

13 thoughts on “How do LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE work in JPA and Hibernate

  1. Really small correction- in case 5, in the example the following appears:
    (session, product) -> {
    session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_WRITE)).lock(product);
    LOGGER.info(“PESSIMISTIC_WRITE acquired”);
    },
    (session, product) -> {
    session.buildLockRequest(new LockOptions(LockMode.PESSIMISTIC_READ)).lock(product);
    LOGGER.info(“PESSIMISTIC_WRITE acquired”);
    }
    The last LOGGER.info is PESSIMISTIC_WRITE by mistake and should be PESSIMISTIC_READ

  2. Hi Vlad,

    Hibernate LockOptions class contains a constant SKIP_LOCKED, which used in place of timeout should skip rows that are locked by other transactions (at least this is what javadoc claims).

    I tried it with MSSQL and it seems not to generate anything at all (although MSSQL has a hint READPAST which I would say can be used exactly for that behavior – https://msdn.microsoft.com/en-us/library/ms187373.aspx).

    I wonder what (and if anything) is generated for SKIP_LOCKED for PostgreSQL.

    Best Regards,
    Piotr

    1. SKIP_LOCKED means to attempt to acquire locks on a result set and skip the ones that are locked by other transactions. READPAST does not acquire any lock at all so it’s not the same as SKIP_LOCKED. READPAST makes sense only in SQL Server because they still use 2PL instead of MVCC by default too.

      1. Right – READPAST by itself will not lock, but following will:

        select top 1 * from my_table with (rowlock, updlock, readpast);

        This will return a first not locked row and will acquire a lock on it at the same time.
        So this is exactly SKIP_LOCKED semantics as far as I understand it.

        (Note: I tried this on MSSQL with MVCC).

  3. As far as I checked it in practice it works and documents seem to indicate that too, so sure I will add a feature request as you suggest.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s