Hi.
I need to update a record in a table. The problem is that the record may not exist yet.
Since I'm working in a multi-threaded environment, I want to have a single command create it only if it doesn't exist yet, to prevent two different threads from creating it simultaneously.

How is it possible (using HQL or hibernate session methods) to update a record or create it if it doesn't exist yet?
If it's not possible, is there any suggested workaround (I'm working with multiple database implementations, so I'd rather not use proprietary SQL)?

Thanks,
Oz

Have you checked the documentation?

Assuming your using the session object you have a method that provides this service, e.g.,

session.saveOrUpdate(Object );

Thanks David. I did see this method but for some reason didn't think to use it because the fields I used didn't have a unique constraint, so I figured it would just create another record with the same values.
Now that I think about it, I can't remember why I wanted to avoid unique constraints, so I'll try and do it this way.
Thanks a lot,
Oz

This does not solve the problem described.
In fact, it will not even work in one single thread, when you try to saveOrUpdate() the same data twice:


and I cannot see anything in the documentation that states, that it should: But please correct me, if I missed something.

So how can this be solved?
There are some things that come to my mind, but I don't like any of them...

• transaction-isolationlevel repeadable read (or synchronized)
  If the function, that inserts/updates the record uses the correct transaction-isolationlevel, it should work.
  
  • do all databases support this synchronization level? - What if not?
    
  • this isolation-level will lock the complete table while it's used, so there may be a better alternative
    
• 2nd session with the same transaction (JDBC-connection)
  open a 2nd session that works with the same transaction. try to insert the record - if a constraint-violation occurs, update the existing record in the original session
  
  • this seems awkward
    
  • I don't know how to open a second session with the same connection in Spring
    
• simply catch the ConstraintViolationException and then update the existing record
  
  • as per defintion all hibernate exceptions are non-recoverable, as per docs, book, faq, best practices guide - so this is not really an option (http://opensource.atlassian.com/project ... tion_16030)
    
• single point to write those files
  the service methods don't write these entities, but only pass them to one specific thread, that will persist those entities in a separate session (and transaction)
  
  • 2 different transactions and all the common problems that arise
    
• aggregation task
  the service methods write these entities directly to a table A, that does not have the unique-constraint.
  an aggregation task will, select all entries of one type, write the newest to another table B that has the unique constraint and then delete the original entries
  
  • this is a lot of work
    
  • table B will not always show the most-recent data
    

any guidance/hints/comments welcome