How best to handle Constraint Violations in Spring JDBC

1
votes

What is considered the best/most correct way to handle a Constraint Violation in Spring JDBC?

As a real example. I've got a users table that contains, amongst other things, columns of url_slug and email. Both of these are UNIQUE. When creating a new record, or updating an existing record, if I try to make one record contain a duplicate value of another record in this table I want to return a sensible error back to the caller.

The only options I can think of are both flawed. I'm doing this in Postgres 10, using Spring NamedParameterJdbcTemplate in case that matters at all.

Check the data before doing the INSERT/UPDATE.

This will involve an extra query on every Insert/Update call, and has a race condition that means it might still not catch it. i.e.

  1. Thread 1 starts transaction
  2. Thread 2 starts transaction
  3. Thread 1 queries data
  4. Thread 2 queries data
  5. Thread 1 does update
  6. Thread 2 does update
  7. Thread 1 does commit
  8. Thread 2 does commit <-- Constraint Violation, even though at #4 the data was fine

Handle the DuplicateKeyException.

The problem here is that it's not thrown until the Transaction is committed, at which point it might well be unclear exactly which SQL call failed, which constraint failed, or anything else like that.

1
springpostgresqljdbcspring-jdbcjdbctemplate

1 Answers

0
votes

There is no "best" way to handle these kind of exceptions, except putting it into a try catch block and propagate back the error message to the user.

Of course in your example the problem is that you most probably don't want to use Serializable isolation level which essentially executes every transaction one-by-one, making sure that this cannot happen. Another way would be to lock the table for the entire transaction, but I wouldn't advise that either.

Simply put your transactional call into a try-catch block and handle it as you want.