Efficient concurrent insert and unique constraint

[dcausse]

Hi,

Imagine a table Foo with :
ID PRIMARY
BAR UNIQUE

For concurrent insert on this table at SQL/JDBC level I do :

Foo foo = foodao.findByBar(bar);
if(foo == null) {
 // foobar does not exists or not committed.
 foo = new Foo(bar);
 try {
 // If a second transaction have already inserted bar
 // my insert is locked until other session commit or rollback
 // Of course this insert cannot be delayed
 foodao.save(foo);
 // If the other transaction has rollbacked my insert is successfull
 } catch(SQLException sqle) {
 if(sqle instanceof ConstraintExceptionOnBar)) {
 // If other session has committed my bar
 // then I will have to use this one and continue
 foo = foodao.findByBar(bar);
 } else {
 thow new MyException(sqle);
 }
 }
}

With EJB3 entities I can't do this...
Even if I catch EntityExistsException transaction is invalidated, I've read that transaction are all invalidated by hibernate if an exception occur. IMHO it is safe and efficient here to rely on DB locking mechanism and error handling, how can I be as efficient here with EJB3 entities?
I don't want to restart all my transaction...

Any suggestions?

Thank you.


David.

[dcausse]

I tried to find a word-around to my problem and found a deprecated way to access a JDBC connection with Session.connection(); and do my error checking there.

I've read that in the future a Worker API will be available via Session.doWork(Work w) and I could gain access to the current JDBC Connection object.

I've prepared my code to handle this but for now I still use the deprecated Session.connection().

My question is : can I rely on this? Will a catched SQLException thrown within a doWork() will rollback my current transaction?

If no, is there any design pattern to make a concurrent safe "find or create" entity with hibernate?

Thank you.