JPA reading data with NO LOCK

Question

In the application we're writing, it is required that we use the with(NOLOCK) in our queries. Just so that the queries don't take so long to process.

I haven't found anything on how to accomplish this. What I did find is how to enable optimistic or pessimistic locking, but as far as I know, that's for writing data, not reading.

Is there a way to do this?

We are using JPA and the Criteria API connecting to a MSSQL server and the application server is Glassfish 4.

Erates

score 5 · Answer 1 · edited Feb 23 '22 at 00:32

The with(NOLOCK) behaviour is very simmilar to working in the READ_UNCOMMITED transaction isolation level, as it is explained here. Given that, you can achieve what you want by using a DB connection that is configured in that transaction level. If you want to decide during the execution what transaction level to use, simple get the underlying connection and change the transaction isolation level (after that you should change it back to the original level).

If you use the with(NOLLOCK) feature for a different goal to avoid some bugs, then you will have to write native queries for that.

score 3 · Answer 2 · answered Dec 10 '19 at 22:58

3

The equivalent of WITH (NOLOCK) in JPA is to use READ_UNCOMMITTED isolation level.

@Transactional(isolation = Isolation.READ_UNCOMMITTED)

answered Dec 10 '19 at 22:58

Venkat

314
3
10

1

But this one not having 'WITH (NO LOCK)' in query which is not getting generated – Satish Patro Apr 16 '21 at 12:09

score 0 · Answer 3 · answered Mar 17 '15 at 11:45

0

The right solution of your task is using of optimistic locking, which enabled in main JPA providers by default. In short: you must nothing to do for reading data from the database without locking. On other hand JPA provides locking whole table row in through database row locking mechanism (typically) when pessimistic mode is enabled. For more info look at link

answered Mar 17 '15 at 11:45

Alexander Fedyukov

964
8
18

So if I understand it correct, I cannot provide the same behaviour in JPA as the `with (NOLOCK)` does in TSQL? Because optimistic locking (default in JPA) still locks the rows it is reading from while `with (NOLOCK)` does no locking at all? – Erates Mar 17 '15 at 11:52
On my mind you can do it by using optimistic lock mode. But implementation is provider specified. You can "peep" sql queries from hibernate to sql-server by setting to "true" hibernate.show_sql property and use additional DB monitor, then make sure about native DB lock or nolock. When you are working through JPA you don't worry about database specific implementation of some paradigm. – Alexander Fedyukov Mar 17 '15 at 12:15

JPA reading data with NO LOCK

3 Answers3