Doing SELECT FOR UPDATE in JPA

There are certain business operations that simply can not be repeated more than once. For example:

  1. User clicks the submit button to pay for an airline ticket multiple times. The request should only be processed the first time.
  2. There is $150 left in an account. A request to withdraw $100 is sent twice almost at the same time. Only the first one should succeed.

Basically, these operations are not idempotent. SQL provides a great way to reject concurrent requests that should not be processed. This is achieved by simply adding “FOR UPDATE” to a SELECT statement. This puts exclusive lock on the read rows. Any other transaction will block if it tries to read or update those rows.

We can learn how to do SELECT FOR UPDATE using the second example above:

Step 1: First read the account balance table using SELECT FOR UPDATE.

Step 2: Check if there is sufficient balance for the withdrawal. If not abort.

Step 3: Proceed to deduct the withdrawn amount from the balance and commit the transaction.

Multiple transactions executing this routine are guaranteed to run in sequence.

In JPA, achieve SELECT FOR UPDATE by using LockModeType of PESSIMISTIC_WRITE during a query. For example:

EntityManager em;
//Step 1: SELECT FOR UPDATE
AccountBalance ab = em.find(AccountBalance.class, accountId,
    LockModeType.PESSIMISTIC_WRITE);
//Step 2: Validation check
if (ab.getBalance() - withdrawAmount < 0.00) {
   //Error handling. Abort transaction
   //...

   throw ...; //Get out of here
} 
//Step 3: Proceed with operation
ab.setBalance(ab.getBalance() - withdrawAmount);

If you are doing a JPA QL query, then you will need to set the lock mode for the TypedQuery object. For example:

TypedQuery<Cart> q = em.createQuery("select c from Cart c where ...", Cart.class);
q.setLockMode(LockModeType.PESSIMISTIC_WRITE);

Caveat

SELECT FOR UPDATE has a specific purpose. Do not use it indiscriminately. For example, if you are reading product data to display in a page, if you use SELECT FOR UPDATE, the web site will crawl to a halt. There is absolutely no need for it in such a read only situation.

Advertisements

9 thoughts on “Doing SELECT FOR UPDATE in JPA

    • You are correct and I have changed the post. I should also mention that using LockModeType.PESSIMISTIC_READ also causes OpenJPA to do SELECT FOR UPDATE. I guess, that is an easy way to ensure repetable read. The correct way would have been to use repeatable read transaction isolation level. But, I do not think JPA has any way to control the isolation level of a transaction, which must be set at the JDBC connection level prior to starting a transaction.

    • Hi,

      It is assumed that all JPA code will execute in some kind of a transaction. A common scenario is to use them from within a stateless session EJB where all EJB methods are by default executed within a transaction.

  1. How LockModeType.PESSIMISTIC_READ can be extended to child record ? is it possible ?
    So I have employee – and
    1- an employee can have many address;
    2- two employees can share same address. this is what the design is with some legacy system.
    Now if i want to update one employee with new address – considering 2 i want to lock address as well ; can we include lock on address as well?

  2. Hi, just a question I have with a issue that recently happened to my. this way to do the queries can apply to java apps which are running in different JVM, but are connecting to the same Data Base? I mean can I lock queries from diferents JVMs or this happend to level of the hibernate cache?

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