I have inheritted a project that uses NHibernate and have run into some performance issues. The code has wrapped every call to the db in a transaction whether records are updated or just selected. The CommittTransaction on all of the Selects is putting a hefty ding in performance. Is it really necessary to use a transaction if all you're doing is selecting data from the db?

I know outside of NHibernate that you wouldn't need a transaction, but wasn't sure if NHibernate needed the transaction for something.

I wouldn't think a transaction on a read-only operation makes sense either, but IANAL.

Hi,

I'm an ex-DBA and have quite a bit of experience with query performance.

I don't think that a Transaction is needed when you are purely doing
a read.

If you are doing a read with the intent to modify data I would suggest
that you perform that read within a Transaction so that the proper database locks can occur from the time your transaction begins and you issue the commit.

Keep in mind, you want to keep the transactions as small as possible, if possible to avoid holding onto a lock for an extended period of time.