by Rahul Biswas

In this tip, you'll learn how to prevent non-repeatable reads through the Java Persistence API (JPA) and JPA extensions provided by EclipseLink.

Non-Repeatable Reads

A non-repeatable read is a concept pertinent to database transactions. In a non-repeatable read, multiple reads of a data item from a datasource do not return the same value for the data item. In JPA terms, a non-repeatable read means that within a transaction, if an application reads the same entity multiple times from a datasource, the application will find that the entity state has changed between reads.

Non-repeatable reads apply to the following scenario: A transaction, T1, reads a row in a database. Another transaction, T2, then modifies or deletes that row before T1 has committed. Both transactions eventually commit successfully.

Generally one of the following approaches is used to prevent that scenario:

• Optimistic. This approach assumes that the same data is not being read and written concurrently. The write transaction is allowed to commit. However, the read transaction is required to detect the changed value when it attempts to commit.
• Pessimistic. This approach assumes that the same data may be read and written concurrently. The read transaction locks the row representing the data in the underlying datasource. The write transaction can commit only after the read commits. During the read transaction the value of the data being locked does not change.

Consider, for example, the following simple table in a database:
Id Description Price 720 Expensive Item 582.99 721 Nice to have Item 66.99

Suppose an application, A, starts a transaction, T1, and queries the table to retrieve the price for "Expensive Item". Suppose A then starts another transaction, T2, to update the price for "Expensive Item".

Figure 1 illustrates the optimistic approach. Transaction T1 assumes that data is not being concurrently modified, although, in fact, transaction T2 does concurrently modify the data and proceeds with the commit. When T1 attempts to commit, it detects the change and notifies the application, which may rollback the T1 transaction.

Figure 1. Preventing Non-Repeatable Reads Using an Optimistic Approach

Figure 2 illustrates the pessimistic approach. Here, the T2 update is blocked until the T1 transaction commits.

Figure 2. Preventing Non-Repeatable Reads Using a Pessimistic Approach

Preventing Non-Repeatable Reads in JPA

You can use JPA to prevent non-repeatable reads on versioned entities. A versioned entity is marked with the @Version annotation, as illustrated in the following code snippet:

   @Entity

   public class StockQuote implements Serializable {

       @Version
       public Long getVersion() {
           return version;
       }

and its corresponding database schema has a version column, such as that created by the following SQL statement:

   CREATE TABLE STOCKQUOTE
   (ID NUMBER NOT NULL, VERSION NUMBER, PRICE FLOAT, DESCRIPTION VARCHAR(255),
      PRIMARY KEY (ID));

Versioning enables JPA to manage optimistic locking. Optimistic locking assumes that there will be infrequent conflicts between concurrent transactions. In optimistic locking, the objective is to give concurrent transactions a lot of freedom to process simultaneously, but to detect and prevent collisions.

The way you prevent non-repeatable reads in JPA on a versioned entity is through the lock() method of the EntityManager class. Here is the method signature:

   public void lock(Object entity, LockModeType lockMode);

The first method parameter is the entity instance that needs to be locked in the transaction.

The second method parameter is the lock mode, which can have one of the following values:

• READ
• WRITE

Both lock modes prevent non-repeatable reads. However, the WRITE lock mode also forces the version column to be updated.

As illustrated in Figure 1, transaction T1 acquires a read lock, but transaction T2 is allowed to commit its changes. When transaction T1 tries to commit, the EclipseLink JPA implementation detects that the data changed since the last read. It does this by checking the version column. It then throws an OptimisticLockException to application A. At this point, the application can retry the operation after refreshing the value of the entity. Alternatively, the application can abort the operation and rollback the transaction.

Taking Advantage of EclipseLink Extensions

EclipseLink is an open source project whose goal is to provide a comprehensive persistence framework that will run in any Java environment and that will support the reading and writing of objects to and from virtually any type of data source. One of the project's deliverables is an advanced features extension to JPA. You can take advantage of this extension to ensure repeatable reads through pessimitic locking, something that is not currently supported in the JPA 1.0 specification. However, this solution is not portable because it uses EclipseLink-specific extensions.

The way to ensure repeatable reads with EclipseLink is through its support for pessimistic locking on JPA Query Language (JPA QL) queries. Pessimistic locking assumes that there will be frequent conflicts between concurrent transactions. To prevent collisions during pessimistic locking, an entity is locked in the database for the entire time that it is in application memory.

EclipseLink enables pessimistic locks on JPA QL queries through query hints, which are JPA extension points for vendor-specific query features.

There are two ways to enable a pessimistic lock through a JPA QL query hint. One way is to use a @NamedQuery annotation, as in the following example:

   @NamedQuery(
     name="GetStock"
     query="select sq from StockQuote as sq where sq.id = :id"
     hints={@QueryHint(name=EclipseLinkQueryHints.PESSIMISTIC_LOCK,
             value=PessimisticLock.Lock)})

The other way is to use the Query API, as in the following example:

   Query q = em.createNamedQuery("GetStock");
   q.setHint(EclipseLinkQueryHints.PESSIMISTIC_LOCK,
                   PessimisticLock.Lock);
   q.setParameter("id", 1);

Acquiring a pessimistic lock through either of these techniques typically locks the pertinent row in the underlying database. As illustrated in Figure 2, if transaction T1 runs either of the two queries above, it locks the underlying datasource row and blocks T2 from committing its updates. After T1 commits, T2 can commit its changes.

Sample Application

Let's look at a sample application that prevents non-repeatable reads through EclipseLink JPA extensions. In fact, the application also allows non-repeatable reads so that you can compare the results of both types of reads. You can find the application in the sample package that accompanies this tip.

The sample application is a simplified version of a stock market application that would typically handle a large number of concurrent transactions, and in doing so, service simultaneous reads and writes to entities. In general, here's what the application does:

1. Creates a table in a database. Here are the SQL statements that create the table (you can find this code in file createDDL.ddbc):

      CREATE TABLE STOCKQUOTE
          (ID NUMBER NOT NULL,
          VERSION INTEGER,
          PRICE FLOAT,
          DESCRIPTION VARCHAR(255),
          PRIMARY KEY (ID))
      ;
   
      INSERT INTO STOCKQUOTE (ID, PRICE, VERSION, DESCRIPTION)
          VALUES(1, 23, 1, 'JAVA')
      ;
   
      CREATE TABLE SEQUENCE (
          SEQ_NAME VARCHAR(50) NOT NULL,
          SEQ_COUNT INTEGER,
          PRIMARY KEY (SEQ_NAME))
      ;
   
      INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values ('SEQ_GEN', 1);
   

    
   Notice the VERSION column in the table.
2. Establishes a persistence unit that specifies the metadata files, classes, and JAR files for the persisted entities. The persistence unit is always specified in the persistence.xml file in the META-INF directory of an application. Here is the content of the persistence.xml file for the sample application:

      <?xml version="1.0" encoding="UTF-8"?>
      <persistence version="1.0"
       xmlns="http://java.sun.com/xml/ns/persistence"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
        <persistence-unit name="tech-tip-4-samplePU" transaction-type="RESOURCE_LOCAL">
          <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
          <class>com.sun.techtip.sample.jpa.entity.StockQuote</class>
          <properties>
            <property name="eclipselink.jdbc.user" value="[your-db-userid]"/>
            <property name="eclipselink.jdbc.password" value="[your-db-password]"/>
            <property name="eclipselink.jdbc.url" value="jdbc:oracle:thin:@[your-host]:[your-port]:[your-db-sid]"/>
            <property name="eclipselink.jdbc.driver" value="oracle.jdbc.driver.OracleDriver"/>
          </properties>
        </persistence-unit>
      </persistence>
   

    
   Notice that the persistence unit specifies EclipseLink as the JPA persistence provider.
3. Creates two worker threads, a ReaderThread and a WriterThread thread. The application can run in one of three modes, repeatable read with optimistic locking, repeatable read with pessimistic locking or non-repeatable read. You indicate which mode by specifying rr and o, for repeatable read with optimistic locking, rr and p, for repeatable read with pessimistic locking, or nrr, for non-repeatable read, when you start the application. The application uses the entries (or entry) as arguments when it starts the ReaderThread. The code for this part of the application is in file Main.java. Here is a snippet of that code:

      public class Main {
   
          private static final String NRR = "nrr";
          private static final String RR="rr";
          private static final String P="p";
          private static final String O="o";
   
          public Main() {
          }
   
          public static void main(String[] args){
              if(args==null || args.length < 1){
                  displayUsage();
                  System.exit(-1);
              }
   
              boolean bLock = RR.equals(args[0].trim());
   
              if(args.length < 2 && bLock){
                  displayUsage();
                  System.exit(-1);
              }
   
              boolean isPessimistic = bLock? P.equals(args[1].trim()): false;
   
              System.out.println("Running sample for " +
                      (bLock? "Repeatable read ":"Non-repeatable read ") +
                      (bLock? (isPessimistic? "in pessimistic mode":"in optimisitic mode"):
                       ""));
   
              //create two worker threads with lock options
              ReaderThread rt = new ReaderThread(bLock, isPessimistic);
              WriterThread wt = new WriterThread();
   
              //start the run
              new Thread(rt).start();
              new Thread(wt).start();
   

    
   

In repeatable read with optimistic locking mode, the ReaderThread begins a transaction on an entity. The WriterThread then updates the stock price during the ReaderThread transaction and successfully commits. The ReaderThread attempts to commit its transaction. However, the underlying JPA implementation detects that the corresponding database row has been updated by another thread after it was last read, so it throws an OptimisticLockException. The application catches this exception and flags the transaction as failed.

In repeatable read with pessimistic locking mode, the ReaderThread begins a transaction on an entity. The WriterThread then attempts to update the stock price during the ReaderThread transaction. However, the update attempt is blocked because the ReaderThread has a lock on both the entity object and the underlying database row representing the object.

In non-repeatable read mode, the ReaderThread begins a transaction on an entity. Then the WriterThread updates the stock price on the same entity and commits its transaction before the ReaderThread commits its transaction. The ReaderThread detects the conflict by detecting the difference in the price and prints out a warning message.

Here is part of the code in the ReaderThread:

   public class ReaderThread implements Runnable{

       private EntityManagerFactory emf;
       private boolean bLock;
       private boolean isPessimistic;

       public ReaderThread(boolean bLock, boolean isPessimistic) {
           this.bLock = bLock;
           this.isPessimistic=isPessimistic;
       }

       public void run() {
           emf = DataSourceManager.getInstance().getEMF();
           read();
       }

       private void read(){

           EntityManager em = emf.createEntityManager();
           em.getTransaction().begin();

           StockQuote sq;

           if(bLock){
               if (isPessimistic) {
                   Query q = em.createNamedQuery("GetStock");
                   q.setParameter("id", 1);
                   q.setHint(EclipseLinkQueryHints.PESSIMISTIC_LOCK,
                           PessimisticLock.Lock);
                   sq = (StockQuote) q.getSingleResult();

               }else{
                   sq = em.find(StockQuote.class, new Long(1));
                   em.lock(sq, LockModeType.READ);
               }

           }else{
               sq = em.find(StockQuote.class, new Long(1));
           }

           double price = sq.getPrice();

           System.out.println("ReaderThread: original price - "+ price);

           //now wait for write thread to do it's update
           while(!WriterThread.readyToWrite.get()){
               try{
                   System.out.println("ReaderThread: waiting for writer thread to" +
                           " be ready...");
                   Thread.currentThread().sleep(100);
               }catch(InterruptedException iex){
                   iex.printStackTrace();
               }
           }

           WriterThread.attemptWrite.set(true);
           System.out.println("ReaderThread: giving WriterThread OK to commit...");

           //write thread has attempted it's write
           System.out.println("ReaderThread: hit enter to continue....");
           try{
                   System.in.read();
           }catch(Exception ex){
               ex.printStackTrace();
           }

           if(!bLock || (bLock && isPessimistic)){
                em.refresh(sq);
           }
           double _price = sq.getPrice();

           try{
               em.getTransaction().commit();
               System.out.println("ReaderThread: latest price - " + _price);

               if (_price != price && !bLock) {
                   System.out.println("Price was modified by the writer thread, " +
                           "repeatable read failed as expected.");
               } else if (!bLock) {
                   System.out.println("Price did not change inspite of " +
                           "concurrent update!");
               } else if (_price == price && bLock) {
                   System.out.println("Repeatable read succeeded!");
               } else {
                   System.out.println("The app failed in testing " +
                           "repeatable reads, please check your DB supports " +
                           "row locking.");
               }
           }catch(Exception ex){
               System.out.println("ReaderThread: Transaction failed with following" +
                       " message -- "+ex.getMessage());
           }

       }
   }

 
Notice that the ReaderThread enables repeatable reads through pessimistic locking in a JPA QL query hint:

   if(bLock){
            if (isPessimistic) {
                Query q = em.createNamedQuery("GetStock");
                q.setParameter("id", 1);
                q.setHint(EclipseLinkQueryHints.PESSIMISTIC_LOCK,
                        PessimisticLock.Lock);
                sq = (StockQuote) q.getSingleResult();

            }else{
                sq = em.find(StockQuote.class, new Long(1));
                em.lock(sq, LockModeType.READ);
            }

     }

Running the Sample Application

A sample package accompanies this tip. To install and run the application in the sample package:

1. Download the sample package and extract its contents. You should now see a newly extracted directory <sample_install_dir>/jpa-repeatable-read.tech-tip, where <sample_install_dir> is the directory where you installed the sample package. For example, if you extracted the contents to C:\ on a Windows machine, then your newly created directory should be at C:\jpa-repeatable-read.tech-tip.
2. Change to the jpa-repeatable-read.tech-tip directory and set the values for following properties in the build.xml file as appropriate for your operating environment: jdbc.url, db.userid, db.password, and javaee.home. The application uses these property settings to update the persistence.xml file and in the initial setup on the database.
3. Set up the database and establish the persistence unit by entering the following command in the jpa-repeatable-read.tech-tip directory:

      ant setup
   

4. Run the application in repeatable read with optimistic lock mode. Ensure that the arguments of the run target in the build.xml file have the values rr and o as follows:

   <target name="run" depends="clean, pusetup, compile">
           <java classname="com.sun.techtip.sample.Main" fork="true">
               <arg value="rr"/>
               <arg value="o"/>

Then enter the following command in the jpa-repeatable-read.tech-tip directory:

   ant run all

You should see output similar to the following:

   Running sample for Repeatable read in optimisitic mode
   [EL Info]: 2008.07.01 12:46:16.500--ServerSession(17423963)--EclipseLink, version: Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)
   [EL Info]: 2008.07.01 12:46:19.343--ServerSession(17423963)--file:/D:/work/articles/jpa-repeatable-read.tech-tip/build/classes/-tech-tip-4-samplePU login successful
   WriterThread: original price - 40.745903
   WriterThread: ready, need OK from  ReaderThread...
   ReaderThread: original price - 40.745903
   ReaderThread: giving WriterThread OK to commit...
   ReaderThread: hit enter to continue....
   WriterThread: starting to commit now...
   WriterThread: committed tx...
   WriterThread: updated price - 44.8204933

   [EL Warning]: 2008.07.01 12:47:37.906--UnitOfWork(24118161)--Exception [EclipseLink-5006] (Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)): org.eclipse.persistence.exceptions.OptimisticLockException
   Exception Description: The object [com.sun.techtip.sample.jpa.entity.StockQuote@878c4c] cannot be updated because it has changed or been deleted since it was last read.
   Class> com.sun.techtip.sample.jpa.entity.StockQuote Primary Key> [1]
   [EL Warning]: 2008.07.01 12:47:37.921--UnitOfWork(24118161)--javax.persistence.OptimisticLockException: Exception [EclipseLink-5006] (Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)): org.eclipse.persistence.exceptions.OptimisticLockException
   Exception Description: The object [com.sun.techtip.sample.jpa.entity.StockQuote@878c4c] cannot be updated because it has changed or been deleted since it was last read.
   Class> com.sun.techtip.sample.jpa.entity.StockQuote Primary Key> [1]
   ReaderThread: Transaction failed with following message -- javax.persistence.OptimisticLockException: Exception [EclipseLink-5006] (Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)): org.eclipse.persistence.exceptions.OptimisticLockException
   Exception Description: The object [com.sun.techtip.sample.jpa.entity.StockQuote@878c4c] cannot be updated because it has changed or been deleted since it was last read.
   Class> com.sun.techtip.sample.jpa.entity.StockQuote Primary Key> [1]

 
Notice that the non-repeatable read was prevented. The underlying JPA implementation detects a change to the price by the WriterThread and throws an OptimisticLockException. Then the ReaderThread catches the exception and flags the transaction as failed.
5. Run the application in repeatable read with pessimistic lock mode. Ensure that the arguments of the run target in the build.xml file have the values rr and p as follows:

   <target name="run" depends="clean, pusetup, compile">
        <java classname="com.sun.techtip.sample.Main" fork="true">
            <arg value="rr"/>
            <arg value="p"/>

   ant run all

   Running sample for Repeatable read in pessimistic mode
   [EL Info]: 2008.07.01 12:53:50.078--ServerSession(17423963)--EclipseLink, version: Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)
   [EL Info]: 2008.07.01 12:53:51.875--ServerSession(17423963)--file:/D:/work/articles/jpa-repeatable-read.tech-tip/build/classes/-tech-tip-4-samplePU login successful
   WriterThread: original price - 44.8204933
   WriterThread: ready, need OK from  ReaderThread...
   ReaderThread: original price - 44.8204933
   ReaderThread: giving WriterThread OK to commit...
   ReaderThread: hit enter to continue....
   WriterThread: starting to commit now...

   ReaderThread: latest price - 44.8204933
   Repeatable read succeeded!
   WriterThread: committed tx...
   WriterThread: updated price - 49.302542630000005

   ant run all

   Running sample for Non-repeatable read
   [EL Info]: 2008.07.01 12:55:33.906--ServerSession(17423963)--EclipseLink, version: Eclipse Persistence Services - 1.0 (Build SNAPSHOT - 20080604)
   [EL Info]: 2008.07.01 12:55:35.734--ServerSession(17423963)--file:/D:/work/articles/jpa-repeatable-read.tech-tip/build/classes/-tech-tip-4-samplePU login successful
   ReaderThread: original price - 49.30254263
   ReaderThread: waiting for writer thread to be ready...
   WriterThread: original price - 49.30254263
   WriterThread: ready, need OK from  ReaderThread...
   ReaderThread: giving WriterThread OK to commit...
   ReaderThread: hit enter to continue....
   WriterThread: starting to commit now...
   WriterThread: committed tx...
   WriterThread: updated price - 54.232796893

   ReaderThread: latest price - 54.232796893
   Price was modified by the writer thread, repeatable read failed as expected.