Menu
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
335
To specify a lock on a custom query method of a Spring Data JPA repository, we can annotate the method with @Lock and specify the required lock mode type: @Lock(LockModeType. OPTIMISTIC_FORCE_INCREMENT) @Query("SELECT c FROM Customer c WHERE c.
PessimisticLockException will be thrown when we query for rows which are already locked. If the initial select query is successful, rows which meet the select query criteria are locked for the duration of a transaction. We can be sure that no other transaction will modify them.
Optimistic Locking is a mechanism which ensures that data has not changed externally within a transaction. To enable Optimistic Locking we need to use a version field annotated with @Version. This annotation is provided by JPA specification (tutorial).
PessimisticLockException indicates that obtaining a lock or converting a shared to exclusive lock fails and results in a transaction-level rollback. LockTimeoutException indicates that obtaining a lock or converting a shared lock to exclusive times out and results in a statement-level rollback.
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
JPA provides three Pessimistic locking modes: PESSIMISTIC_READ, PESSIMISTIC_FORCE_INCREMENT and PESSIMISTIC_WRITE. PESSIMISTIC_READ obtains a long-term read lock on the data to prevent the data from being updated or deleted. Other transactions may read the data during the lock, but will not be able to modify or delete the data.
Because optimistic locking (default in JPA) still locks the rows it is reading from while with (NOLOCK) does no locking at all? On my mind you can do it by using optimistic lock mode. But implementation is provider specified.
When using Pessimistic Locking, the database will try to lock the entity immediately. The underlying JPA implementation throws a LockTimeoutException when the lock cannot be obtained immediately. To avoid such exceptions, we can specify the lock timeout value.
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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
