Running queries on ReadCommitted (Question)

[fairking]

NH v.5.3.8

Configuration:

var cfg = new Configuration()
                .SetProperty(Environment.ReleaseConnections, "on_close")
                .SetProperty(Environment.DefaultFlushMode, FlushMode.Commit.ToString())
                .SetProperty(Environment.Isolation, System.Data.IsolationLevel.ReadCommitted.ToString())
                .SetProperty(Environment.Dialect, typeof(SQLiteDialect).AssemblyQualifiedName)
                .SetProperty(Environment.ConnectionDriver, typeof(SQLite20Driver).AssemblyQualifiedName)
                //.SetProperty(Environment.ShowSql, "true")
                //.SetProperty(Environment.FormatSql, "true")
                .SetNamingStrategy(ImprovedNamingStrategy.Instance);

The following test fails. Instead of returning only one row, it returns two:

[Fact]
public async Task ReadCommited_Test()
{
    string contact1Id;
    string contact2Id;

    // Create two contacts
    using (var tran = Db.BeginTransaction())
    {
        var contactService = GetService<ContactService>();

        contact1Id = await contactService.CreateContact(ValidateModel(new ViewModels.ContactFormVm()
        {
            FirstName = "Jan",
            LastName = "Kowalski"
        }));

        contact2Id = await contactService.CreateContact(ValidateModel(new ViewModels.ContactFormVm()
        {
            FirstName = "John",
            LastName = "Smith"
        }));

        await tran.CommitAsync();
        Db.Clear();
    }

    // Try to soft delete the first contact and run the query to get not deleted contacts
    using (var tran = Db.BeginTransaction())
    {
        var contact1 = await Db.GetAsync<Contact>(contact1Id);

        contact1.SetDeleted(true);

        await Db.SaveOrUpdateAsync(contact1);

        // await Db.FlushAsync(); // The following line fixes the issue !!!

        var notDeletedContacts = await Db.Query<Contact>().Where(x => x.IsDeleted == false).ToListAsync();

        // Must return only one entity, but actually returns two !!! So the test fails.
        Assert.Collection(
            notDeletedContacts,
            (c) => { Assert.Equal("John", c.FirstName); Assert.False(c.IsDeleted); }
        );

        await tran.CommitAsync();
        Db.Clear();
    }
}

I am not sure what is the behaviour of the following scenario (maybe I am wrong). I can see a problem when the session always needs to be flushed after every Save which is not a good solution to me.

Any ideas guys? Is there any very smart solution or pattern to this issue? Or maybe it is a bug in NH?

Thanks for any advice.

[hazzik]

What's your default flush mode?

[fairking]

What's your default flush mode?

I described it in the configuration above. Commit.

Ok, If I change it to Auto it is working. I guess because it executes Flush before the query.
But will it be very inneficient? Is that the way how it works? I thought the query applies to the database and then if there any persistent entities it also applies to them without a need to flush (as linq, not plain sql).

My pattern is to use session.Query<>() in transactions and session.CreateSQLQuery() in read only sessions. So I quess I must be very careful with executing NH linq queries in transactions then.

[hazzik]

What's your default flush mode?

I described it in the configuration above. Commit.

This is really the answer to your question.

The queries do not do in-memory post process checks of the fetched data. It is just returns the results from the database (or a L2 cache if it is enabled for query). This would be extremely hard task to implement (and sometimes impossible) and then it would bring more inefficiencies. Consider this query:

var ids = session.Query<Contact>().Where(x => x.IsDeleted == false).Select(x => x.Id).ToList();

Look how there is no IsDeleted column in the result and so NHibernate would not be able to filter these IDs further in memory.

To avoid stale query result there are flush and cache purge mechanisms. They are quite simple.

If one uses Commit or Manual flush mode then it is user's responsibility to call Flush to ensure that the data is flushed to the DB.

Always makes sure that the flush happens before each query and on commit.

Auto is a bit more sophisticated than Always: it does basic checks to determine if flush is required for the particular query. Also, it behaves like Manual if there is no active transaction.

This is well documented behavior. Modes other than Auto considered an advance feature and should be used carefully with the full understanding of the behavior.