What's a good way to handle "async" commits?

Question

I have a WCF service that uses ODP.NET 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 TransactionScope. The older layer connects to Oracle via ODBC, not ODP.NET.

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 sometimes returns on the TransactionScope.Commit() before the data is actually available for reads from the service layer.

I see a similar post about a Java user having trouble like this as well on Stack Overflow.

A representative from Oracle posted that there isn't much I can do about this problem:

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, commit can return even if the resource managers haven't actually committed. 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.

So, my question is this:

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 Commit() call returns?

How do big systems deal with the fact that the data might not be ready for reading immediately?

Answer 1

I assume that the whole transaction has prepared and a commit outcome decided by the TransactionManager, therefore eventually (barring heuristic damage) the Resource Managers will receive their commit message and complete. However, there are no guarantees as to how long that might take - could be days, no timeouts apply, having voted "commit" in the Prepare the Resource Manager must wait to hear the collective outcome.

Under these conditions, the simplest approach is to take "an understood, we're thinking" approach. Your request has been understood, but you actually don't know the outcome, and that's what you tell the user. Yes, in all sane circumstances the request will complete, but under some conditions operators could actually choose to intervene in the transaction manually (and maybe cause heuristic damage in doing so.)

To go one step further, you could start a new transaction and perform some queries to see if the data is there. Now, if you are populating a result screen you will naturally be doing such as query. The question would be what to do if the expected results are not there. So again, tell the user "your recent request is being processed, hit refresh to see if it's complete". Or retry automatically (I don't much like auto retry - prefer to educate the user that it's effectively an asynch operation.)