How to limit my user creation method to one transaction at the time? (Container vs Transaction managed Concurrency)

Question

I am using a ContainerRequestFilter that checks if a user already exists in my DB. If not, it calls a service to create it:

@Provider
@Priority(Priorities.AUTHENTICATION)
public class AuthenticationRequestFilter implements ContainerRequestFilter {
    // [...]
    @Inject
    JsonWebToken jwt;

    @Override
    public void filter(ContainerRequestContext containerRequestContext) {
            UserAccount userAccount = userAccountService.createIfNotExistsBySubjectId(jwt.getSubject());
        }
    }
}

The service bean:

@RequestScoped
public class UserAccountService {
    // [...]
    @Transactional
    public UserAccount createIfNotExistsBySubjectId(String subjectId) {       
        UserAccount userAccount = UserAccount.find("subjectId", subjectId).withLock(LockModeType.PESSIMISTIC_WRITE).firstResult();
        if (userAccount == null) {
            log.info(MessageFormat.format("Creating new user account for subjectId {0}.", subjectId));
            UserAccount userAccount = new UserAccount(subjectId)
            userAccount.persistAndFlush();
        } 
        return userAccount;
    }
}

With this, I am encountering race conditions when I send multiple requests simultaneously. Two threads are calling my method:

... resulting in an obvious SQL error:

To solve this, I tried two approaches:

• The Quarkus Panache Documentation states that I can lock a find operation - hence the LockModeType.PESSIMISTIC_WRITE in my service.

• The Quarkus CDI Documentation states that I can enforce Container-managed Concurrency by using the annotation io.quarkus.arc.Lock. I tried to apply it on my createIfNotExistsBySubjectId, but to no avail.

What am I doing wrong? I want to ensure that the user creation method is called in a blocked manner.

Answer 1

Anytime you do a read/check/update operation against a remote system, you are going to get issues like this.

What you need to do is utilize the Database's "upsert" feature. See https://stackoverflow.com/a/31742830/2504224 on how to create a proper query in PostgreSQL