NHibernate TransactionScope issue with Oracle 11g

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

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).

One question, why are you doing the inner session.BeginTransaction - since 2.1 GA NHibernate will automatically enroll into TransactionScope contexts so there's no reason to do your own anymore.