Java Hibernate HQL queries with nolock

Question

Is there a way to run these queries as if I added a (NOLOCK) hint to them?

Answer 1

If you really need this, then you want to do something like:

session.connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

which is identical to a nolock.

Before you do that, really think carefully if you want to do a dirty read. Most of the time people do this because it's what they've always done, rather than because it's the right thing to do. In particular, this does not work well with caching.

Actually, this thread goes into the issues a little. Read carefully before deciding.

Answer 2

In latest version of Hibernate you have to do it this way:

  Session session = (Session) em.getDelegate();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            }
        });

Answer 3

You can do the "with (nolock)" if you go native. This is extreme but if the alternative is changing the transaction isolation level, you might rather do this.

Note that this example is for MSSQL.

String sqlQueryString = "SELECT * FROM my_classes_table WITH (nolock) WHERE columnName = :columnValue";

SQLQuery sqlQuery= session.createSQLQuery(sqlQueryString).addEntity(MyClass.class);
sqlQuery.setLong("columnValue", value);
List<MyClass> out = sqlQuery.list();