Hi Thomas, > > trying to do simultaneous inserts of data that might be identical. > > Could you use the MERGE > statement?http://www.h2database.com/html/grammar.html#merge
Trying a few test cases, this might work. The current application code is using Hibernate, though, so I'm not sure how easy that will be to adjust. It'd probably have to be done in straight SQL, which I was hoping to avoid. > > The logic is generally: > > 1. Lookup for row with tuple, > > If found, return id > > You should probably do a SELECT ... FOR UPDATE here. Unfortunately it > locks the whole table, not just the selected record. My understanding of SELECT ... FOR UPDATE is that you are intending to update the rows returned. In this application's case, we want to insert only if the SELECT returns 0 rows. Perhaps I am misunderstanding the purpose of FOR UPDATE. In this application's case, we don't need to update any rows. The application looks up the row id to use for inserting additional data in related tables. If it isn't found, we add it. I can try adding the FOR UPDATE, but this will hurt performance in cases where the rows do not match under the unique constraint, because otherwise we could do all the work at the same time without locking the whole table. > > when the unique constraint violation > > happens it occasionally cannot locate the id. > > I'm not sure what you mean here... Should H2 know the id? Your code > should know the id. My main point in the original post is that after the SQLException due to the unique conflict, an identical SELECT query cannot locate the row that contains the conflicting data if the other transaction has not committed yet. I would expect to be able to SELECT to find the row after knowing that it exists. Blocking on the INSERT of conflicting data until the other transaction completes would be nice. I tried looking at H2's source to figure out how to go about doing this, but I haven't completely familiarized myself with it yet. Thanks, -Scott -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
