JPA - PessimisticLockException

[Updated: Dec 22, 2017, Created: Dec 22, 2017]

PessimisticLockException is thrown if pessimistic lock cannot be obtained or if the time it takes to obtain a lock exceeds the value of javax.persistence.lock.timeout property.

If javax.persistence.lock.timeout (milliseconds) property is not set by the application, a default value (depending on the provider and underlying database) is used.

Example

The Entity

@Entity
public class Article {
  @Id
  @GeneratedValue
  private long id;
  private String content;
    .............
}

Main class throwing PessimisticLockException

In following example, each of the two threads (acting as two users) obtain pessimistic read and write locks. The write transactions delays for a long time and PessimisticLockException is thrown in the read transaction:

public class PessimisticLockExceptionExample {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) throws Exception {
      ExecutorService es = Executors.newFixedThreadPool(3);
      try {
          persistArticle();
          es.execute(() -> {
              updateArticle();
          });
          es.execute(() -> {
              //simulating other user by using different thread
              readArticle();
          });
          es.shutdown();
          es.awaitTermination(3, TimeUnit.MINUTES);
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void updateArticle() {
      log("updating Article entity");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      Article article = em.find(Article.class, 1L, LockModeType.PESSIMISTIC_WRITE);
      try {//sleep for long enough to block read thread lock for long time
          TimeUnit.MINUTES.sleep(1);
      } catch (InterruptedException e) {
          e.printStackTrace();
      }
      article.setContent("updated content .. ");
      log("committing in write thread.");
      em.getTransaction().commit();
      em.close();
      log("Article updated", article);
  }

  private static void readArticle() {
      try {//some delay before reading
          TimeUnit.MILLISECONDS.sleep(100);
      } catch (InterruptedException e) {
          e.printStackTrace();
      }
      log("before acquiring read lock");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      Article article = em.find(Article.class, 1L, LockModeType.PESSIMISTIC_READ);
      log("After acquiring read lock", article);
      em.getTransaction().commit();
      em.close();
      log("Article after read commit", article);
  }

  public static void persistArticle() {
      log("persisting article");
      Article article = new Article("test article");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(article);
      em.getTransaction().commit();
      em.close();
      log("Article persisted", article);
  }

  private static void log(Object... msgs) {
      System.out.println(LocalTime.now() + " - " + Thread.currentThread().getName() +
              " - " + Arrays.toString(msgs));
  }
}
20:38:03.974 - main - [persisting article]
20:38:04.057 - main - [Article persisted, Article{id=1, content='test article'}]
20:38:04.057 - pool-2-thread-1 - [updating Article entity]
20:38:04.173 - pool-2-thread-2 - [before acquiring read lock]
Dec 22, 2017 8:38:06 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 50200, SQLState: HYT00
Dec 22, 2017 8:38:06 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Timeout trying to lock table ; SQL statement:
select article0_.id as id1_0_0_, article0_.content as content2_0_0_ from Article article0_ where article0_.id=? for update [50200-196]
Dec 22, 2017 8:38:06 PM org.hibernate.event.internal.DefaultLoadEventListener doOnLoad
INFO: HHH000327: Error performing load command : org.hibernate.PessimisticLockException: could not extract ResultSet
Exception in thread "pool-2-thread-2" javax.persistence.PessimisticLockException: could not extract ResultSet
	at org.hibernate.internal.ExceptionConverterImpl.wrapLockException(ExceptionConverterImpl.java:249)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:103)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:176)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3431)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3381)
	at com.logicbig.example.PessimisticLockExceptionExample.readArticle(PessimisticLockExceptionExample.java:61)
	at com.logicbig.example.PessimisticLockExceptionExample.lambda$main$1(PessimisticLockExceptionExample.java:26)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.PessimisticLockException: could not extract ResultSet
	at org.hibernate.dialect.H2Dialect$3.convert(H2Dialect.java:353)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:419)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:191)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
	at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
	at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
	at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4083)
	at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
	at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
	at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
	at org.hibernate.event.internal.DefaultLoadEventListener.lockAndLoad(DefaultLoadEventListener.java:403)
	at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:124)
	at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
	at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1239)
	at org.hibernate.internal.SessionImpl.access$1900(SessionImpl.java:203)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2797)
	at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2778)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3407)
	... 6 more
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
select article0_.id as id1_0_0_, article0_.content as content2_0_0_ from Article article0_ where article0_.id=? for update [50200-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:168)
	at org.h2.command.Command.filterConcurrentUpdate(Command.java:310)
	at org.h2.command.Command.executeQuery(Command.java:205)
	at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
	... 23 more
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "ARTICLE": another transaction has updated or deleted the same row [90131-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:168)
	at org.h2.mvstore.db.MVTable.convertException(MVTable.java:898)
	at org.h2.mvstore.db.MVPrimaryIndex.remove(MVPrimaryIndex.java:173)
	at org.h2.mvstore.db.MVTable.removeRow(MVTable.java:678)
	at org.h2.table.TableFilter.lockRows(TableFilter.java:1175)
	at org.h2.command.dml.Select.queryFlat(Select.java:530)
	at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
	at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)
	at org.h2.command.dml.Query.query(Query.java:371)
	at org.h2.command.dml.Query.query(Query.java:333)
	at org.h2.command.CommandContainer.query(CommandContainer.java:113)
	at org.h2.command.Command.executeQuery(Command.java:201)
	... 25 more
Caused by: java.lang.IllegalStateException: Entry is locked [1.4.196/101]
	at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:765)
	at org.h2.mvstore.db.TransactionStore$TransactionMap.set(TransactionStore.java:1035)
	at org.h2.mvstore.db.TransactionStore$TransactionMap.remove(TransactionStore.java:993)
	at org.h2.mvstore.db.MVPrimaryIndex.remove(MVPrimaryIndex.java:167)
	... 34 more

As seen in above output, the read transaction blocks (at em.find call) and then throws PessimisticLockException after waiting beyond the lock wait time. H2 (h2 1.4.196) default lock timeout is 1 second.

An application can choose to set the wait time to 0 to use this exception to control the program flow, or it may choose to use a higher value to block transactions.

Setting javax.persistence.lock.timeout

Setting following properties in persistence.xml:

<properties>
  <property name="javax.persistence.lock.timeout" value="600000"/>
  <property name="javax.persistence.schema-generation.database.action" value="create"/>
  <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=600000"/>
</properties>

Note that javax.persistence.lock.timeout does not seem to have effect in this case, we had to set H2 specific property, LOCK_TIMEOUT to make it work.

Output now:

21:40:14.329 - main - [persisting article]
21:40:14.412 - main - [Article persisted, Article{id=1, content='test article'}]
21:40:14.413 - pool-2-thread-1 - [updating Article entity]
21:40:14.513 - pool-2-thread-2 - [before acquiring read lock]
21:41:14.425 - pool-2-thread-1 - [committing in write thread.]
21:41:14.428 - pool-2-thread-1 - [Article updated, Article{id=1, content='updated content .. '}]
21:41:14.429 - pool-2-thread-2 - [After acquiring read lock, Article{id=1, content='updated content .. '}]
21:41:14.430 - pool-2-thread-2 - [Article after read commit, Article{id=1, content='updated content .. '}]

Example Project

Dependencies and Technologies Used :

  • h2 1.4.196: H2 Database Engine.
  • hibernate-core 5.2.12.Final: The core O/RM functionality as provided by Hibernate.
  • JDK 1.8
  • Maven 3.3.9

PessimisticLockException Example Select All Download
  • pessimistic-lock-exception-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
          • META-INF

See Also