JPA ISSUE - Execution of Double (or Multiple) UPDATE SQL Statements for the Updates done on the same Entity Object

Issue:

In a JPA transaction, Updating of the same entity is split into two separate UPDATE SQL statement execution.

Reason:

1. Some properties of an entity is updated in a transaction.
2. Then there is a db query to retrieve some data.
3. This query execution cause the above entity modification to flush to the db. (Due to the JPA implementation).
•  This cause the execution of the first UPDATE SQL statement.
4. Update another set of properties of the same entity.
5. At the next flush event (at the end of TX or execution of another query), The execution of the second UPDATE SQL statement on the same record executed for the last change..

E.g:

// Start modifying bankAccount entity
bankAccount.setName(name);
// Execute a Query which causes flush
List<PaymentOrganization>  paymentOrganizations = paymentOrganizationFacade.getAllOrganizations();
// modify another attribute of same entity
bankAccount.setPaymentOrganizations(paymentOrganizations);

Solution:

Do not put any query execution in between the modifications of different attributes of the same entity object.

E.g:
Move the query before starting the modification of the entity.

List<PaymentOrganization>  paymentOrganizations = paymentOrganizationFacade.getAllOrganizations();
bankAccount.setName(name);
bankAccount.setPaymentOrganizations(paymentOrganizations);

A JPA issue which was caused by the Oracle DB's treatment of empty strings in VARCHAR fileds as NULL

Yesterday I encountered a strange issue when I was doing a performance test of my current project using JMeter. My project uses EJB 3 on Weblogic 10.3 server and an Oracle 11g database. I executed a JMeter test with 25 threads with 4000 iterations. But on each test execution, the test cases failed with a UniqueConstraintException after running the test cases for about 10 minutes. 

That unique constraint violation exception happened because system was trying to insert a record which was already inserted to the database in setUpTest() method in JMeter java sampler client. But this was not supposed to happen because in actual test cases (in runTest() methods), the system runs a find query to check whether a record with matching value is already existing in the database before inserting any record of the specific type to the database. Another strange thing was this happened only to the test cases executed after the first 10 minutes of the test.

So after some investigation, I found that in the setUpTest() method, one field was set to an empty string in the specific entity object (record) I mentioned earlier. But when I checked the relevant record in the Oracle database, it contained NULL value instead of an empty string for the relevant field. 

After some googling, it turned out that when JPA (or using normal SQL query) insert a empty string to Oracle database, Oracle converts it to a NULL before storing it in the database. 

So the reason behind my issue is when JPA insert an entity object which has an empty string as the value for a its property, Oracle changes that field value to NULL and store NULL value in the database instead of an empty string. But in the JPA cache, the value of the relevant property of the inserted object is still an empty string, not a NULL value. When system runs the JPQL find query, it checks for the relevant object in the cache first. Because the relevant newly inserted object still has an empty string for the value of the relevant property, the find query returns that object, so system doesn't try to create a new object with the same values again. Therefore we don't get an unique constraint violation exception. But after about 10 minutes, the JPA cache expires and cache is reloaded from the database. At this time, the value for the previously mentioned field of the reloaded specific object is no longer an empty string, because what is in the database for that field is NULL. So from now on, when the system tries to find an object with a value of empty string for this field, it is no longer able to find it. Therefore it assumes this record doesn't exist in the database and tries to re-insert the record.  But because Oracle changes the empty string to NULL when it inserts the relevant record to the database and because a record with NULL already exists in the database, it throws an unique constraint violation exception.

See this discussion in stackoverflow for more details on why Oracle treats VARCHAR empty strings as NULL.

http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null