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