On this page.... RSS 2.0 | Atom 1.0 | CDF
# Thursday, April 6, 2006

This is for anyone who is "lucky" enough to have to use Oracle as a data source in .NET and wants to use the new features in the System.Transactions namespace (which is pretty cool, by the way).  In searching around for an answer to whether or not Oracle supports System.Transactions, I found that they do, but with a cost.  You see, if you use System.Transactions with Oracle (or any non-SQL Server 2005 transaction resource manager), all transactions will be coordinated through the Microsoft Distributed Transaction Coordinator (DTC).  This involves a fair amount of overhead in terms of processing and resources and can involve headaches when truly dealing with distributed transactions.

SQL Server 2005 takes advantage of one of the nifty features of System.Transactions, which is what they call Promotable Single Phase Enlistment (PSPE).  This means that by default, when you start a transaction using, say, TransactionScope, and the first data source in the transaction is SQL Server 2005, it will start out as a standard SQL Server transaction, meaning it would be equivalent to using SqlTransaction on a single connection.  However, if you add other data sources including (I presume) other connections to SQL Server 2005, it will promote that transaction to a distributed transaction managed by the DTC.

This is pretty cool because many of us use transactional programming against a single data source, so we can take advantage of TransactionScope and the so-called ambient transaction without incurring the overhead of DTC.  But unfortunately, as of writing, there is no such support for other transaction resource managers (such as the one included with the Oracle ADO.NET client).

My recommendation would be against using System.Transactions unless you are going to be having distributed transactions most of the time.  Hopefully Oracle will implement the PSPE for their ADO.NET provider, but in the meantime, it just isn't worth the overhead.

Thursday, April 6, 2006 1:41:04 PM (Eastern Daylight Time, UTC-04:00)  #    Disclaimer  |  Comments [3]  | 
Wednesday, April 12, 2006 6:31:55 PM (Eastern Daylight Time, UTC-04:00)
It was my udnerstanding that the 10gR2 release of Oracle did support the necessary functionality.
Wednesday, April 12, 2006 8:13:40 PM (Eastern Daylight Time, UTC-04:00)
That may be, but I cannot confirm it. I neither have access to that version of Oracle, nor can I seem to find anything "official" saying so. Please advise if you do.
Wednesday, April 19, 2006 2:42:18 PM (Eastern Daylight Time, UTC-04:00)
Wally, I just asked on the forums and one of the regulars there claims that ODP.NET does not in fact support System.Transactions. See http://forums.oracle.com/forums/thread.jspa?forumID=146&threadID=380709.
Comments are closed.

The opinions expressed herein are solely my own personal opinions, founded or unfounded, rational or not, and you can quote me on that.

Thanks to the good folks at dasBlog!

Copyright © 2019 J. Ambrose Little