13 October 2009

Exploring EclipseLink @OptimisticLocking

Been a while since I've had some fun with JPA, so I decided to spend a little time with it today.

I created a very simple domain model (Employee --> LeaveRecord) to use.



Since I was just intent of doing some quick testing, instead of following the usual route of creating an EJB session facade to expose the @Entity objects, and then exercising that from a client to test things out, I simply created some JUnit4 @Test cases to act as the test clients. These @Test cases exercised the @Entity objects from outside the container, so it was actually a very easy way to go.





One thing I'd never looked at much was the EclipseLink specific annotations, so I decided to take a quick peek around there for something interesting to test. A quick peruse of the EclipseLink documentation drew me to the @OptimisticLocking annotation.  How could you not be optimistic with that!

The goal of the @OptimisticLocking annotation is to direct EclipseLink to use an optimistic locking strategy for the @Entity, directing it to the current property values from the object it is persisting against the data currently in the database to ensure it hasn't changed since it was last read. 

There are several different options available, so I took a look at the differences between the OptimisticLockingType.ALL_COLUMNS and the OptimisticLockingType.CHANGED_COLUMNS options.

The @OptimisticLocking annotation is specified on the POJO.

  @Table(name = "EMPLOYEES")
  @OptimisticLocking(type=OptimisticLockingType.ALL_COLUMNS)
  public class Employee implements Serializable {
      ...
  }
 
Doing simple reads and updates of the Employee @Entity with the eclipselink.logging.level set to FINEST shows the SQL that is created when the different types are applied.

@OptimisticLocking(type=OptimisticLockingType.ALL_COLUMNS)
Connection(26174809)--UPDATE EMPLOYEES SET VACATION_HOURS = ? 
  WHERE ((EMPLOYEE_ID = ?) AND 
  (((((EMAIL_ADDRESS = ?) AND 
      (FIRST_NAME = ?)) AND 
      (LAST_NAME = ?)) AND 
      (SALARY = ?)) AND 
      (VACATION_HOURS = ?)))
   bind => [999, 1, jack.rooster@anon.org1, Jack1, Rooster1, 1.0, 1]

In this configuration, all the fields of the @Entity are contained in the WHERE clause of the UPDATE statement.

@OptimisticLocking(type=OptimisticLockingType.CHANGED_COLUMNS)
Connection(2554341)--UPDATE EMPLOYEES SET VACATION_HOURS = ? 
  WHERE ((EMPLOYEE_ID = ?) AND (VACATION_HOURS = ?))
  bind => [999, 1, 1]

In this configuration, only the updated fields of the @Entity are contained in the WHERE clause of the UPDATE statement.

After testing the @OptimisticLocking annotation and observing that I worked as expected in my test environment, the next step was to test what happens when a change is made to an object after it has been read, but before it is updated.

The flow is essentially this:

  T1 --> read employee 1
  T1 --> create and start T2
    T2 --> read employee 1
    T2 --> update employee 1
    T2 --> persist employee 1
  T1 --> update employee 1
  T1 --> persist employee 1  *expect OptimisticLockingException*

The @Test case below represents this sequence.

@Test(expected = RollbackException.class, timeout = 20000)
public void checkOptimisticLocking() throws Exception {

    Employee pre = employeePM.find(Employee.class, Long.valueOf(1));

    // do the separate thread update of the specified employee with value
    EmployeeTestOptimisticLockingHelper t = 
        new EmployeeTestOptimisticLockingHelper(1L, -999L);

    t.start();
    t.join(10000);

    // Now do the local the update
    // should throw OptimisticLockException
    employeePM.getTransaction().begin();
    pre.setVacationHours(999L);
    employeePM.getTransaction().commit();
}

The EmployeeTestOptimisticLockingHelper is a separate class that is executed via another Thread.  This allows it to perform the change using a separate EntityManager.

public class EmployeeTestOptimisticLockingHelper extends Thread {

    private Long id;
    private Long newval;

    public EmployeeTestOptimisticLockingHelper(Long id, Long newval) {
        this.id = id;
        this.newval = newval;
    }

    @Override
    public void run() {
        EntityManager em = null;
        try {
            em = Persistence.createEntityManagerFactory("CompanyUnit")
                            .createEntityManager();
            Employee emp = em.find(Employee.class, id);
            em.getTransaction().begin();
            emp.setVacationHours(newval);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            em.close();
            interrupt();
        }
    }
}

When this @Test is executed, it results in the following exception being thrown, demonstrating that the specified @OptimisticLocking model is working as expected.

[EL Finer]: 2009.10.13 14:54:43.796--ClientSession(14031599)--Connection(26174809)--rollback transaction
[EL Warning]: 2009.10.13 14:54:43.796--UnitOfWork(26953544)--javax.persistence.OptimisticLockException: Exception [EclipseLink-5006] (Eclipse Persistence Services - 1.0.2 (Build 20081024)): org.eclipse.persistence.exceptions.OptimisticLockException
Exception Description: The object [Employee 1 Jack1 Rooster1 jack.rooster@anon.org1 999 $1.0] cannot be updated because it has changed or been deleted since it was last read. 
Class> sab.demo.company.domain.Employee Primary Key> [1]
 at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:480)
 at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1330)
 at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:159)
 at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1002)
 at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
 at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
 at sab.demo.testdomains.EmployeeTest.checkOptimisticLocking(EmployeeTest.java:149)

And the JUnit runner shows the @Test passes as expected, since it is configred to expect the wrapper javax.persistence.RollbackException to occur.



07 October 2009

Getting samples schema to run against an Oracle DB

The samples domain that is able to be optionally created with a WebLogic Server installation provides a wealth of useful examples, covering core JavaEE APIs through to an full sample application called MedRec.

By default, the samples domain installs and uses a PointBase database to host its application data, which allows it to work immediately after installation with no additional requirements to install, configure a database.

It probably goes without saying, but there is interest in making the samples also work with an Oracle database.

When the samples domain is created, it provides its own set of online documentation that describes how to configure, install and use the various samples.

One section in the doc notes how to install the sample schema against an Oracle database, which ultimately results in the execution of the demo.ddl file against the specified Oracle database instance.

>ant db.setup.oracle


I just tried it against an Oracle XE (10.2) database I have lying around and noticed some problems when it executed, based on inserting date format data into several of the tables used by the MedRec application.

[sql] Failed to execute:   INSERT INTO patient (id,first_name,middle_name,last_name,dob,gender,ssn,address_id,phone,email) VALUES (101,'Fred','I','Winner','1965-03-26','Male','123456789',101,'4151234564','fred@golf.com')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format string

[sql] Failed to execute:   INSERT INTO record (id,pat_id,phys_id,record_date,vital_id,symptoms,diagnosis,notes) VALUES (101,101,102,'1999-06-18',101,'Complains about chest pain.','Mild stroke.  Aspiran advised.','Patient needs to stop smoking.')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format [sql] 

[sql] Failed to execute:   INSERT INTO prescription (id,pat_id,date_prescribed,drug,record_id,dosage,frequency,refills_remaining,instructions) VALUES (101,101,'1999-06-18','Advil',101,'100 tbls','1/4hrs',0,'No instructions')
[sql] java.sql.SQLDataException: ORA-01861: literal does not match format string


To workaround this, I found that by editing the $WLS_HOME\wlserver_10.3\samples\server\examples\src\examples\common\demo.ddl and appending the DATE function to the respective insert statements, the demo.ddl script executed without error and the data was inserted correctly.

Here are examples of the row inserts that have been modified:

INSERT INTO patient (id,first_name,middle_name,last_name,dob,gender,ssn,address_id,phone,email) 
VALUES 
(101,'Fred','I','Winner',DATE'1965-03-26','Male','123456789',101,'4151234564','fred@golf.com');

INSERT INTO record (id,pat_id,phys_id,record_date,vital_id,symptoms,diagnosis,notes) 
VALUES 
(101,101,102,DATE'1999-06-18',101,'Complains about chest pain.','Mild stroke.  Aspiran advised.','Patient needs to stop smoking.');

INSERT INTO prescription
(id,pat_id,date_prescribed,drug,record_id,dosage,frequency,refills_remaining,instructions) VALUES
(101,101,DATE'1999-06-18','Advil',101,'100 tbls','1/4hrs',0,'No instructions');

Once that was done, the demo.ddl executed successfully and the schema was created in the specified Oracle database.