Saturday, December 18, 2010

Lag between Commit and select with Distributed Transactions when two connections are enlisted to the transaction in Oracle with ODAC

Recently I came across a wired behavior in the application we are currently working on, to simply put a SELECT on the database doesn't return the data we committed to the same database just a few milliseconds before. The environment was .Net 3.5 connecting to a Oralce 11g database over ODAC (Oracle Data Access Components for Oracle).
For this behavior to be  visible,  the  code has to be  accessing the same  record soon after doing an update (just after several milliseconds) and the operation  has to be incorporated with a distributed transaction with another oracle database  too. May be due to the too specific  nature of the  conditions to see this issue,  I couldn't find  any resources even on the internet for several  hours. and finally  I came across some links which described an issue with Oracle which became a  feature  in later releases, which possibly be related with  the problem I'm facing.
These links were talking about a possibility of controlling the commit behavior of Oracle, it to be synchronous or asynchronous. But with ODAC, this feature didn't appear to be exposed. In fact, ODAC documentation provided two methods Commit - which is SAID TO BE synchronous, and also a BeginCommit method for asynchronous usage. It was very disappointing to not find much documentation on this as much as I would have liked.
In the end, the only viable solution for us was to implement a retry after a delay behavior in our code, to retry the operation after about 10 milliseconds, to recover from the problem which occurs very rarely.
Here's the StackOverflow question I asked on this.


Post a Comment