Spring Data Jpa @Lock annotation with @Transactional

Question

As per java docs for @Lock annotations:

Annotation used to specify the LockModeType to be used when executing the query. It will be evaluated when using Query on a query method or if you derive the query from the method name.

As mentioned above it will be evaluated with @Query annotation or findBySomething..() method.

But as per my finding, when i put @Lock annotation with @Transactional in any method, and get record from db in that transaction method it is acquiring lock on db rows, ideally it should not(correct me if i am wrong):

I verified this with two transactions T1 and T2 as follows:

Starts T1 first, and fetch some records from db and sleep that thread and did not update them.
Now start T2 on other method having same @Lock with pessimistic write and @Transactional annotation and fetch same records and trying to update them, But when it is trying to commit those changes it waits for some time and then throw exception saying PessimsticLock timeout exception

@Transactional
@Lock(LockModeType.PESSIMISTIC_WRITE)
public boolean addInventory(Integer id){

   repo.findById(id)// #statement 1
}

also when #statement 1 is called it does not fire "select for update" query instead only fire select query.

Is it true that @Lock can be used with @Transactional and all rows which are fetched under this transaction get lock ?

Using SpringBoot version 2.1.4.RELEASE

SBhogal · Accepted Answer · 2021-11-26 06:05:48Z

I was able to figure out the behavior which I was facing:

@Lock annotation has not effect, if it put over method having @Transactional annotation only , it is working with @Query or findByXyz() method.

In my case, T2 transaction not able to commit because of database which is MySQL and have default transaction isolation level 'Repeatable Read' which do not allow to proceed T2 transaction until T1 commits.

@Lock(LockModeType.PESSIMISTIC_WRITE) //will not have any effect
@Transactional
public Response handleRequest(){
  //no lock will acquire
// prior to know about mysql's repeatble read, i was having impression, any call to db in this method will lock the db rows, which is wrong
}


@Lock(LockModeType.PESSIMISTIC_WRITE)// will fire select for update
@Transactional
public findByUserId(){

// any db call from repo layer which have no lock annotations 

}
@Lock(LockModeType.PESSIMISTIC_WRITE)// will fire select for update
@Query("select u from user as u where u.id=:id")
@Transactional
public getByUserID(String id){

}

Collectives™ on Stack Overflow

Spring Data Jpa @Lock annotation with @Transactional

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related