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;
AccountBalance ab = em.find(AccountBalance.class, accountId,
//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);


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.

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

Leave a Reply

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

You are commenting using your 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