2

I am trying to use NHibernate in combination with .NET's TransactionScope object. So far I have successfully used Oracle 11g and SQL Server 2008R2 with no issues. However, SQL Compact seems to fall on its face.

using (var scope = new TransactionScope(TranactionScopeOption.Required))
{
    using (var session = _sessionFactory.OpenSession())
    {
        // The line below throws.  I also tried passing in System.Data.IsolationLevel.ReadCommitted to no avail
        using (var txn = session.BeginTransaction())
        {
            // Perform insert
            txn.Commit();
        }
    }
    scope.Complete();
}

This results in the following exception. I understand what this means, but I don't understand why it is attempting to create a nested transaction.

NHibernate.TransactionException: Begin failed with SQL exception ---> System.InvalidOperationException: SqlCeConnection does not support nested transactions.
   at System.Data.SqlServerCe.SqlCeConnection.BeginTransaction(IsolationLevel isolationLevel)
   at System.Data.SqlServerCe.SqlCeConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(IsolationLevel isolationLevel)
   at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)
       at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)
Travis
  • 2,654
  • 4
  • 26
  • 46

2 Answers2

0

I think the answer lies in this question: The connection object can not be enlisted in transaction scope

"At a guess, TransactionScope needs to escalate to a distributed or nested transaction, neither of which is supported by CE."

Community
  • 1
  • 1
user764754
  • 3,865
  • 2
  • 39
  • 55
  • I understand that; it is what is happening. What I don't understand is _why_ is needs to escalate. Clearly there is a single connection, and as I said above, both TransactionScope and BeginTransaction should enlist in an ambient transaction (that's how it works with both SQL Server and Oracle). – Travis Jan 30 '14 at 17:02
  • When I use OpenSession() inside a running ISession it would seem to me the inner session uses a different connection (tried it with and without TransactionScope). I think DTC is required if there is more than one open connection. Could it be that it works for you in SQS only because you have MS DTC enabled there? – user764754 Jan 30 '14 at 19:22
  • A `TransactionScope` object does not open a connection; it's basically just a thread local storage flag that indicates a transaction has been requested. The code above does not use OpenSession within a running ISession. No DTC is enabled in my testing. – Travis Jan 31 '14 at 20:20
0

Your code potentially produces 2 transactions:

1)new TransactionScope(TranactionScopeOption.Required)
2)session.BeginTransaction()

TransactionScope.Required "uses an ambient transaction if one already exists. Otherwise, it creates a new transaction before entering the scope. This is the default value". Your are guaranteed to have an ambient transaction by time session.BeginTransaction() gets hit, hence, making it a nested transaction.

If the transaction scope is not completed everything within its scope will be rolled back.

Newbie
  • 7,031
  • 9
  • 60
  • 85
  • 1
    My (perhaps incorrect) assumption was that session.BeginTransaction() would enlist in the ambient transaction rather than creating a new one. After all, how can a transaction be created before any connection is even opened. Searching more, I just found two interesting, and somewhat conflicting answers: http://stackoverflow.com/questions/2080253/missing-support-for-ambient-transactions-in-nhibernate - seems to indicate I shouldn't mix the two; and http://stackoverflow.com/questions/6574735/nhibernate-3-transactionscope-vs-nhibernate-transactions - seems to indicate it should just work. – Travis Nov 15 '11 at 01:51