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 nHibernate strategy in our MVC website:

• A SessionScope for the request (to track changes)
• An ActiveRecord.TransactonScope 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 ActiveRecord.TransactionScope there is no delayed flush and any contained selects are also caught up in a long-running transaction.

I'm wondering if it's because we have an old version of nHibernate (it was from trunk very near 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 have 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 are available (as a guard)
• Converted our ids to Guid from identity
  • This stopped the auto flush of insert/update outside of 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 scope.Commit is called in our Controllers
  • All INSERTs / UPDATEs occur wrapped inside a transaction as an atomic unit, with no SELECTs contained.

... But for some reason nHProf now != sqlProfiler (selects seems to happen in 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!!