Timeout SqlException when using IsolationLevel.ReadUncommitted

Question

I'm trying to write an integration test that tests my FluentNHibernate mapping configuration. The basic idea of the test is to insert some data, then read it back, and ensure the inserted data matches the data read back. I don't want to add test data into the database on every test run, so I attempt to insert the data using a transaction, and while that transaction is still open, I attempt to read the uncommitted data back (using a separate session). The plan is that once the assertions have been made, I will rollback the transaction around the inserting of data, leaving my database in the same state that it was in before the test ran. Unfortunately though, I'm getting the following error at the point of trying to read the data:

NHibernate.Exceptions.GenericADOException : could not load an entity: [CQRSTutorial.DAL.EventDescriptor#51ff4d15-ddbc-4157-8652-a7a10177a6e3][SQL: SELECT eventdescr0_.Id as Id1_2_0_, eventdescr0_.EventType as EventT2_2_0_, eventdescr0_.Data as Data3_2_0_ FROM Events eventdescr0_ WHERE eventdescr0_.Id=?]  ----> System.Data.SqlClient.SqlException : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I've created a test specifically to highlight this issue, as follows:

[TestFixture, Category(TestConstants.Integration)]
public class TimeoutExpiredTest
{
    private ISession _writeSession;

    public static ISessionFactory CreateSessionFactory(IsolationLevel isolationLevel = IsolationLevel.Unspecified)
    {
        var msSqlConfiguration = MsSqlConfiguration
            .MsSql2012
            .IsolationLevel(isolationLevel)
            .ConnectionString(x => x.FromConnectionStringWithKey("CQRSTutorial"));

        var cfg = new CustomAutomappingConfiguration();
        return Fluently
            .Configure()
            .Database(msSqlConfiguration)
            .Mappings(m =>
            {
                m.AutoMappings.Add(
                    AutoMap.AssemblyOf<EventDescriptor>(cfg)
                        .UseOverridesFromAssemblyOf<EventDescriptorMapping>());
            })
            .BuildSessionFactory();
    }

    [SetUp]
    public void SetUp()
    {
        _writeSession = CreateSessionFactory().OpenSession();
        _writeSession.BeginTransaction();
    }

    [Test]
    public void InsertAndReadTabOpened()
    {
        var objectToStoreInDbTemporarily = GetObjectToStoreInDbTemporarily();

        _writeSession.SaveOrUpdate(objectToStoreInDbTemporarily);
        _writeSession.Flush(); // the data needs to go to the database (even if only temporarily), rather than just staying in an NHibernate cache.

        var readSession = CreateSessionFactory(IsolationLevel.ReadUncommitted).OpenSession();
        var retrievedEventDescriptor = readSession.Get<EventDescriptor>(objectToStoreInDbTemporarily.Id); // this line throws the exception after 30 seconds.

        // If previous line worked, I could then assert inserted values match retrieved values at this point.
    }

    [TearDown]
    public void TearDown()
    {
        _writeSession.Transaction?.Rollback();
    }

    public class CustomAutomappingConfiguration : DefaultAutomappingConfiguration
    {
        public override bool ShouldMap(Type type)
        {
            return type.Name == typeof(EventDescriptor).Name; // we're only mapping this class for now.
        }
    }

I've left out the code that creates the object for brevity. The original test served it's purpose - it forced to me to have to correctly define the NHibernate mapping for the object in question. But the original test was inserting new rows on every test run, which feels dirty and unnecessary - hence this transaction / rollback approach.

Looking in SQL Profiler, it seems the problem is on the Read side - the Read session is using IsolationLevel.ReadCommitted rather than IsolationLevel.ReadUncommitted. Any ideas why the read-session would not use the IsolationLevel specified in the test?

Answer 1

Ah, I've just taken a second look and I'm wondering if it's because the readSession.Get() has no explicit transaction. Therefore, NH is using an implicit transaction with a read committed isolation level? Maybe wrap the readSession.Get() in an explicit transaction with read uncommitted isolation level?