NHibernate TransactionScope issue with Oracle 11g

Question

The following code snippet works fine with SQL Server 2008 (SP1) but with Oracle 11g the call to session.BeginTransaction() throws an exception with the message ‘Connection is already part of a local or a distributed transaction’ (stack trace shown below). Using the '"NHibernate.Driver.OracleDataClientDriver".

Has anyone else run into this?

using (var scope = new TransactionScope())
{
   using (var session = sessionFactory.OpenSession())
   using (var transaction = session.BeginTransaction())
   {
      // do what you need to do with the session
      transaction.Commit();
    }
    scope.Complete();
}

     Exception at:    at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)
           at NHibernate.Transaction.AdoTransaction.Begin()
           at NHibernate.AdoNet.ConnectionManager.BeginTransaction()
           at NHibernate.Impl.SessionImpl.BeginTransaction()
           at MetraTech.BusinessEntity.DataAccess.Persistence.StandardRepository.SaveInstances(List`1& dataObjects) in S:\MetraTech\BusinessEntity\DataAccess\Persistence\StandardRepository.cs:line 3103

        Inner error message was: Connection is already part of a local or a distributed transaction
        Inner exception at:    at Oracle.DataAccess.Client.OracleConnection.BeginTransaction(IsolationLevel isolationLevel)
           at Oracle.DataAccess.Client.OracleConnection.BeginDbTransaction(IsolationLevel isolationLevel)
           at System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction()
           at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)

Answer 1

The problem with using only the transaction scope is outlined here: NHibernate FlushMode Auto Not Flushing Before Find

It appears nhibernate (v3.1 with oracle dialect and 11g db w/opd.net v2.112.1.2) requires it's own transactions to avoid the flushing issue but I haven't been able to get the transaction scope to work with the nhibernate transactions.

I can't seem to get it to work :( this might be a defect in nhibernate or odp.net, not sure...

found same problem here: NHibernate 3.0: TransactionScope and Auto-Flushing

FIXED: found a solution! by putting "enlist=dynamic;" into my oracle connection string, the problem was resolved. I have been able to use both the nhibernate transaction (to fix the flush issue) and the transaction scope like so:

        ISessionFactory sessionFactory = CreateSessionFactory();

        using (TransactionScope ts = new TransactionScope())
        {
            using (ISession session = sessionFactory.OpenSession())
            using (ITransaction tx = session.BeginTransaction())
            {
                //do stuff here

                tx.Commit();

            }
            ts.Complete();
        }

I checked my log files and found this: 2011-06-27 14:03:59,852 [10] DEBUG NHibernate.Impl.AbstractSessionImpl - enlisted into DTC transaction: Serializable

before any SQL was executed on the connection. I will unit test to confirm proper execution. I'm not too sure what serializable is telling me though

Answer 2

Brads answer, using an outer TransactionScope and an inner NHibernate transaction with enlist=dynamic, doesn't seem to work properly. Ok, the data gets committed.

But if you omit the scope.Complete() or raise an exception after tx.Commit() the data still gets committed (for Oracle)! However, for some reason this works for SQL-Server.

NHibernate transactions take care of auto-flush but in the end they call the underlying ADO.NET transaction. While many sources encourage the above pattern as best practice for NHibernate to solve the auto-flush issue, sources discussing native ADO.NET say the contrary: Do NOT use TransactionScope and inner transactions together, not for Oracle and not for SQL-Server. (See this question and my answer)

My conclusion: Do not combine TransactionScope and NHibernate transactions. To use TransactionScope, skip NHibernate transactions and handle the flushing manually (see also NHibernate Flush doc).