nhibernate transaction sometimes does not insert all records

Question

After doing more research and listening to advice here I have been able to get closer to figuring out my issue. The NHibernate issue that I am having deals with a transaction and it has only been replicated in production as it does not happen when we test locally. We have 2 tables Booking and Rooms for every 1 booking entry there should be 1 room entry. So if we have 802 Booking entries we should have 802 room entries. In the code the Booking gets added first and then we add the Room entry which contains a BookingID column and that is how it relates to the Booking table. About 2 days out of the week we have an issue where we find something like 400 Booking entries and 398 Room entries. We tested different scenarios and manufactured some errors and when that happens everything gets rolled back as it should and it shows in the log. We have also added logging around the transaction and can see that even when a Room entry does not get inserted the transaction is committed successfully. Is there somehow that I can get the session ID passed into the logger or tune something within the current code? The issue is happening about once for every 200 requests. I am thinking that maybe in those instances we could have different sessions or Nhibernate is not tracking the data for the 2nd insert query. Right now we dispose of the session in the Application_EndRequest not sure if maybe disposing of the session explicitly right after the transaction commits will solve anything.

// Repository
                  public class EntityRepository : IRepository
                      {
                        private readonly string connectionStringKey;
                        private NHibernate.ISession context;
            
                    protected NHibernate.ISession Context
                    {
                        get
                        {
                            return (this.context == null || !this.context.IsOpen) ?
                                (this.context = DataContextFactory.GetContextForConnection(this.connectionStringKey)) : this.context;
                        }
                    }
                    
                  public void Add<TObject>(TObject obj) where TObject : class
                    {
                        Context.Save(obj);
                    }
                  public NHibernate.ITransaction CreateTransaction()
                   {
                      return Context.BeginTransaction(); // flushmode is auto as default
                   }
            
            }
    // Main Method
    
      public Models.BookingRequestResult BookingRoom(Models.Booking booking)
        {
            ITransaction transaction = null;
            try
            {
                booking.BookingAppID = CodeGenerator.UniqueIdGenerator.Generate(10);
                transaction = repository.CreateTransaction(); 
                logger.InfoFormat("Transaction Started for: {0}", booking.BookingAppID );
                transaction.Begin(System.Data.IsolationLevel.Serializable);
        
                repository.Add(booking); // booking added
                logger.InfoFormat("Booking Added for {0}", booking.BookingAppID);
        
                var RoomAppt = new Rooms { Booking = booking , Type="Room" };
        
                repository.Add(RoomAppt); // room added and the BookingID is the ID of the newly created booking
                                           
        
                transaction.Commit();
                logger.InfoFormat("Booking: {0} has been committed", booking.BookingAppID); // This is in the logger even when the room does not get added 
        
                ...        
           } catch(exception e) {
             // any exceptions we log here and rollback transaction
            }
        }
             // Creates SessionFactory for application
           public static class DataContextFactory
            {
                private static IDictionary<string, NHibernate.ISessionFactory> sFactories = new Dictionary<string, NHibernate.ISessionFactory>();
        
                public static NHibernate.ISession GetContextForConnection(string connectionStringKey = null)
                {
                    if (string.IsNullOrEmpty(connectionStringKey)) connectionStringKey = GetCurrentContext();
        
                    var context = sContextStore[connectionStringKey];
                    if(context == null || !context.IsOpen)
                    {
                        lock (sCreateSyncRoot)
                        {
                            context = sContextStore[connectionStringKey];
                            if (context == null || !context.IsOpen)
                            {
                                if(sFactories.ContainsKey(connectionStringKey))
                                {
                                    context = sFactories[connectionStringKey].OpenSession();
                                    sContextStore[connectionStringKey] = context;
                                }
                                else
                                {
                                    throw new ArgumentException("This connection string wasn't specified in Db Context configuration.");
                                }
                            }
                        }
                    }
                    return context;
                }
    
    public static void Initialize(System.Xml.Linq.XElement configNode = null)
        {
            lock (sInitSyncRoot)
            {
                if (sContextWrappers != null)
                {
                    throw new InvalidOperationException("Context is already initialized");
                }
    
                if (configNode == null)
                {
                    var configFile =
                        System.IO.Path.Combine(
                            System.Configuration.ConfigurationManager.AppSettings[ConfigurationTokens.ConfigurationPath],
                            "DataContextFactory.conf");
                    if (System.IO.File.Exists(configFile))
                    {
                        configNode = System.Xml.Linq.XElement.Load(configFile);
                        
                    }
                }
                if (configNode == null)
                {
                    throw new NullReferenceException("No configuration node or file was provided.");
                }
    
                var attrib = configNode.Attribute("storeType");
                if (attrib == null)
                {
                    throw new NullReferenceException("Store type is undefined");
                }
                sContextStore = Utilities.ReflectionUtility.Get<IContextStore>(attrib.Value);
    
                attrib = configNode.Attribute("defaultConnectionStringKey");
                if (attrib != null)
                {
                    sDefaultConnectionString = attrib.Value;
                }
    
                foreach (var dbContext in configNode.Elements("DbContext"))
                {
                    var dbConnectionStringAttribute = dbContext.Attribute("connectionStringKey");
                    if (dbConnectionStringAttribute == null || string.IsNullOrWhiteSpace(dbConnectionStringAttribute.Value))
                    {
                        throw new ArgumentException("No connection string key specified in DataContextFactory.conf.");
                    }
                    FluentConfiguration configuration = 
                    ConfigureWrapper(dbContext, dbConnectionStringAttribute.Value);
                    configuration.Mappings(c => {
                        c.FluentMappings.Conventions.Add<EnumConvention>();
                    });
                    AddMappingAssemblies(dbContext, configuration);
                    sFactories.Add(dbConnectionStringAttribute.Value, configuration.BuildSessionFactory());
                    
                }
            }
        }
        
    
            public static void DeInitialize()
            {
                if (sContextStore != null)
                {
                    var ctxStores = sContextStore.GetAll();
                    foreach (var ctx in ctxStores)
                    {
                        if (ctx.IsOpen)
                        {
                            var objCtx = ctx.Close();
                        }
                        ctx.Dispose();
                    }
                }
            }
        
            } 
    
    // Global.asax
    
            protected void Application_EndRequest(object sender, EventArgs args)
            {
                PatientPoint.Infrastructure.Data.DataContextFactory.DeInitialize();
            } 

Answer 1

Your repository.CreateTransaction calls ISession.BeginTransaction - it opens transaction with default isolation level. Then you tries to open transaction with explicit isolation level specified:

transaction = repository.CreateTransaction(); 
logger.InfoFormat("Transaction Started for: {0}", booking.BookingAppID );
transaction.Begin(System.Data.IsolationLevel.Serializable); //<-- THIS LINE LOOKS WRONG

Most likely such multiple begin transaction calls are not properly supported. As mentioned in source:

it is intended that there be at most one uncommitted ITransaction associated with a particular ISession at a time.

You need to call ISession.BeginTransaction(isolationLevel) once and don't call ITransaction.Begin on open transaction.