Introduction

Normally, if you do NOT specify the isolation level for an nHiberante transaction, it will remain undefined. This is bad for many reasons – the primary one being that SQL Server does not know what to do with undefined and has to revert to its own default setting (READCOMMITTED). This is cumbersome – since SQL Server has to do this EVERY single time.  So, you need to figure out a way to explicitly set the isolation level to something other than undefined.

Option 1 – Globally set it – during the configuration

Setting it globally seems to be the logical choice. However, for some reason, Fluent nHibernate did not pick up the global setting. In Fluent nHibernate, there is a database property that one can try setting as follows:

var config = Fluently.Configure()

               .Database(

                   MsSqlConfiguration.MsSql2008.Raw("connection.isolation", "READCOMMITTED")

This, however did not seem to work (the database still sees the transaction as ‘undefined’).

Option 2 – In Code (Set it at the start of the transaction)

The surefire way to accomplish this is to explicitly set the isolation level at the start of the transaction : 

session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)

For e.g. – the start of your transaction may look like this:

var session = SessionManager.GetSession();

  using (var transaction  = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))

{ // LINQ query 

}

Yes – this means doing a global Search and Replace for all your BeginTransactions to BeginTransaction(IsolationLevel.ReadCommitted).

On nHibernate’s Transaction.Commit

Surround your commits with a try-catch, so you can rollback the transaction (after all, that is why you are IN a transaction in the first place – so you can rollback if something goes wrong).

   1: try

   2: { 

   3:     transaction.Commit();

   4: }

   5: catch (Exception ex)

   6: {

   7:     transaction.Rollback();

   8:     throw;

   9: }

Summary

An attempt to set the isolation level globally – via the startup configuration setting for Fluent nHibernate did not seem to work. However, setting it in code, at the start of each transaction – i.e. – at every BeginTransaction – seemed to do the trick. The isolation level correctly shows up (in hibernating rhinos profiler) as READCOMMITTED.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin