2

I have simple hibernate query with nolock used in query. I am getting error - A recognition error occurred and hibernate error is Exception thrown: 'NHibernate.Hql.Ast.ANTLR.QuerySyntaxException' in NHibernate.dll. it works without nolock. I don't want to use 

<property name="connection.isolation">ReadUncommitted</property>

becasue I have to apply nolock to specific table only.

below is hql query -

select d from Users d with (nolock) where d.Userid = 2

Am I missing anything?

SSD
  • 1,041
  • 3
  • 19
  • 39
  • Why do you have to apply `NOLOCK`? – Thom A Mar 11 '19 at 10:57
  • I want to read data fast no matter data is locked by another process or not. Actually I am evaluating some hql query of my project. – SSD Mar 11 '19 at 11:03
  • @SSD, I suggest snapshot isolation rather than `NOLOCK` or `READ UNCOMITTED`. Be aware that `NOLOCK` can cause rows to be skipped or duplicated in the results, not just uncommitted data. – Dan Guzman Mar 11 '19 at 11:09
  • That isn't what `NOLOCK` does though @SSD . [Bad Habits: Putting NOLOCK everywhere](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/). – Thom A Mar 11 '19 at 11:10

1 Answers1

1

HQL will not support direct with (nolock). But we can use native SQL query.

So, for example, instead of something like this (getting list of users):

var hql    = "select d from Users d with (nolock) where d.Userid = 2";
var query  = session.CreateQuery(sql);
var result = query.List<User>();

we would need to use raw sql API

var sql    = "select d.* from schema.UserTable d with (nolock) where d.user_id = 2 ";
var query  = session.CreateSQLQuery(sql);
var result = query
    .AddEntity(typeof(User))
    .List<User>();

In case, we know that by ID only one user will be returned.. we can use UniqueResult<> instead of List

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • Thanks @Radim. What if I have already complex HQL query written in code. Do I have to change code to write native sql for each and then execute. – SSD Mar 11 '19 at 13:06
  • The point with HQL is... it is just a query language on top of **our entities**. We are querying domain (POCO objects), *not building SQL*.. no direct access to underlying DB engine features. We can add some hints to any query with some .. super special magic... eg. introducing Interceptor and overriding `SqlString OnPrepareStatement`... but in your case... I would prefer raw SQL instead of HQL... because it is more straightforward. And also, we are doing very RAW SQL here.. we do use DB engine hints... i.e. raw sql... – Radim Köhler Mar 11 '19 at 13:14