nHibernate and NOLOCK

I wanted to fire a query in existing session under new transaction with read uncommented mode. This means my query would not wait for any in flight UPDATE commands  on my table.

I  had a following code in my C# class:

ISession session = GetSession();

using (var tran = session.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))

{

    IQuery query =

    session.CreateQuery(

        "SELECT Count(E.Id) FROM EditorNotification E WHERE

         E.User=:userId AND E.IsRead = 0")

        .SetParameter("userId", userId);

    object uniqueResult = query.UniqueResult();

    int count = Convert.ToInt32(uniqueResult);

    tran.Commit();

    return count;

}

I went to SQL Profiler and wanted to see whether my transaction is really started in read uncommitted mode or not. I don’t want to believe nHibernate until I see it working in SQL Profiler.

So I added "Audit Login" and few TM events in my sql profiler trace.

After executing my c# code, I noticed my Audit Login is still showing isolation level read committed, which is my default isolation level. That does not sound right. My C# code is does not have any bus, its very verbose and clear. So either SQL Profiler is laying or nHibernate is not doing what it supposed to do.

Easy to test who is at fault. In my SQL Management Studio, I opened new query on this DB and ran following command to keep the connection open on EditorNotification table so as to block any read committed isolation queries.

begin transaction

go

update EditorNotification set IsRead =1 where id = 3

go

After running C# code, I noticed that it got blocked and finally timed out waiting for a READ lock on this table. That means nHibernate is not doing what I am telling it to do.

So I found the solution in nHibernate's CreateSQLQuery method where I can use my old days SQL skills and specify directly a query with whatever query hints I want to use. Modified C# code is as follows:

ISession session = GetSession();

IQuery query =

session.CreateSQLQuery(

    string.Format("SELECT Count(E.Id) FROM EditorNotification E 

    WITH (NOLOCK) WHERE E.User_id={0} AND E.IsRead = 0", userId));

object uniqueResult = query.UniqueResult();

int count = Convert.ToInt32(uniqueResult);

return count;

CreateSQLQuery call came to rescue. If you happen to figure out why earlier nHibernate code did not work, please put it as part of comments to this blog.