Spring Data: How to lock a row in a transaction and make other transactions wait until it is released?

Question

I have a @Transactional method where I request a record using findById() from a Spring Data repository.

Now I want to lock this object in a way that other @Transactional methods if executed in parallel will wait until the lock is released.

What I tried: I went through the documentation of @Lock and LockModeType but I still can't figure out if it covers my case.

Another option would be select for update on a DB level, but I am not sure it is the best option.

Q: How to lock an Entity object (db row) in a spring data transaction and make other transactions wait?

Answer 1

What you want is called Pessimistic locking. Spring Data through JPA specification supports that. There are two modes of pessimistic locking available:

I think you are mixing up Transaction isolation with Locking. Through @Transactional annotation you can specify isolation which is applied to the connection, while through psirng-data @Locking you can define the locking mode. The Lock mode is not related to the transaction isolation specified on @Transactional. Effectivly if the isolation is SERIALIZEABLE you would not need locking via JPA or spring-data, because the database will take care of it. The locks will be DB controlled in this case.

When you define locking the control is in your hands. Here is a description of the pessimistic lock types.

PESSIMISTIC_WRITE - all transactions including the read only ones will be blocked until the transaction holding the lock is complete. Dirty reads are not allowed.

PESSIMISTIC_READ - when you have this mode , concurrent transactions can read the data of the locked row. All updates need to obtain PESSIMISTIC_WRITE lock.

On top of this you can specify a timeout for the locking transaction completion.