none
TransactionScope and Oracle provider RRS feed

  • Question

  • Hello.
    I'm using TransactionScope with standard .NET Oracle provider. TransactionScope always creates distributed transaction. Is there a way to make TransactionScope create only local transactions or work in the same manner as it works for SQL Server provider (creates a local transaction and propogates it to the distributed only if required)?

    Thanks in advnce.
    Monday, January 18, 2010 4:58 PM

Answers

  • > I was also thinking about implementing IPromotableSinglePhaseNotification and IEnlistmentNotification interfaces. Could you suggest something at this point?

    Considering that an Internet search for "IPromotableSinglePhaseNotification Oracle" doesn't seem to produce anything interesting, what you are asking probably falls in the "tough to impossible" category.

    TransactionScope is nice, but unfortunately it didn't work that nicely with existing drivers.  Consider that it didn't support lightweight transactions for Microsoft's own SQL Server 2000 and lightweight transactions only worked correctly under limited scenarios for SQL Server 2005.  (It got a lot better with SQL Server 2008.)  You are not alone in your frustrations -- these have happened with numerous databases platforms, including Microsoft's!  The solution always seems to involve upgrading to the latest version of the database server...

    Not what you want to hear, but now that TransactionScope is five years old, it is not unreasonable to expect drivers to provide good support for it nowadays.



    Tuesday, January 19, 2010 11:11 PM
  • Probably best to switch now since the Microsoft .NET library for Oracle has been deprecated and will not undergo any future development:

    http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 20, 2010 1:06 PM

All replies

  • This is documented to be available in Oracle ODP 11.1.0.7.20 when connected to Oracle Database 11g Release 1 or higher with Oracle Services for Microsoft Transaction Server 11.1.0.7.20 or higher.

    Please see "Promotable Transactions" in http://www.oracle.com/technology/tech/windows/odpnet/col/odp.net_11.1.0.7.20_twp.pdf for more information.

    This is not available in the provider that is shipped by Microsoft.
    Tuesday, January 19, 2010 12:55 AM
  • Thank you for reply.

    Unfortunately I can't use Oracle ODP and my Oracle version is 10g. Can you suggest any "undocumented" capabilities/work arounds?
    For instance I was thinking about creating a local transaction manually by calling:
    OracleTransaction localTx = oracleConnection.BeginTransaction();
    and making TransactionScope using it:
    TransactionScope tx = new TransactionScope(localTx);
    but OracleTransaction doesn't inherit the System.Transactions.Transaction class and I can't pass OracleTransaction object to TransactionScope ctor.
    Is there any way of "converting" OracleTransaction to System.Transactions.Transaction? Will it work from your point?

    I was also thinking about implementing IPromotableSinglePhaseNotification and IEnlistmentNotification interfaces. Could you suggest something at this point?

    Thank you in advance.
    Tuesday, January 19, 2010 10:55 AM
  • > I was also thinking about implementing IPromotableSinglePhaseNotification and IEnlistmentNotification interfaces. Could you suggest something at this point?

    Considering that an Internet search for "IPromotableSinglePhaseNotification Oracle" doesn't seem to produce anything interesting, what you are asking probably falls in the "tough to impossible" category.

    TransactionScope is nice, but unfortunately it didn't work that nicely with existing drivers.  Consider that it didn't support lightweight transactions for Microsoft's own SQL Server 2000 and lightweight transactions only worked correctly under limited scenarios for SQL Server 2005.  (It got a lot better with SQL Server 2008.)  You are not alone in your frustrations -- these have happened with numerous databases platforms, including Microsoft's!  The solution always seems to involve upgrading to the latest version of the database server...

    Not what you want to hear, but now that TransactionScope is five years old, it is not unreasonable to expect drivers to provide good support for it nowadays.



    Tuesday, January 19, 2010 11:11 PM
  • At this point Oracle upgrade + ODP seems to be the fastest way for me.

    Thank you for fast and useful support.
    Wednesday, January 20, 2010 9:11 AM
  • Probably best to switch now since the Microsoft .NET library for Oracle has been deprecated and will not undergo any future development:

    http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 20, 2010 1:06 PM
  • Probably best to switch now since the Microsoft .NET software library for Orace has been deprecated and will not undergo any future development:

    http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    It helps me out of the problem, The link you provided is very useful.
    Friday, August 27, 2010 1:14 AM