Derby in a Pinch

Quick guide to using the Derby database server.

Continue reading



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.

Logging JPA SQL Statements in TomEE

If you are coding using JPA, monitoring the generated SQL statements is absolutely necessary. To enable logging of the SQL statements, add these lines in boldface to your persistence.xml for a persistence unit.

<persistence-unit name="VikingoWeb">
        <property name="openjpa.Log" value="SQL=TRACE" />
        <property name="openjpa.ConnectionFactoryProperties" 

The second property will log the parameter values that are injected into a prepared statement.