NHiberate nested transaction blocking

Question

I have an NHibernate Transaction that does some work and makes a call to a legacy method which uses DBTransactions to call several Stored Procedures. If any of these legacy method calls fail then the NHibernate Transaction should rollback.

The problem is that the Legacy methods are using data in the database that the NHibernate transaction is creating. This is causing the Legacy code's SQL Process to lock while waiting on the Nhibernate process. The Nhibernate process is waiting on the Legacy SQL process to finish thus causing a deadlock.

I have tried using ReadUncommitted for both transactions but that doesn't work.

I tried setting up a Distributed Transaction by using System.EnterpriseServices as recommended in the NHibernate in Action but that doesn't seem to work either.

I'm not really sure where to go from here.

Answer 1

You can do one of a few things in desending order of preference.

1. If your NHibernate work comes first (in terms of execution order), you need to make your legacy methods use your NHibernate database connection. The ISession exposes a Connection property which you could pass to your legacy ADO code.

2. Or vice versa if this is easier. As long as they are sharing the same connection you will be able to execute both sets of data access in the same transaction context. The ISessionFactory.OpenSession() has an overload that takes and IDbConnection instance.

3. Start an ambient implicit transaction before you start either legacy methods, or NHibernate methods. When you have finished with both your Nhibernate and legacy code, you can commit them both at the same time by completing the ambient transaction. (I am only putting this 3rd because it will probably take you longer to implement than 1 or 2) See MSDN for how to dow this.

4. If you get really stuck and are using SQL Server you can do this, but it's really nasty. You can unite the two separate connections into the same database transaction. I can't find a link on how to do this just yet, so post back if you are desparate enough to try it!