Java Hibernate HQL queries with nolock

Question

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

score 9 · Accepted Answer · answered Aug 17 '10 at 12:56

9

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.

answered Aug 17 '10 at 12:56

GaryF

23,950
10
60
73

Thanks, Gary. Yes, I was looking for dirty reads. Our DBA's have recommended NOLOCK for all "selects" in this project area. Problems was, part of the SQLs were hibernate. – Sarit Aug 17 '10 at 13:09
2

In the latest versions of the Hibernate, connection() seems to be removed from the API. Any ideas on how similar effect can be achived without connection object? – IK. Sep 25 '12 at 05:05
1

If you use the `@Transactional`-annotation, it can be specified as the `isolation`-property. – Tobb Apr 30 '15 at 07:19
@Tobb - To be clear, I assume you mean the Spring Transactional annotation, which is not part of Hibernate. – GaryF May 05 '15 at 20:08

score 7 · Answer 2 · answered Jan 21 '14 at 15:24

7

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);
            }
        });

answered Jan 21 '14 at 15:24

mkbrv

407
4
13

I have used this method to set read_uncommitted transaction isolation levels on certain queries but I did realize that the connection amount was increased multiple times more than the amount without it. Connection amount on our Websphere server which holds the applicaiton was 4 for one user before read_uncommitted transactions and after that it became like 80. Is that normal? Or should I look for something else and somewhere else to find the issue? – CntkCtn May 16 '17 at 09:03

score 3 · Answer 3 · answered Oct 22 '15 at 16:17

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();

Java Hibernate HQL queries with nolock

3 Answers3

Linked