Spring Data CrudRepository multithreads causing "save unique constraint" conflict

Question

I have a situation, where two threads are trying to check for a userId, and when it doesn't appear, they try to create it. The problem is, the userId is SQL created and by the time the 2nd thread tries to save it has already passed the "if user == null" error checking.

relevant portion of User.java:

@Column(name = "username", unique=true, length = 150)
private String username;

DemoApp.java :

            String usrName = "testuser";
            // ur is a CrudRepository from Spring Boot for Users
            User existingU = ur.findOneByUsername(usrName);  // Both Threads cannot find usrName 
            if(existingU == null){ 
                //#thread 1 is able to save the user, but thread 2 causes "unique constraint SQL error." 
                existingU = new User(usrName, "firstname", "lastname");
                ur.save(existingU); // Thread 1 succeeds here... Thread 2 fail
                lg.info("Saved new user"); // Thread 1 outputs this.
                // Thread 2 errors out on .save and crashes
            } else {
                lg.info("User found in database"); // this never happens
            }

UserRepository (for ur variable)

import org.springframework.data.repository.CrudRepository;


public interface UserRepository extends CrudRepository<User, Long> {
    User findOneByUsername(String userName);
}

QUESTION: How can I make sure that the CrudRepository findByUsername is super-up-to-date and won't go into existingU==null if-statement?? Is there some more elegant way to design this situation? Perhaps there is no solution because the threads seem to launch at the same millisecond.

Answer 1

In order to prevent such a situation, you have to ensure that only one thread at a time executes your code.

I see two ways to do that:

1. put it in a synchronized block, synchronizing on the same object. This only works, if your transaction is contained completely inside the synchronization block, and all your code runs on a single machine. The later seems not reasonable to assume in 2017 where even a "Hello World" is expected to scale to at least half a dozen machines. So...

2. make the database perform the locking. You can select data from a database in such a way that nobody can access the data until you commit your transaction. This is called SELECT FOR UPDATE. The problem in your situation that you don't have a row to update, you want to create a row. You should be able to do that when you select the complete table (or actually the columns with the unique constraint) with a SELECT FOR UPDATE.

Don't do this! The described approach will create an exclusive lock on the complete index => No inserts (that's your point), no updates, no deletes, and depending on the RDBMS there might be further constraints on what kind of operations get blocked by this. This might seriously limit scalability.

So before going down this route reconsider if you can't put your operation in a block and do a retry in case of a failure. Easy to do; no DB dependency; no SQL wizardry and assuming you only rarely actually get a conflict probably better performance and scalability.

If you actually want to do this, the link above should get you started. You put that code in a custom method implementation for your repository.