Help with Stateless Transaction and Mismatched number of begin and commit

[Tony B]

Im running into a random error and have a theory but looking for some help from all of you nhibernate experts!!  

Refer to the following generic code:

using (var session = NHibernateSessionFactory.Instance.OpenStatelessSession())
            using (var txn = session.BeginTransaction())
            {
                var _Model = session
                    .CreateStoredProcQuery("stored_proc")
                    .SetTimeout(120)
                    .UniqueResult<Model>();
                txn.Commit();
                return _Model;
            }

According to Nhibernate documentation this will automatically rollback a transaction that fails however I am getting an error that says "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0"

The stored proc being called has a sql transaction which I have run in concurrency testing via 10 sessions with 10 executions each and cannot reproduce it at a sql level.

I am curious if in the above code, since nhibernate is using a stateless session, and calling a stored proc if a system error occurs if nhibernate is smart enough to rollback the transaction or if that is what is causing the problem...  ie nhibernate cannot rollback because it thinks there is a mismatch.  The other consideration is in the stored proc if I also include SET XACT_ABORT ON; if this might also be the reason as it is currently set to off.

Hoping someone can help!!!  Thanks

[Frédéric Delaporte]

NHibernate will rollback its own transaction, but has no knowledge of inner transactions directly opened by the stored procedure. It will not handle them.

If you can avoid handling transaction in the stored procedure, that would be the easiest.

Otherwise make sure to handle possible errors in the stored procedure through SQL code in order to cleanup transactions opened inside it.