How to implement atomic "save if not exists" logic by using Spring data JPA crud methods?

Question

I have users registration controller in my Spring Boot project with spring-boot-starter-data-jpa and spring-boot-starter-web dependencies, which implements the following logic, where usersRepository is an instance of standard CrudRepository:

@PostMapping
public String processRegistrationForm(@Valid @ModelAttribute("registrationForm") UserForm form,
                                      Errors errors, Model model) {
    if (!errors.hasErrors()) {
        UserEntity user = usersRepository.findByUsername(form.getUsername());
        if (user != null) {
            errors.rejectValue("username", "registration.username.not.unique");
        } else {
            usersRepository.save(form.toUserEntity(passwordEncoder));
            model.addAttribute("isRegistrationComplete", true);
        }
    }
    return "registration";
}

The method first checks whether the user with the given username exists, and if not - saves it into the database. The problem here is that this check-then-act behavior may result in DataIntegrityViolationException (with the underlying unique username constraint violation) if someone intervenes in between findByUsername() and save() calls and manages to save the user with the same username into the database. How can I avoid this? And would making the whole method @Transactional solve this problem?

The answer surely depends on 1/ your application's deployment scheme (e.g. multiple instances up at the same time) and 2/ your datastore (ACID capability) - JPA usually implies this but not always ? Could you expand on that ? — GPI
– GPI, Commented Sep 7, 2021 at 13:40
@GPI, the application is going to use PostgreSQL as underlying DBMS and only one instance of application is expected to be deployed at a time. However several web-requests to the controller may happen in parallel if, say, two or more users decide to register themselves at the same time and choose the same username. — escudero380
– escudero380, Commented Sep 7, 2021 at 13:59
As far as I understand, if I want the method above to be atomic from the perspective of the database queries, I need to annotate it with @Transactional (isolation = Isolation.REPEATABLE_READ), so that the queried row in my PostgreSQL database will be blocked and all other queries to this row will have to wait until the previous transaction is finished. Is it true? — escudero380
– escudero380, Commented Sep 7, 2021 at 14:41

queeg · Accepted Answer · 2021-09-08 19:59:18Z

1

It seems you want to create an entity but not overwrite it, and in an atomic operation you cannot test for existence first.

You could, however put a unique key on your resource and then simply go for the create option. If that entity (with that specific key) already exists you should receive an exception telling you about duplicate data. Now you can still decide whether you want to error out or simply update the existing entry.

Edit: Reading the other comments: your unique key is probably the user name, and you want to error out saying that the chosen user name is already in use.

Edit2: So you mention that my suggestion is what you had implemented but you were not happy. I think you did not suffer from performance but did not like the code (parsing - see my comment) or the user behaviour.

A user just fills in a form to register and while being delayed by a captcha or some bad password pattern all of a sudden that user name is taken by someone else. Not a nice situation. You will only resolve it by acting as soon as a user tries to register with a name. Upon the first such check (and when you return the status that the user is still available) create the entity with an attribute that this is just a placeholder. While the user still fills in the registration form other users already can see the name is taken.

For all cases where a registration is not finished and thus names are blocked for nothing, have a garbage collector job that removes all placeholders after some time. So if a placeholder has not completed to a full user account within one hour, just remove that entry from the DB and another user is free to reuse the name.

edited Sep 8, 2021 at 19:59

answered Sep 7, 2021 at 17:10

queeg

9,9402 gold badges26 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

escudero380 Over a year ago

Yes, the previous version of my controller did exactly what you are saying. It simply tried to call crud save() method wrapped in try-catch block, and then reject the duplicate value only if exception was caught, so that no transaction with isolation was needed at all, which, I presume, would result in better performance. However, in that case I needed to parse exception manually (and this turned out to be an ordeal) to figure out that it was caused by SQL unique constraint violation rather than by something else. That is why I decided to try another approach.

GPI Over a year ago

I would agree trying to insert first, then dealing with exceptions is the best course of action. Performance, to me, is a non-issue (if you have a single pg DB and app surely, you are not creating 10 users / sec, plus the case of duplicate usernames is the exception and not the norm anyway). If you really want to check that the DataIntegrityException is caused by duplicate username, without parsing the exception, then in your catch, try to select by username, and then you'll know - the failure means the row is visible to the transaction.

queeg Over a year ago

You do not need to implement much parsing. Check stackoverflow.com/questions/26383624/… and postgresql.org/docs/current/errcodes-appendix.html - I bet you receive some class 23 error.

escudero380 Over a year ago

@HiranChaudhuri, not that simple. The problem is that crud methods from Spring Data JPA never throw pure SQLException, and such exceptions are deeply wrapped in DataIntegrityViolationException, so that I need to go down the stack and call getCouse() in recursion until I find SQLException. Technically, it's not the problem, but I just don't like the resulting code which is needed for a such simple action. And if I decide to switch to another DBMS, SQL-codes might change.

escudero380 Over a year ago

Though, I would agree eventually that trying to insert record first and then deal with exception would be preferable non-blocking strategy rather then blocking the database record for a transaction.

|

Collectives™ on Stack Overflow

How to implement atomic "save if not exists" logic by using Spring data JPA crud methods?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related