We have experienced the following problem on several different occasions over the last 3-to-4 months, and it seems each time it is resolved, we end up with a new solution.
The problem is with the database isolation level settings on our ejbs. We are currently running our application on AIX on a RS/6000 with WebSphere 3.5.4 and Oracle 8.1.6.
There are two important settings: "Transaction Attribute" and "Isolation Level" on each ejb and the one we have problems with is "Isolation Level". We use TX_REQUIRED for all our session beans and TX_SUPPORTS for our entity beans. Our session beans are a facade to our entity beans. The isolation levels across all our beans are "TRANSACTION_REPEATABLE_READ". Last week, we tried to fix another problem with database serialization and changed the isolation level settings of two beans, ExporterActivityEJB and TransferLogEJB, to "TRANSACTION_READ_COMMITTED". The new settings worked at first but after a while we started to see the problem where the database transaction would be rolled back when we tried to insert a record into the EXPORTER_ACTIVITY table in Oracle. It even seems that the transaction up to that point is not rolled back and the records are still in the database. We changed the settings for the two ejbs back to "TRANSACTION_REPEATABLE_READ". We verified the changes through the WebSphere console and restarted the individual beans. The problem did not get resolved. After that we restarted our application in Websphere but that didn't help either.
After verifying that all deployment descriptors had the correct settings in the ejb jar file and removing and recreating all ejbs in Websphere and restarting the application server the problem finally went away in our test environment. We moved our application forward to the QA environment and repeated the steps that resolved the issue in test there. However, the issue is still happening in QA.
In our production environment we are still using the "TRANSACTION_READ_COMMITTED" settings and the application is still working there.
We are confused since it seems that each combination of settings does randomly either work or not work depending on which environment we are in. Can anyone give us some hints on what is going on?