How do add NOLOCK with nHibernate?

How do you add NOLOCK when using nhibernate? (criteria query)

Solution 1:

SetLockMode(LockMode.None) or connection.isolation ReadUncomitted does NOT append a NOLOCK to your queries.

Ayende goes into the correct answer on his blog:

If you're using <sql-query> you can do the following:

<sql-query name="PeopleByName">
    <return alias="person"
                    class="Person"/>
    SELECT {person.*}
    FROM People {person} WITH(nolock)
    WHERE {person}.Name LIKE :name
</sql-query>

Note the WTIH(nolock) appended to the FROM clause.

Solution 2:

I'll explain how to do this so that you can add NOLOCK (or any other query hints), whilst still using ICriteria or HQL, and without having to stick knowledge of your queries into the mappings or session factory configuration.

I wrote this for NHibernate 2.1. There are a number of major caveats with it, mostly due to bugs in NHibernate when "use_sql_comments" is turned on (see below). I'm not sure if these bugs have been fixed in NH 3, but try it out. UPDATE: Bugs have not been fixed as of NH 3.3. The technique and workarounds I describe here still work.

Firstly, create an interceptor, like this:

[Serializable]
public class QueryHintInterceptor : EmptyInterceptor
{
    internal const string QUERY_HINT_NOLOCK_COMMENT = "queryhint-nolock: ";

    /// <summary>
    /// Gets a comment to add to a sql query to tell this interceptor to add 'OPTION (TABLE HINT(table_alias, INDEX = index_name))' to the query.
    /// </summary>
    internal static string GetQueryHintNoLock(string tableName)
    {
        return QUERY_HINT_NOLOCK_COMMENT + tableName;
    }

    public override SqlString OnPrepareStatement(SqlString sql)
    {
        if (sql.ToString().Contains(QUERY_HINT_NOLOCK_COMMENT))
        {
            sql = ApplyQueryHintNoLock(sql, sql.ToString());
        }

        return base.OnPrepareStatement(sql);
    }

    private static SqlString ApplyQueryHintNoLock(SqlString sql, string sqlString)
    {
        var indexOfTableName = sqlString.IndexOf(QUERY_HINT_NOLOCK_COMMENT) + QUERY_HINT_NOLOCK_COMMENT.Length;

        if (indexOfTableName < 0)
            throw new InvalidOperationException(
                "Query hint comment should contain name of table, like this: '/* queryhint-nolock: tableName */'");

        var indexOfTableNameEnd = sqlString.IndexOf(" ", indexOfTableName + 1);

        if (indexOfTableNameEnd < 0)
            throw new InvalidOperationException(
                "Query hint comment should contain name of table, like this: '/* queryhint-nlock: tableName */'");

        var tableName = sqlString.Substring(indexOfTableName, indexOfTableNameEnd - indexOfTableName).Trim();

        var regex = new Regex(@"{0}\s(\w+)".F(tableName));

        var aliasMatches = regex.Matches(sqlString, indexOfTableNameEnd);

        if (aliasMatches.Count == 0)
            throw new InvalidOperationException("Could not find aliases for table with name: " + tableName);

        var q = 0;
        foreach (Match aliasMatch in aliasMatches)
        {
            var alias = aliasMatch.Groups[1].Value;
            var aliasIndex = aliasMatch.Groups[1].Index + q + alias.Length;

            sql = sql.Insert(aliasIndex, " WITH (NOLOCK)");
            q += " WITH (NOLOCK)".Length;
        }
        return sql;
    }

    private static SqlString InsertOption(SqlString sql, string option)
    {
        // The original code used just "sql.Length". I found that the end of the sql string actually contains new lines and a semi colon.
        // Might need to change in future versions of NHibernate.
        var regex = new Regex(@"[^\;\s]", RegexOptions.RightToLeft);
        var insertAt = regex.Match(sql.ToString()).Index + 1;
        return sql.Insert(insertAt, option);
    }
}

Then create some nice extension methods somewhere:

public static class NHibernateQueryExtensions
{
    public static IQuery QueryHintNoLock(this IQuery query, string tableName)
    {
        return query.SetComment(QueryHintInterceptor.GetQueryHintNoLock(tableName));
    }

    public static ICriteria QueryHintNoLock(this ICriteria query, string tableName)
    {
        return query.SetComment(QueryHintInterceptor.GetQueryHintNoLock(tableName));
    }
}

Next, tell NHibernate to use your interceptor:

config.SetInterceptor(new QueryHintInterceptor());

Finally, enable the use_sql_comments property in your NHibernate configuration.

And you're done! Now you can add nolock hints like this:

var criteria = Session.CreateCriteria<Foo>()
    .QueryHintNoLock("tableFoo")
    .List<Foo>();

I based this work around the technique described here: http://www.codewrecks.com/blog/index.php/2011/07/23/use-sql-server-query-hints-with-nhibernate-hql-and-icriteria/

NHibernate Showstopping Bugs:

Firstly, there is this bug with NHibernate that you will need to fix. (You can either fix this bug by repairing the NHibernate source directly, or by doing what I did and creating your own Dialect which repairs the issue).

Secondly, there is another bug which seems to occur when you do a paged query, on any page after the first page, and you are using projections. The sql generated by NHibernate is completely wrong around the "OVER" clause. At this stage I don't know how to fix this bug but I'm working on it. UPDATE: I have detailed how to fix this bug here. Like the other bug, this one can also be fixed either by repairing the NHibernate source code or by creating your own Dialect class.