title: Oracle lag between commit and select
body: <p>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.  </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Any help would be appreciated.</p>
  
  <p>edit: sample code</p>
  
  <pre><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
  </code></pre>