When does Spring @Transactional lock the database table row

Question

I'm using Spring data JPA and I have a method that is annotated with Spring's @Transactional annotation. In that method, I fetch entities from DB. Some entities are used for read-only purposes, while some are updated in that transaction. Let's take an example

@Transactional
public void method1(Long id) {
    var entityA = repositoryA.findById(id);
    var entityB = repositoryB.findByOtherId(id);

    entityA.setProperty1(entityB.getProperty() + 1);
}

@Transactional
public void method2(Long id) {
    var entityA = repositoryA.findById(id);
    var entityB = repositoryB.findByOtherId(id);

    entityA.setProperty2(entityB.getProperty() + 2);
}

In the above example let's say entityA and entityB correspond to tableA and tableB respectively and property1 and property2 are two of the columns of tableA. Also, I'm using SQL Server so the default isolation level is READ_COMMITTED.

I have the following questions:

After executing line var entityA = repositoryA.findById(id); how is it determined which type of lock should be acquired? In the above example, we are updating data in tableA and reading data in tableB, so are different locks acquired here?
Let's say both method1 and method2 are invoked concurrently with the same id. Will the thread that acquired lock first, block the second thread or will both execute in parallel? I understand that the isolation level is READ_COMMITTED but the DB doesn't know whether I've made any changes to rows fetched by first thread.
If I want to execute both methods in parallel for the same id what isolation level should be set? Also in both the methods I'm updating different columns of the same row, so when both the transactions commit will it be the same result as executing those transactions serially? On a side note, when Hibernate flushes the changes during commit phase, for update statements it updates all the fields of the entity, so will there be a lost update in this case?
If I were to specify readOnly=true in the Transactional annotation, will it have any effect on acquiring of locks?

"@Transactional" as itself on any isolation level doesn't enabling any locking. To achieve locking behaviour you should use "@Lock" annotation or use " for update" in your query. Detailed explanation here: https://stackoverflow.com/questions/58786195/what-is-the-relation-between-spring-transactional-and-spring-lock-annotation — Sergey Vasnev, Apr 16 '21 at 09:16
`"@Transactional" as itself on any isolation level doesn't enabling any locking` This seems to contradict from https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15 — JavaLearner, Apr 16 '21 at 10:02
Yes you are correct and I should be more precise. Depending on isolation level READ locks or shared-locks can be acquired but WRITE locks or exclusive-locks will not be created without additional actions. — Sergey Vasnev, Apr 16 '21 at 10:21

score 3 · Answer 1 · answered Apr 18 '21 at 02:46

Below are with respect to Hibernate as the JPA provider.

#1 There are no explicit locks in the code. So, the DB will exercise locks according to the transaction isolation level.

#2 When the transaction commits the row represented by entityA will be locked by the DB. If method1 wins it will block method2 from accessing the underlying row and vice versa. Again, DB is managing the locks here (so there will be no thread-level synchronization here).

#3 With READ_COMMITTED isolation you are bound to encounter lost update. To avoid lost update you'll need at least REPEATABLE_READ isolation level or control locks programmatically using pessimistic lock modes hibernate provides. If you want to stick with READ_COMMITTED (preferred for 99.99% of the use cases) and still want to thwart lost update implement optimistic locks using @Version.

#4 readonly=true turns off Hibernate's first level cache dirty checking. Nothing more.

When does Spring @Transactional lock the database table row

1 Answers1