Unable to use TransactionScope with NHibernate

[Travis]

I'm am struggling to get an application to work properly with
NHibernate and transactions and various RDMBSes.

-I need to use the lightweight transaction manager (LTM) - no
distributed transactions
-I am connecting to a single database
-I have a nested call hierarchy and use TransactionScope to demarcate
transactions
-I need to support SQL Server 2005, 2008, 2008R2, Oracle 10G, 11G, and
SQL Compact 4.0.

Does anyone have any tips or suggestions? How are you dealing with
the various and seemingly conflicting problems/workarounds with
NHibernate?

Here are a few scenarios I have tried.

Scenario 1:
Don't use NHibernate transactions at all, just use TransactionScope.

Issue:
This works great in the normal case; however this will leak a database
connection with SQL Server 2005 when a failure occurs (NH-2107 bug.
Astonished that this is marked as 'trivial.' See:
https://nhibernate.jira.com/browse/NH-2107 and
http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/)

using (var outerScope = new TransactionScope())
{
// Open session on first operation
using (var session =
NHibernateDataManager._sessionFactory.OpenSession())
{
using (var innerScope = new TransactionScope())
{
session.Query<MyObject>().FirstOrDefault();
innerScope.Complete();
}

using (var innerScope = new TransactionScope())
{
session.Query<MyObject>().FirstOrDefault();
innerScope.Complete();
}
}

outerScope.Complete();
}

Scenario 2:
Create an NHibernate transaction after opening a session.

Issue:
This will fail on SQL Compact with an error that it doesn't support
nested transactions. On other databases it will fail with an error
indicating that MSDTC is not enabled.
using (var outerScope = new TransactionScope())
{
// Open session on first operation
using (var session =
NHibernateDataManager._sessionFactory.OpenSession())
{
using (var innerScope = new TransactionScope())
{
using (var txn = session.BeginTransaction())
{
session.Query<MyObject>().FirstOrDefault();
txn.Commit();
}
innerScope.Complete();
}

using (var innerScope = new TransactionScope())
{
using (var txn = session.BeginTransaction())
{
session.Query<MyObject>().FirstOrDefault();
txn.Commit();
}
innerScope.Complete();
}
}

outerScope.Complete();
}

Scenario 3:
Create a new NHibernate session for each operation

Issue:
Fails just like scenario 2. This will fail on SQL Compact with an
error that it doesn't support nested transactions. On other databases
it will fail with an error indicating that MSDTC is not enabled.
using (var outerScope = new TransactionScope())
{
// Open session on first operation
using (var session =
NHibernateDataManager._sessionFactory.OpenSession())
{
using (var innerScope = new TransactionScope())
{
using (var txn = session.BeginTransaction())
{
session.Query<MyObject>().FirstOrDefault();
txn.Commit();
}
innerScope.Complete();
}
}
using (var session =
NHibernateDataManager._sessionFactory.OpenSession())
{
using (var innerScope = new TransactionScope())
{
using (var txn = session.BeginTransaction())
{
session.Query<MyObject>().FirstOrDefault();
txn.Commit();
}
innerScope.Complete();
}
}

outerScope.Complete();
}

[Jason Meckley]

The first problem I see is nested transactions. don't nest them. you only need one. if you are using a system transaction then instantiate the transaction before you open the session
using(var tx = new TransactionScope())
using(var session = factory.OpenSession()
{
   do work
   tx.Complete();
}

if you are using NH 3.x that's all you need. if you are using NH 2.x you still need to manage the NH transaction to prevent a memory leak when a system transaction does not complete.

However, since you don't need distributed transactions, I would drop the system transaction all together and stick with NH transaction
using(var session = factory.OpenSession())
using(var tx = session.BeginTransaction
try
{
   do work
   tx.Commit();
}
catch
{
   tx.Rollback();
   throw;
}

Again, do not nest transactions.

[Travis]

Thanks for your response!

Nested TransactionScope objects do not necessarily mean nested
transactions, just that a particular block of code should participate
in a transaction, either by creating a new ambient transaction, or
using the existing one (http://msdn.microsoft.com/en-us/library/
system.transactions.transactionscope.aspx). From what I understood,
NH 3.x supports TransactionScope. Ideally I would just use
TransactionScope, but NHibernate will leak connections to SQL Server
2005 on a rollback (http://davybrion.com/blog/2010/05/avoiding-leaking-
connections-with-nhibernate-and-transactionscope/), so I need to also
manage an NHibernate transaction.

I need to be able to manage transactions outside of the actual
database access code so that the outermost calling code actually
demarcates the transaction boundary. I am using WCF so ideally I
would just annotate my service operations with
TransactionScopeRequired and TransactionAutoComplete attributes. If
you are suggesting I forego TransactionScope altogether, I would need
to implement my own equivalent attribute that manages both an
NHibernate ISession as well as an NHibernate ITransaction. That just
seems like overkill..

[Jason Meckley]

technically I don't think it works with NH. and conceptually it doesn't make sense. the transaction boundary is just that, a boundary. nesting them doesn't make sense.

NH has supported Tx Scope since 2.1. there is a memory leak in 2.1 which you mentioned above and was resolved with 3.x but it has been supported for some time.

your tx boundaries should demark either a query (read) or a command (write) operation. keeping these separated makes managing NH, and the code in general a breeze.


@joe (who emailed me privately)
"How bad is it to create a new session and transaction within another session and transaction?
I can imagine performance will be suboptimal, but what about data consistency?"

it's not about performance. it's about the concept of what a transaction boundary is. when working with wcf, threading(service bus) or asp.net(mvc) the simplest and most flexible approach is to treat is call/thread/request as an atomic unit of work that either reads or writes. never both.
with this pattern the flow looks like this:
open a session
begin a transaction
do work
commit/rollback & dispose of transaction
dispose of session

doing the work may require multiple components, but each component is using the same session and occurs within the same transaction. webforms bends this approach, but not so much it's unusable. it would look more like this

open a session
begin a transaction
do work
commit/rollback & dispose of transaction
begin a transaction
do work
commit/rollback & dispose of transaction
begin a transaction
do work
commit/rollback & dispose of transaction
...
dispose of session

in any case there is a single session and no nested transactions, just clean simple NH session management