StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat's a good way to handle "async" commits?
text
Body
copied!<p>I have a WCF service that uses <a href="http://www.oracle.com/technology/tech/windows/odpnet/index.html" rel="nofollow noreferrer">ODP.NET</a> to read data from an Oracle database. The service also writes to the database, but indirectly, as all updates and inserts are achieved through an older layer of business logic that I access via COM+, which I wrap in a <a href="http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx" rel="nofollow noreferrer">TransactionScope</a>. The older layer connects to Oracle via ODBC, not ODP.NET.</p> <p>The problem I have is that because Oracle uses a two-phase-commit, and because the older business layer is using ODBC and not ODP.NET, the transaction <em>sometimes</em> returns on the <code>TransactionScope.Commit()</code> before the data is actually available for reads from the service layer. </p> <p>I see <a href="https://stackoverflow.com/questions/983296/oracle-lag-between-commit-and-select">a similar post about a Java user having trouble like this</a> as well on Stack Overflow.</p> <p>A representative from Oracle <a href="http://forums.oracle.com/forums/thread.jspa?threadID=958260&tstart=0" rel="nofollow noreferrer">posted that there isn't much I can do about this</a> problem:</p> <blockquote> <p>This maybe due to the way OLETx ITransaction::Commit() method behaves. After phase 1 of the 2PC (i.e. the prepare phase) if all is successful, <strong>commit can return even if the resource managers haven't actually committed</strong>. After all the successful "prepare" is a guarantee that the resource managers cannot arbitrarily abort after this point. Thus even though a resource manager couldn't commit because it didn't receive a "commit" notification from the MSDTC (due to say a communication failure), the component's commit request returns successfully. If you select rows from the table(s) immediately you may sometimes see the actual commit occur in the database after you have already executed your select. Your select will not therefore see the new rows due to consistent read semantics. There is nothing we can do about this in Oracle as the "commit success after successful phase 1" optimization is part of the MSDTC's implementation.</p> </blockquote> <p><strong><em>So, my question is this:</em></strong></p> <p>How should I go about dealing with the possible delay ("asyc" via the title) problem of figuring out when the second part of the 2PC actually occurs, so I can be sure that data I inserted (indirectly) is actually available to be selected after the <code>Commit()</code> call returns?</p> <p><strong>How do big systems deal with the fact that the data might not be ready for reading immediately?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload