Dialect/Driver - Every SELECT I perform, add with(nolock)

Question

I need to know a way to implement in my system, a Driver or Dialect which, whenever I perform a SELECT in Nhibernate, the SELECT adds the with(nolock) with it. I need that to be in C# and NHibernate, not directly in the DB !

Hope you can understand !

Thanks !

score 2 · Answer 1 · edited May 23 '17 at 12:00

2

Using the WITH(NOLOCK) hint is the same thing as using a READ UNCOMMITED transaction isolation level as discussed here: When should you use "with (nolock)".

You can specify the transaction isolation level when starting new transactions with NHibernate:

var session = SessionFactory.OpenSession();
session.BeginTransaction(IsolationLevel.ReadUncommitted);

I would not recommend this unless you really know what you are doing though. Here's some more information on the subject: Why use a READ UNCOMMITTED isolation level?.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 29 '12 at 20:43

dillenmeister

1,627
1
10
18

My problem is, I need to see the with(nolock) in my query. I mean, ANY select performed in my DB must have the with(nolock). EX. ´SELECT * from Client´. When this is performed, it must be set to ´SELECT * from Client with(nolock)´. Thanks ! – Leonardo Mar 29 '12 at 20:53
Ok. As I said, READ UNCOMMITED isolation level will have the same effect as the WITH(NOLOCK) hint. Please explain why this solution would not work for you. – dillenmeister Mar 29 '12 at 21:02
I mean. I know it works, but my client wants to see the with nolock at the end of all selects. So thats why I need to use a dialect or a driver to do so ! – Leonardo Mar 29 '12 at 21:11
Ok. Chances that someone would have made a special dialect for this seems very low since 1) there is already a way to do this (isolation level) and 2) it's not recommended anyway. My advice is that you explain this to your client instead. Otherwise you are probably left to implement this yourself. I would start looking at the [existing dialects](https://github.com/nhibernate/nhibernate-core/tree/master/src/NHibernate/Dialect). Good luck. – dillenmeister Mar 29 '12 at 21:24
I've been looking at the Dialects but I have no clue where to start, I mean, how to add the with(nolock) in my queries. Do you have any tip, a function in Dialect.cs maybe ? Thanks ! – Leonardo Mar 29 '12 at 22:51

score 2 · Accepted Answer · answered Mar 30 '12 at 12:29

It is possible to modify the sql using an Interceptor and overriding the OnPrepareStatement method, something like this:

public class AddNoLockHintsInterceptor : EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        // Modify the sql to add hints

        return sql;
    }
}

And here is a way to register the interceptor with NHibernate:

var session = SessionFactory.OpenSession(new AddNoLockHintsInterceptor());

score 1 · Answer 3 · answered Aug 22 '13 at 10:26

Hope this would help someone, I used this code to add no lock hints to the most of the queries, related to the answer dillenmeister

public class NoLockHintsInterceptor : EmptyInterceptor
    {
        public override SqlString OnPrepareStatement(SqlString sql)
        {
            // Modify the sql to add hints
            if (sql.StartsWithCaseInsensitive("select"))
            {
                var parts = new List<object>((object[]) sql.Parts);
                object fromItem = parts.FirstOrDefault(p => p.ToString().ToLower().Trim().Equals("from"));
                int fromIndex = fromItem != null ? parts.IndexOf(fromItem) : -1;
                object whereItem = parts.FirstOrDefault(p => p.ToString().ToLower().Trim().Equals("where"));
                int whereIndex = whereItem != null ? parts.IndexOf(whereItem) : parts.Count;

                if (fromIndex == -1)
                    return sql;

                parts.Insert(parts.IndexOf(fromItem) + 2, " with(nolock) ");
                for (int i = fromIndex; i < whereIndex; i++)
                {
                    if (parts[i - 1].Equals(","))
                    {
                        parts.Insert(i + 2, " with(nolock) ");
                        i += 2;
                    }
                    if (parts[i].ToString().Trim().EndsWith(" on"))
                    {
                        parts[i] = parts[i].ToString().Replace(" on", " with(nolock) on ");
                    }
                }
                sql = new SqlString(parts.ToArray());
            }
            return sql;
        }
    }

Tola Ch. · Answer 4 · 2016-09-22T17:43:51.707

There are two errors in this code:

For SQL script that has parameter This code won't work.
SqlString.Parts is not compile, I am using NHibernate 4.0.0.4000

Here is the Fix:

public class NoLockInterceptor : EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
        {
            //var log = new StringBuilder();
            //log.Append(sql.ToString());
            //log.AppendLine();

            // Modify the sql to add hints
            if (sql.StartsWithCaseInsensitive("select"))
            {
                var parts = sql.ToString().Split().ToList();
                var fromItem = parts.FirstOrDefault(p => p.Trim().Equals("from", StringComparison.OrdinalIgnoreCase));
                int fromIndex = fromItem != null ? parts.IndexOf(fromItem) : -1;
                var whereItem = parts.FirstOrDefault(p => p.Trim().Equals("where", StringComparison.OrdinalIgnoreCase));
                int whereIndex = whereItem != null ? parts.IndexOf(whereItem) : parts.Count;

                if (fromIndex == -1)
                    return sql;

                parts.Insert(parts.IndexOf(fromItem) + 3, "WITH (NOLOCK)");
                for (int i = fromIndex; i < whereIndex; i++)
                {
                    if (parts[i - 1].Equals(","))
                    {
                        parts.Insert(i + 3, "WITH (NOLOCK)");
                        i += 3;
                    }
                    if (parts[i].Trim().Equals("on", StringComparison.OrdinalIgnoreCase))
                    {
                        parts[i] = "WITH (NOLOCK) on";
                    }
                }
                // MUST use SqlString.Parse() method instead of new SqlString()
                sql = SqlString.Parse(string.Join(" ", parts));
            }

            //log.Append(sql);
            return sql;
        }
}

Dialect/Driver - Every SELECT I perform, add with(nolock)

4 Answers4

Linked