Hibernate Sequence generation

A while ago I was working with an older version of hibernate (3.3 I think) and had to do a few thousand inserts. Whilst hibernate is not necessarily the tool for this job I was using it to do the insertion and found it spent a large amount of time fetching the primary key id one by one from the database sequence.

To get around this you can use the TableHiLoGenrator but this means that your sequence values are not what you are actually inserting into the database which means that if you wanted to use the sequence later you will need to do some stuffing around.

I had a look at implementing my own sequence generator that relied on the sequence using an increment n and then the generator is configured with this value so that it only fetches the sequence value every n times. I got this working but then we ended up not using it as we found a better way of managing the bulk insertions.

Deadlocks in oracle 11g

We ran into an issue recently where we would get deadlocks on Oracle 11g but not on 10g. After some investigation it turns out there is a change in how Oracle locks rows in 11g which means that insertions to a table with a foreign key also lock the candidate key in the referenced table!

The fix was to add indexes to the column in the referenced table so that Oracle can use this instead of locking the row.

This blog post by Richard Foote had a good explanation of the issue.