Logic: Database or Application/2 (constraints check)

Question

This is a specific version of this question.
I want to check if I am inserting a duplicate row. Should I check it programmatically in my application layer:

if (exists(obj))
{
    throw new DuplicateObjectException();
}
HibernateSessionFactory.getSession().save(obj);

or should I catch the exception thrown by the database layer and triggered when I violate the contraint?

try
{
    HibernateSessionFactory.getSession().save(obj);
}
catch(ConstraintViolationException e)
{
    throw new DuplicateObjectException();
}

EDIT: In other words: though the constraint is there to remain (it's good database design anyway, and I can't be sure my app will be the only one accessing the table) shall I rely on the constraint and handle the exception its violation will raise, or I'd better check anyway?

EDIT2: Of course I do check+insert within a transaction, locking the table to ensure no other process is writing another record in the meantime

Answer 1

First, you must have a primary key or unique constraint on the database to enforce this uniqueness properly - no question.

Given that the constraint exists, which way should you code in the application? My preference would be to try the insert and catch the exceptions. Because presumably most inserts will succeed, only a few will fails as duplicates (that's what "exception" implies!): it is inefficient to perform an exists check before every insert, when the database is going to be performing its own constraint checking anyway.

Also, it is theoretically possible for the exists check to be wrong anyway - if someone else manages to commit a record with the same key value in the small interval between your exists check and your insert. Then, if you don't trap the database exception, you will believe the insert succeeded when in fact it didn't.

Answer 2

You check that the object exists solely in application code, and then once satisfied that it does not, blithely save the object. But another concurrent client might insert their own object in the moment between your two lines of code. So you'd get a Duplicate exception anyway, only this time you don't catch it.

You must do the save() and catch the exception. Otherwise you have a race condition with other concurrent clients working on the same database.