none
Oracle 10g Express - support .NET TransactionScope? RRS feed

Answers

  • Hi Devvvy,

    I was experiencing similar issues trying to use TransctionScope to perform distributed transaction across WCF Services, with an Oracle 10g database server and ODP.NET 10.2.0.2.21. I contacted Oracle for technical support and received the following response, which I have paraphrased:-

    "TransactionScope has been supported for quite some time. What hasn't been supported until this recent ODP.NET 11.1.0.7.10 beta is promotable transactions. ODP.NET releases prior to this beta support transactions that are set up as distributed only or local only at design time."

    "TransactionScopes and promotable transactions are supported in ODP.NET v 11.1.0.7.10 and OracMTS v11.1.0.7.10."

    "The Oracle Database server requires 11g. (Or more accurately, the first database that joins the transactions needs to be 11g. Subsequent resource managers can be older than 11g and still be promoted.)"

    "Make sure to use ReadCommitted, not Serializable transactions."

    The Oracle Data Access Components for 11.1.0.7.10 beta can be downloaded from here:-

    http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

    HTH!

    Dave

    Thursday, April 23, 2009 8:30 AM

All replies

  • Hi Devvvy,

    I was experiencing similar issues trying to use TransctionScope to perform distributed transaction across WCF Services, with an Oracle 10g database server and ODP.NET 10.2.0.2.21. I contacted Oracle for technical support and received the following response, which I have paraphrased:-

    "TransactionScope has been supported for quite some time. What hasn't been supported until this recent ODP.NET 11.1.0.7.10 beta is promotable transactions. ODP.NET releases prior to this beta support transactions that are set up as distributed only or local only at design time."

    "TransactionScopes and promotable transactions are supported in ODP.NET v 11.1.0.7.10 and OracMTS v11.1.0.7.10."

    "The Oracle Database server requires 11g. (Or more accurately, the first database that joins the transactions needs to be 11g. Subsequent resource managers can be older than 11g and still be promoted.)"

    "Make sure to use ReadCommitted, not Serializable transactions."

    The Oracle Data Access Components for 11.1.0.7.10 beta can be downloaded from here:-

    http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

    HTH!

    Dave

    Thursday, April 23, 2009 8:30 AM
  • Thank you very much for sharing - I think I have got the versions right but let me check if it's "ReadCommitted/Serializable" . Perhaps this is it. I have got around the problem by adding special handling in many places just for Oracle (i.e. for Oracle don't do transactionScope, rather, use oConn.BeginTransaction/oTx.Commit/Rollback instead). Gosh that was painful experience.

    Will post back result next week after I test this. (tied up at the moment)

    Thanks again, appreciate it.
    Thursday, April 23, 2009 8:41 AM
  • You're welcome. I was told explicity to use ReadCommitted so it is definitely worth trying...

    Please do update me on how you get on?

    Cheers,

    Dave

    Thursday, April 23, 2009 8:49 AM
  • Sorry it took so long - been really busy...

    I already tried:

                (a) Make sure Transaction isolation level not "Serializable".
                        TransactionOptions oTxOption = new TransactionOptions();
                        oTxOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
                        oTxOption.Timeout = TimeSpan.FromMilliseconds(AppContext.DefaultDBMaxTimeoutMillisec);

                        using (oScope = new TransactionScope(TransactionScopeOption.Required, oTxOption))
                        {
                            nGeneratedId = SaveCore(ref oUser);
                            oScope.Complete();
                        }
                (b) "ODP.NET 11.1.0.7.10 or higher.  On the client, you must also use Oracle Services for MTS 11.1.0.7.10 or higher."

                    Well, last time I checked this version was still in beta. My version:
                        ODAC         11.1.0.6.21
                        MTS           11.1.0.6.0
                        ODP.NET     2.0.11.1.0.6.20
                        Database    11g Express

                        (Yes DTC Service is running)
                   
                (c) Include this in your oracle connection string:
                        PROMOTABLE TRANSACTION=LOCAL;
                     yes it did make it better - i.e. it didn't just crashed/died of un-managed oracle exception but instead:

    +        InnerException    {"There was an error promoting the transaction to a distributed transaction."}    System.Exception {System.Transactions.TransactionPromotionException}


    Perhaps it's because my machine is XP?! I don't know.

    REF:
    http://forums.oracle.com/forums/thread.jspa?messageID=3394916#3394916
    http://weblogs.asp.net/ricardoperes/archive/2009/04/07/using-transactionscope-with-odp-net.aspx
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/9496b6d3-4808-4e24-8c16-75756e23168b
    • Edited by devvvy Thursday, June 11, 2009 1:23 PM
    Thursday, June 11, 2009 12:33 PM

  • hello

    I have been trying to use TransactionScope with Oracle - and ran into problem: app just crashed with un-managed exception from within oracle provider/ODP.NET
        http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/9496b6d3-4808-4e24-8c16-75756e23168b?prof=required
       
    I am wondering, if I were to use WCF transaction, do I *HAVE* to use Transaction scope?

    For example:

                [ServiceBehavior(TransactionAutoCompleteOnSessionClose=true,
                      TransactionIsolationLevel=IsolationLevel.ReadCommitted,
                      TransactionTimeout="00:00:30")]
                public class ServiceClass : IServiceClass
                {
                   [OperationBehavior(TransactionAutoComplete=true, TransactionScopeRequired=true)]
                   bool ISomeBankService.TransferMoney
                   {
                           TransactionOptions oTxOption = new TransactionOptions();
                        oTxOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
                        oTxOption.Timeout = TimeSpan.FromMilliseconds(AppContext.DefaultDBMaxTimeoutMillisec);
                        using (oScope = new TransactionScope(TransactionScopeOption.Required, oTxOption))
                        {
                              Bank1DAO.Withdraw(1000);
                            Bank2DAO.Deposit(1000);
                        }
                   }
                }
               
    Can I just rewrite as such:

                [ServiceBehavior(TransactionAutoCompleteOnSessionClose=true,
                      TransactionIsolationLevel=IsolationLevel.ReadCommitted,
                      TransactionTimeout="00:00:30")]
                public class ServiceClass : IServiceClass
                {
                   [OperationBehavior(TransactionAutoComplete=true, TransactionScopeRequired=true)]
                   bool ISomeBankService.TransferMoney
                   {
                           IDbConnection oConn = DBUtil.GetConnection(...);
                           IDbTranaction oTx = null;
                          
                           try
                           {
                               oTx = oConn.BeginTransaction();
                              Bank1DAO.Withdraw(1000);
                            Bank2DAO.Deposit(1000);
                            oTx.Commit();
                           } catch(Exception ex)
                           {
                               oTx.Rollback();
                               ...
                           }
                          
                   }
                }
               
    Will it work?

    (This is actually not best example - usually only transaction that span user-think-time gets across WCF boundary - certainly not withdraw-deposit scenario)
    Thursday, June 11, 2009 12:55 PM
  • Hi Devvvy,

    Thanks for posting your update.

    I only managed to get promotable distributed transactions to work using ODP.NET / OraMTS 11.1.0.7.10, which according to the Oracle download site (http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html) is still in beta release. Please note that it does not work in any previous versions and I have had this confirmed by Oracle Technical Support.

    My scenario, has the added complication of flowing transactions from client to application server (middle-tier) using WCF. To get this all working, I discovered that I also needed to configure Microsoft Distributed Transaction Coordinator (MSDTC). I was running XP SP2 on the client and by default MSDTC was disabled. The instructions I followed were http://support.microsoft.com/Default.aspx?id=899191.

    Let me know if you need more specific help?

    Cheers,

    Dave


    Thursday, June 11, 2009 1:00 PM
  • Thanks Dave

    QUESTION 1:
    "Please note that it does not work in any previous versions"

    What do you mean? ODAC 11.1.0.7.10 don't sit well with 10g? I'm using 11g Express actually.

    I'm not sure fact that's it's an XP box with 11 Express has anything to do with this.

    QUESTION 2: See my other post (scroll up this page) where I tried to replace "TransactonScope" with "oTx.BeginTransaction"? Do you think it'd work with WCF "TransactionAutoComplete" attribute?

    Thursday, June 11, 2009 1:06 PM
  • "Please note that it does not work in any previous versions"

    I mean exactly that. I tried to get promotable distributed transactions working using previous versions of ODP.NET / OraMTS on the client and failed miserably. After contacting Oracle Technical Support, I learned that this functionality is only supported in version 11.1.0.7.10.

    My tests have shown that an 11.1.0.7.10 client will quite happily participle in a distributed transaction with a single Oracle 10g server. However, if you have multiple databases participating in the same distributed transaction, the first database to be accessed within the transaction must be Oracle 11g. Subsequent databases within the transaction may be Oracle 10g.

    Without actually trying it, I’m not sure if your code samples above will work. To be honest, I’m not entirely sure what behaviour you are trying to achieve. On your operation contract is the TransactionFlowOption set to Mandatory or Allowed? In other words, do you intend to always flow a transaction into your service operation? If so, do you really want to create a nested transaction inside of your service operation? If not, do you want to create a new transaction if none exists? I believe that if TransactionScopeRequired=true and the caller does not flow a transaction, the service operation will automatically create and execute within a new transaction scope. Therefore, explicitly creating the transaction may not be necessary (check documentation).

    Note that it is generally poor design to pass around the connection/transaction pair outside of you data access code. In my opinion, you would be better to pursue your first code example which uses TransactionScope. Assuming you can get this work, you need not concern yourself with transaction enlistment, propagation and flow because when configured correctly the framework will transparently handle this for you. It will also result in much cleaner code!

    HTH!

    Dave

    Thursday, June 11, 2009 2:22 PM
  • Many thanks Dave - under my DAO layer I've actually done soooo much work trying to change TransactionScope to oTx.BeginTransaction just for Oracle... I suppose my only problem now is to wrap multiple WCF calls under one transaction (wizard style form on client side)

    Yes, personally TransactionScope so much cleaner it's very unfortunate Oracle don't have a PROD version which supports this, shame.

    Thanks again, your info really help.
    Thursday, June 11, 2009 2:28 PM