Spring Transactional with Isolation.SERIALIZABLE does not lock database row for updates-CodePudding

I have a Spring Boot application with an H2 database and I have implemented a service with the annotation to use a Transaction with isolation level SERIALIZABLE. The plan is to lock the dataset in the database, so that a concurrent process cannot update the database while my service is still holding the data for update.

@Transactional(isolation = Isolation.SERIALIZABLE) public class MyService {

  public OrderResource addContract(final Long orderId, final ContractResource contractResource) {

    // fetch the contract with Spring Data JPA repository
    final Contract contract = contractRepository.findById(contractId);

    // to test the scenario manually I wait for 10 seconds. In this timespan I manually change the row in the database
    try {
      Thread.sleep(10000);
    } catch (final InterruptedException e) {
      e.printStackTrace();
    }

    updateService.addContract(contract, contractResource);

  }
}

I would now have expected the affected record to be locked in the DB (for example by a Hibernate generated statement like 'SELECT FOR UPDATE'), but this is not the case.

Do I have to pay attention to anything else in addition to the "Isolation.SERIALIZABLE" to achieve this?

CodePudding user response：

Serializable isolation level does not necessarily mean exclusive database lock. For example, PostgreSQL implements the Serializable Snapshot Isolation pattern. The database will check possible serializable anomalies before committing. The behaviour is similar to the optimistic locking approach.

If you want to select rows with exclusive lock, you should probably apply SELECT FOR UPDATE directly. If you want to stick with JPA, then you can try using Lock query annotation.