NHibernate, ActiveRecord, Transaction database locks and when Commits are flushed

Question

This is a common question, but the explanations found so far and observed behaviour are some way apart.

We have want the following nHbernate strategy in our MVC website:

• A SessionScope for the request (to track changes)
• A ActiveRecord.TransacitonScope to wrap our inserts only (to enable rollback/commit of batch)
• Selects to be outside a Transaction (to reduce extent of locks)
• Delayed Flush of inserts (so that our insert/updates occur as a UoW at end of session)

Now currently we:

• Don't get the implied transaction from the SessionScope (with FlushAction Auto or Never)
• If we use the ActiveRecord.TransacationScope there is no delayed flush & any contained selects are also caught up in a long-running transaction.

I'm wondering if we've an old version of nHibernate (it was from trunk very near to 2.0).

We just can't get the expected nHibernate behaviour, and performance sucks (using NHProf and SqlProfiler to monitor db locks).

Answer 1

Here's what we tried since:

• Written our own TransactionScope (inherits from ITransactionScope) that:
  • Opens a ActiveRecord.TransactionScope on the Commit, not in the ctor (delays transaction until needed)
  • Opens a 'SessionScope' in the ctor if none is available (as a guard)
• Converted our ids to Guid from identity
  • This stopped the auto flush of insert/update outside the Transaction (!)

Now we have the following application behaviour:

• Request from MVC
  • SELECTs needed by services are fired, all outside a transaction
  • Repository.Add calls do not hit the db until the scope.Commit in out Controllers
  • All INSERTs / UPDATEs occur wrapped in a transaction as an atomic unit, with no SELECTs contained.

... But for some reason nHProf now != sqlProfiler (selects seems to happen on the db before nHProf reports it).

NOTE Before I get flamed I realise the issues here, and know that the SELECTs aren't in the Transaction. That's the design. Some of our operations will contain the SELECTs (we now have a couple of our own TransactionScope implementations) in serialised transactions. The vast majority of our code does not need up-to-the-minute live data, and we have serialised workloads with individual operators.

ALSO If anyone knows how to get an identity column (non-PK) refreshed post-insert without a need to manually refresh the entity, and in particular by using ActiveRecord markup (I think it's possible in nHibernate mapping files using a 'generated' attribute) please let me know!!