Background
I am connecting to a DB2 database with the isolation level set to 4 (read stability)
I have a table with an ID generated by a sequence and a field that is declared to be unique (an id from an external system).


We have the need to get a record by this external unique key. Since we do not want 2 threads to insert a record with the same unique key we want a method that will create a basic record (with default values but with all the keys filled in) or get a record with the same information from the database.

We can't do the get 1st because if the record is not in the database, we don't create database locks. Therefore it is possible that 2 threads don't find the record and then both try to insert the record.
So we want to do the insert first and deal w/ the constraint violation. However, even though we catch the ConstraintViolationException, the insert still exists in the ActionQueue (even after a flush) so that the exception will continue to be thrown. Is there a better way to do this in hibernate?

Instead of doing it the way we are attempting, an insert/update concept would work as well.
My understanding is that saveOrUpdate() and merge() will not be useful because the unique key is not the primary key.

Any help would be greatly appreciated.

Hope I got you well.


If you received an exception, trash the session, it's unusable. This is part of the best practices of Hibernate : never try and keep on using a session when an exception has been thrown. Create a new one and then do what you need.

Another info : Hibernate never performs any uniqueness check. For example, when you put unique="true", it's only to inform SchemaUpdate that it should generate a unique key contraint in the DDL.

Thank you for the quick reply.

I'm well aware of the best practices, but it seems like this might be an exception to the "rule."

If I was to trash the session, I would need to rollback and redo everything that happened during the transaction because it is not acceptable to commit in the middle.

Perhaps there's a way to do what I need without the need for exception handling (that would be ideal of course). But it is not feasible (to my knowledge) in my scenario to do a get before an insert without changing the isolation level or locking the table because then this scenario is possible:

Thread 1: Get by external unique Key ‘123’ returns null
Thread 2: Get by external unique Key ‘123’ returns null
Thread 1: Insert external unique key ‘123’
Thread 2: Insert external unique key ‘123’ throws a constraint violation exception.

It seems like you should want to recover from exceptions without throwing away all your work. Especially when it is a valid, and somewhat expected exception.

I could of course get the connection from the session and run SQL and deal w/ SQL exceptions. This is far from desirable. I could also potentially use reflection to clear the action queue assuming I had flushed prior to this process.