Do NHibernate's transactions slow down other ADO.NET connections?

Question

We're (slowly) moving an application (with a classic ADO.NET DAL) to NHibernate (following both "one-session-per-request pattern" and "repository pattern").

The application, right now, is in a hybrid state (I know, it's horrorful):

• some query are made by disposable DAO objects (that open a connection in their constructors and dispose it in their Dispose() method);

• some query are made by the strongly typed repositories (whose Get(), Save(), Update() and Delete() methods always start a new transaction - following this suggestion http://nhprof.com/Learn/Alerts/DoNotUseImplicitTransactions - or join an existing transaction).

... and queries made by DAO objects are very slow (two times slower than before).

We previously had a problem with database lockings (see http://stackoverflow.com/questions/3621271/isnt-nhibernates-one-session-per-request-pattern-a-bit-dangerous-for-long-web ), and we solved it opening multiple transactions only when needed, and closing them as soon as possible (not only at the end of the current web request). So, right now, our NHIbernate implementation follows the "one-session-per-request" pattern, but with "multiple-transactions-per-request".

But the speed problem remains. The only way to achieve the same speed as before is to disable NHibernate transactions completely.

What do you think could be the cause? The NHibernate transaction not shared with old ADO.NET connections? What should we do?

Answer 1

Yes transactions (that last for some time) can lock resources on the database. And therefore slow down other operations.

Your transactions should be the smallest possible.

If not possible, change your isolation mode. http://en.wikipedia.org/wiki/Isolation_(database_systems)