title: Can a Oracle query done after a commit, return values prior to the commit when such commit is done with COMMIT_WRITE = NOWAIT?
body: <p>I have a 3th party Java library that in a moment, gets a JDBC connection, starts a transaction, does several batch updates with <code>PreparedStatement.addBatch()</code>, executes the batch, commits the transaction and closes the connection. Almost immediately after (in the span of &lt;10 milliseconds), the library gets another connection and queries one of the records affected by the update.</p>
  <p>For the proper functioning of the library, that query should return the updated record. However, in some rare cases, I'm getting (using <a href="https://github.com/p6spy/p6spy" rel="nofollow noreferrer">P6Spy</a>) that the query is returning the record with its values before the update (and the library fails in some point forwards due to unexpected data).</p>
  <p>I'm trying to understand why this would happen, and then I found that in my database (Oracle 19c) there is a parameter <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/COMMIT_WAIT.html#GUID-40E51330-96D0-4B98-BBF3-6A45D970AAB1" rel="nofollow noreferrer">COMMIT_WAIT</a> that basically gives the possibility that a call to a commit doesn't block until the commit is finished, obtaining <a href="https://www.orafaq.com/node/93" rel="nofollow noreferrer">an asynchronous commit</a>. So I used the <code>SHOW PARAMETERS</code> to see the value of that parameter and I found out that <code>COMMIT_WAIT</code> is set up to <code>NOWAIT</code> (also, <code>COMMIT_LOGGING</code> was set up to <code>BATCH</code>).</p>
  <p>I began to speculate if what was happening was that the call to <code>commit()</code> just started the operation (without waiting for it to finish), and perhaps the next query occurred while the operation was still in progress, returning the value of the record before the transaction. (The isolation level for all connections is <code>Connection.TRANSACTION_READ_COMMITTED</code>)</p>
  <p><strong>Can <code>COMMIT_WAIT</code> set up to <code>NOWAIT</code> cause that kind of scenario?</strong> I read that the use of <code>NOWAIT</code> has a lot of risks associated with it, but mostly they refers to things like loss of durability if the database crashes.</p>