best practise for avoiding unique constraint exceptions

[some one]

Hello,

We have 2 applications who have the same instance of the database as the back end. Needless to say that both these applications simultaneously perform CRUD operations on the same tables.

Both of the applications use Spring and hibernate. However they use different versions. Application A uses Spring 2.5.6 and Hibernate 3.3.1.GA. This uses traditional hibernate mapping files. The other (Application B) uses spring 3.0.0.M3 and Hibernate 3.5.0.CR1. This uses hibernate annotations to decorate the domain layer.

Application B almost daily runs into conflicting primary keys.

java.sql.BatchUpdateException: ORA-00001: unique constraint (RESNA.XPKMX_TRX) violated

This is also not restricted to one table.

I tried using sequences but that was an un mitigated disaster. The Dba is also adding and removing rows directly using sql.

All the primary keys use

org.hibernate.id.IncrementGenerator

to determine the next available primary key.

The knee jerk solution to this would be to catch the exception and try saving it again, hoping that the primary key generator eventually gets a valid one. But i can see this process running till infinity locking up resources.

Any ideas how can i proceed.

Thanks,

s.

[F.Degenaar]

The Hibernate documentation clearly states "Do not use in a cluster" for the IncrementGenerator. Sequences (esp. with sequence-identity) work much better here. You should leave a confined space, say ids below 10,000, for your DBA. The applications shouldn't conflictbecause sequences are cluster-safe.
If that is not an option, your best bet might be to write an interceptor, that repeats the service call until no more unique constraint exception is thrown.

HTH

[some one]

I never figured that it would be a cluster.

I was under the impression for a cluster there needed to more than one instance of the db schema and all of them were synchronized, rather than more than one app hitting a single schema.

~s.