Inserting an object to the db using hibernate, only if it doesn't exist

Question

Let's say I have a mapped User object with a user_id as a primary key, and a mail column with a unique constraint. I want my program to save a User object to the users table only if it doesn't exist.

I can do the following thing in my insert function:

1. begin transaction
2. query for a user with the given mail
3. if it doesn't exist, create a new user and save it using the session
4. commit or rollback if failed

There are two problems:

1. It's expensive to perform two queries. If I was using plain SQL I might use "INSERT IGNORE" syntax, but in my case I want to use hibernate save method, and also I want to hold the User object in the end. Is there a solution for it?
2. Concurrency. Is it possible that two instances of my application will run at the same time. Both will use the "addUser" function with the same "mail" parameter. The first will go through phase 2 and won't find a user with that mail. The second will also go through phase 2 without finding a user because the first instance still didn't save his user and commited. Then the first instance will save and commit the user, and afterwards the second instance will save and commit the user, which has the same mail as the first instance's mail. Of course it will fail, because of the constraint. Am I missing something?

If I am right what should I do:

1. Hope that it won't happen.
2. Lock the users table for read and write at the beginning of the transaction.
3. Add try...except to the function, that will except a unique constraint exception, and if the exception is thrown, will just select the user from the db according to its mail.
4. Other solutions...

Answer 1

You pretty much always have the potential for situations like this unless you introduce so much locking your system becomes unusable. Unless you know this is a problem, you should just concentrate on handling the unique constraint violation gracefully.

Answer 2

you should just concentrate on handling the unique constraint violation gracefully.

You can do this by catching the ConstraintViolationException that will be thrown. If you want to be more grainular, if perhaps there are multiple contraint viilations that can occur, you can do something like this:

...
} catch (ConstraintViolationException e) {
    if (e.getConstraintName() != null && e.getConstraintName().toLowerCase().equals("the_name_of_your_contraint")) {
        // do something, like add an error to be displayed on the UI
    }
}