1 Reply Latest reply: Dec 11, 2012 5:44 PM by gdarling - oracle RSS

    TransactionScope Support without Distributed Transactions?

      I have an .NET 4.0 Web Forms app that uses EF4.1 against an Oracle 11g DB, so I am using the Oracle Data Provider for .NET (ODP.NET) that supports EF4.1 (not the newest one.)

      I am deploying the app and the ODP.NET libraries to a Win2K8 server using a process very similar to this SO post:

      There are three Oracle libraries (oramtsus.dll, oramts.dll, oramts11.dll) that I want to remove from my deployment (if possible), as I don't want distributed transactions. However, I do want to continue to use TransactionScope in my app code.

      Here are my questions about this:

      1) I did read that oramts.dll might still be necessary to support TransactionScope in my code, but are all three of these Oracle DLLs necessary for that?

      2) If I set Enlist=False in my connection string and follow this advice, is that enough to prevent my data transactions from being promoted to distributed transactions?

      3) If #2 is true, then do I need any of the three Oracle DLLs listed above?

      I checked the Oracle MTS docs for answers, but I could not find details about these specific libraries. Thanks very much for your help!
        • 1. Re: TransactionScope Support without Distributed Transactions?
          gdarling - oracle

          I haven't actually tried copy/paste deployment with system.transactions, so take this all with a grain of salt, trust nothing I say, and test thoroughly:

          1110620 and higher ODP supports promoting transactions from local to distributed, as long as the first database you connect to is 11g or higher. If you connect only to a single 11g database, you should get a local transaction. If you then open a second connection inside the transaction, it gets promoted to distributed.

          If you connect to only a single database, you get a distributed transaction right off the bat, even though only a single db/connection is involved.

          If you do end up with a distributed transaction, you need more than just the oramts* dll's. You need those dll's to help enlist in a distributed transaction, but you'll also need the Oracle MTS Recovery Service, as well as some database objects created on the database that sweep DD views for in doubt transactions in order to try to recover them by contacting the initiating client via utl_http. Otherwise you may end up with "lock held by in doubt transaction" errors if a txn goes in doubt.

          If you set enlist=false, and use system.transactions, you get no transaction at all, and get the normal default auto-commit everything behavior, if I recall correctly.

          Hope it helps,