nHibernate transactions and rollbacks

Introduction

The same rollback considerations that apply to normal SQL transactions also apply to nHibernate transactions.

Rollback Scenario

Suppose you have two SQL Statements – the first one tries to insert (and runs into a primary key violation). The second one tries to update the same record as the first one.

SInce the first one failed, the second one should not proceed with its update. However, if you have the two statements within a transaction, the second one will proceed even though the first one has failed. This is because, on failure of the first statement, no one ENDED the transaction.The transaction still proceeds and executes the second statement.

Here is a code example – sql1 INSERTS a new author (and runs into a constraint), sql2 tries to update the same row:

using (var transaction  = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
  
  var sql1 = "INSERT INTO [authors]([au_id], [au_lname], [au_fname])  VALUES (1, 'Gates', 'Bill');"
  session.CreateSQLQuery(sql1);
  
  var sql2 = "UPDATE authors SET    au_fname = 'anuj' WHERE au_id = 1" 
  session.CreateSQLQuery(sql2);
  
  transaction.commit();
}

The workaround

In case of an exception (caught within a try-catch), rollback the transaction.

try{
    using (var transaction  = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)) 
   {
  
    var sql1 = "INSERT INTO [authors]([au_id], [au_lname], [au_fname])  VALUES (1, 'Gates', 'Bill');"
    session.CreateSQLQuery(sql1);
  
    var sql2 = "UPDATE authors SET    au_fname = 'anuj' WHERE au_id = 1" 
    session.CreateSQLQuery(sql2);
  
    transaction.commit();
   } // close using
  } // close try
  catch(Exception e)
  {
    transaction.Rollback();
    session.Clear(); 
  }

Recommended Pattern for nHibernate Transactions

As per nHibernate’s documentation (and Hibernating Rhino’s), as long as you stick to the usings as shown below, the cleanup will automatically happen during the dispose() (for the transaction and the session). The rollbacks will occur as part of the cleanup – so there isn’t a reason for explicitly rolling back – as long as you use the pattern below.

using(var session = sessionFactory.OpenSession()) 
using(var tx = session.BeginTransaction()) 
{ 
    // execute code that uses the session 
    tx.Commit(); 
}

Summary

nHibernate transactions should ideally be surrounded by a try-catch statement – and rolled back in case of exception. In addition, the transaction should always be started with a using( statement – to ensure correct disposal.

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 1216 posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.