Records appear to be locked between sequential database operations (Spring Data, Hibernate)

I’m experiencing what seems to be a record lock between sequential (not concurrent) database operations, which I can’t explain.

Situation

Method saveRegistrationToken is called from a REST controller. I test the calls to the method via Postman (HTTP client); the method is not called anywhere else, this is the only operation executed.

The method execution is supposed to behave as follows:

• Take a String (registration token) and a user ID (also a string) as input
• It should first update a USERS table, setting the value of the REGISTRATION_TOKEN column to null for every row where the column has the same value as the input registration token
• It should then update the USERS table for the row with the specified user ID, setting the value of the REGISTRATION_TOKEN column to the input registration token.

Problem

• Every first execution of the method will behave as expected: sets the value of the DB column REGISTRATION_TOKEN (table USER) to null wherever it is the specified value, and then sets the registration token to the input value for the row with the input user ID. As such, the value of the registration token for the row in question is the input value at the end of the execution of the method.
• Every second execution will correctly do the first step (“void” the registration token wherever it exists) but does not update the value for the row with the specified user ID. As such, the value of the registration token for the row in question is null at the end of the execution of the method.

DefaultUserService.java

@Override
public void saveRegistrationToken(String userId, String registrationToken) {
    usersRepository.voidRegistrationToken(registrationToken);
    User u = usersRepository.findById(userId).get();
    u.setRegistrationToken(registrationToken);
    usersRepository.save(u);
}

UsersRepository.java

@Repository
public interface UsersRepository extends JpaRepository<User, String> {

    @Modifying
    @Transactional
    @Query(value = "UPDATE USERS " +
        "SET registration_token = null " +
        "WHERE registration_token = :registration_token",
        nativeQuery = true)
    void voidRegistrationToken(@Param("registration_token") String registrationToken);
}

User.java

@Entity(name = "users")
@AllArgsConstructor //lombok
@Data
@NoArgsConstructor
@ToString
@EqualsAndHashCode
public class User {
    @Id
    private String id;
    private String registrationToken;
    private String email;
}

What I’ve tried

• I initially thought it would be a flush problem: that once the registration token had been set to null everywhere, the transaction would not be flushed until after the registration token had been set again for the user ID, leading to conflicting behaviour between both DB operations. I disproved that explicitly calling usersRepository.flush(); after the first operation, and observing the same behaviour.
• I tried different propagation and isolation levels on the repository operation: @Transactional(propagation = Propagation.SUPPORTS, isolation = Isolation.READ_UNCOMMITTED), which didn’t help.
• I tried explicitly setting the flush mode on the repository operation: @QueryHints(value = { @QueryHint(name = org.hibernate.annotations.QueryHints.FLUSH_MODE, value = "ALWAYS") }) , which didn’t change anything.
• It now seems to me that the first operation “locks” the updated record, which prevents the second operation from updating it, but I don’t understand how.
• Explicitly specifying auto-commit true: spring.datasource.auto-commit=true

Dependencies: compile("org.springframework.boot:spring-boot-starter-data-jpa") effectively version 2.1.1.RELEASE

Any ideas, explanations, links to docs would be very much appreciated – I’ve tried everything I can think of.

Many thanks, Chris

UPDATE:

Another reason I think it’s some kind of flush problem.

I updated this method as follows:

@Override
public void saveRegistrationToken(String userId, String registrationToken) {
    usersRepository.voidRegistrationToken(registrationToken);

    String check = usersRepository.findById(userId).get().getRegistrationToken();

    /* breakpoint on the following line */
    User u = usersRepository.findById(userId).get();
    u.setRegistrationToken(registrationToken);
    usersRepository.save(u);
}

When stopping at the breakpoint where indicated:

• Every first (“normal”) execution, the value of the check variable is null
• Every second execution, its value is the same as the input registration token

Answer

Although I always prefer to mark the service method as @Transactional as a whole, reviewing your code, I think you defined the appropriate transaction demarcation in your methods, by explicitly define the @Transactional annotation in voidRegistrationToken, and by using the methods provided by JpaRepository, implicitly annotated in such a way.

In any case, as you indicated, as a result of performing the different operations over the User who will be assigned the registration token, you are obtaining inconsistent values.

It is a clear indication that the information of the affected User entity maintained by the EntityManager in the persistence context is being polluted somewhere across the different methods invocation.

I honestly cannot give you the exact reason about this behavior.

It may have to do with the moment in which the changes are flushed to the database, to entirely discretion of the EntityManager, but you already tried to flush the different changes manually and your transactions seems appropriate and, as a consequence, it will probably not be the cause of the problem.

Maybe it have to do with a second level cache as @Guillaume suggested, maybe with the way the @Modifying operation is implemented by Spring Data.

One think you can try is to instruct your @Modifying annotation to clear the persistence context once the operation is completed:

@Modifying(clearAutomatically = true)

This will provide you a clean state to perform the registration token update.

Please, see the relevant docs.

Please, be aware that the possible implications of the use of this solution.

The use of flush and clear in EntityManager is an anti-pattern, something that should be avoided if you can by using the appropriate transaction demarcations and component architecture.

The call of clear leads to all objects being decoupled from the EntityManager. Be aware that, depending of the use case, even modified object data will not be saved to the database – this is the main difference with flush that always will preserve the modifications performed to the entities managed in the persistence context before detach them.

In any case, due to the way your transactions are defined, probably in your use case it will work properly.