Oracle lag between commit and select

Question

We have an Java workflow application that uses an Oracle database to track its steps and interactions with other services. During a workflow run several insert/update/selects are performed and occasionally the select will not return the updated data, even though the insert/update commit that ran before it completed successfully. After the workflow errors out (due to the bad data), if we go back and check the database through a 3rd party app the new/updated data will show up. There seems to be a lag between when our commits go through and when they are visible. This happens in roughly 2% of all workflow runs and it increases during heavy database usage.

Our database support team suggested to change a parameter max-commit-propagation-delay to 0, as it defaulted to 700. This appeared to be a possible solution but ultimately did not fix our problem.

The application runs on WebSphere and the Oracle database is configured as a JDBC datasource. We are using Oracle 10.1g. The application is written in Java 1.5.

Any help would be appreciated.

edit: sample code

DataSource ds; // spring configured

String sql = "INSERT INTO " + currentTable + " (" + stepId + ',' + stepEntryId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)";

Connection conn = ds.getConnection();
PreparedStatement stmt = conn.prepareStatement(sql);
// set values
stmt.executeUpdate();
// close connections

// later on in the code...
Connection conn = ds.getConnection();
PreparedStatement stmt = null;
ResultSet rset = null;

String sql = "SELECT " + stepId + ", " + stepStepId + ", " + stepActionId + ", " + stepOwner + ", " + stepStartDate + ", " + stepDueDate + ", " + stepFinishDate + ", " + stepStatus + ", " + stepCaller + " FROM " + currentTable + " WHERE " + stepEntryId + " = ?";
stmt = conn.prepareStatement(sql);

stmt.setLong(1, entryId);

rset = stmt.executeQuery();
//close connections

Answer 1

By default, the behavior you described should be impossible - changes made in a committed transaction become available immediately to all sessions. However, there are exceptions:

1. Are you using any of the WRITE options in the COMMIT command? If you are not, confirm the value of your COMMIT_WRITE initialization parameter. If either is using the "WRITE BATCH" or especially "WRITE BATCH NOWAIT", you could be opening yourself up to concurrency issues. "WRITE BATCH NOWAIT" would typically be used in cases where the speed of your write transactions is of greater importance than possible concurrency issues. If your initialization parameter is using the "WRITE" variants, you can override it on a transaction basis by specifying the IMMEDIATE clause in your commits (see COMMIT)

2. Is the transaction that is attempting to read the data invoking SET TRANSACTION prior to the other transaction committing? Using SET TRANSACTION to specify SERIALIZATION LEVEL READ ONLY or SERIALIZABLE will result in the the transaction seeing no changes that occur from other committed sessions that occurred after the invocation of SET TRANSACTION (see SET TRANSACTION)

edit: I see that you're using a DataSource class. I'm not familiar with this class - I assume it's a connection sharing resource. I realize that your current app design may not make it easy to use the same connection object throughout your work flow (the steps may designed to operate independently, and you didn't build in a facility to pass a connection object from one step to the next), but you should verify that connection objects being returned to the DataSource object are "clean", especially with regard to open transactions. It may be possible that you are not invoking SET TRANSACTION in your code, but another consumer of DataSource elsewhere may be doing so, and returning the connection back to the datasource with the session still in SERIALIZABLE or READ ONLY mode. When connection sharing, it is imperative that all connections be rolled back before handing them off to a new consumer.

If you have no control or visibility to the behavior of the DataSource class, you may wish to try executing a ROLLBACK on the newly acquired connection to insure it has no lingering transaction already established.