Best way to prevent unique constraint violations with JPA

Question

I have an Keyword and a KeywordType as entities. There are lots of keywords of few types.

When trying to persist the second keyword of a type, the unique constraint is violated and the transaction is rolled back.

Searching SO i found several possibilies (some of them from different contexts, so I'm not sure of their validity here) - this post and this post advise catching the Exception which would be of no use to me as I end up where I started and still need to somehow persist the keyword.
Same applies to locking proposed for a different situaltion here
Custom insert statements as proposed in this and this posts wouldn't work proper I guess, since I'm using Oracle and not MySQL and woulnd like to tie the implementation to Hibernate.

A different workaround would be trying to retrieve the type first in the code generating the keywords, and set it on the keyword if found or create a new one if not.

So, what would be the best - most robust, portable (for different databases and persistence providers) and sane approach here?

Thank you.

The involved entities:

public class Keyword {

    @Id
    @GeneratedValue
    private long id;

    @Column(name = "VALUE")
    private String value;

    @ManyToOne
    @JoinColumn(name = "TYPE_ID")
    private KeywordType type;
    ...
}

and

@Entity
@Table(uniqueConstraints = {@UniqueConstraint(columnNames = { "TYPE" }) })
public class KeywordType {

    @Id
    @GeneratedValue
    private long id;

    @Column(name = "TYPE")
    private String type;
    ...
}

Answer 1

Your last solution is the right one, IMO. Search for the keyword type, and if not found, create it.

Catching the exception is not a good option because

• it's hard to know which exception to catch and make your code portable across JPA and DB engines
• The JPA engine will be in an undetermined state after such an exception, and you should always rollback in this case.

Note however that with this technique, you might still have two transactions searching for the same type in parallel, and then try to insert it in parallel. One of the transaction will rollback, but it will be much less frequent.

Answer 2

If you're using EJB 3.1 and you don't mind serializing this operation, a singleton bean using container managed concurrency can solve the problem.

@Singleton
@ConcurrencyManagement(ConcurrencyManagementType.CONTAINER)
public class KeywordTypeManager
{
    @Lock(LockType.WRITE)
    public void upsert(KeywordType keywordType)
    {
        // Only one thread can execute this at a time.
        // Your implementation here:
        // ...
    }

    @Inject
    private KeywordTypeDao keywordTypeDao;
}