Handling unique constraint violations by Hibernate
A particular table in our system is a M:M link table between Bonds and Payments, imaginatively named BOND_PAYMENTS; and to make the Java devs’ jobs easier it has a surrogate key, BOND_PAYMENT_ID. Its structure, therefore, is basically:
BOND_PAYMENTS (BOND_PAYMENT_ID, BOND_NUMBER, PAYMENT_ID)
This is a very simple design, quite common in relational database designs. There is a Primary key constraint on BOND_PAYMENT_ID, and we’ve also added a Unique constraint on (BOND_NUMBER, PAYMENT_ID) since it makes no sense to have more than one link between a Bond and a Payment.
The application allows a user to view all the Payments linked to a particular Bond; and it allows them to create new links, and delete existing links. Once they’ve made all their desired changes on the page, they hit “Save”, and Hibernate does its magic to run the required SQL on the database. Unfortunately, this was failing with ORA-00001: unique constraint violated.
Now, the way this page works is that it compares the old set of payments for the bond with the new target set, and Hibernate works out which records need to be deleted, which need to be inserted, and leaves the rest untouched. Unfortunately, in its infinite wisdom it does the INSERTs first, then it does the DELETEs. Apparently this order can’t be changed.
This is the cause of the unique constraint violation – if the user deletes a link to a payment, then changes their mind and re-inserts a link to the same payment, Hibernate quite happily tries to insert it then delete it. Since these inserts/deletes are running as separate SQL statements, Oracle validates the constraint immediately on the first insert.
We had only a few options:
- Make the constraint deferrable
- Remove the unique constraint
Option 2 was not very palatable, because the constraint provides excellent protection from nasty application bugs that might allow inconsistent data to be saved. We went with option 1.
ALTER TABLE bond_payments ADD CONSTRAINT bond_payment_uk UNIQUE (bond_number, payment_id) DEFERRABLE INITIALLY DEFERRED;
This solved it – and no changes required to the application. If a bug in the application were to cause it to try to insert a duplicate row, it will fail with ORA-02091 (transaction rolled back) and ORA-00001 (unique constraint violated) when the session COMMITs.
The only downside is that the index created to police this constraint is now a non-unique index, so may be somewhat less efficient for queries. We decided this is not as great a detriment for this particular case.
If you know of any other options that we should have considered, let me know 🙂
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
gary
11 March 2011 - 12:51 pm
I’ll get it out of my system first, but I hate that ‘surrogate key for the sake of it’.
The warnings I’d have about the deferrable constraints are
1. Make sure the application ‘understands’ the exception on commit. Commits tend not to raise exceptions, so they may not be tested for.
2. There are known bugs with deferrable constraints.
http://www.pythian.com/news/9881/deferrable-constraints-in-oracle-11gr2-may-lead-to-logically-corrupted-data/
3. You do introduce, through briefly, a situation where there are two rows in the table for a given BOND_NUMBER and PAYMENT_ID. It should only be visible for that session, but if anything does join to it expecting it to be unique then they might get an odd surprise.
I don’t have any suggestions or solutions (other than not to use Hibernate), but in your position I would throw it on StackOverflow for the weekend.
Jeffrey Kemp
11 March 2011 - 12:58 pm
Oooh, thanks for that Gary! I checked and on our 11.2.0.1.0 instance the same bug still exists. Pretty ugly. I don’t think it will affect us though because we’re only doing inserts and deletes, no updates; and the chances of two sessions hitting the same rows at the same time is extremely low.
At least having the constraint is a bit better than having no constraint 🙂
I’ll raise the exception-handling issue with the developers and hopefully it gets tested. They already know about the visibility issue, but the way they’re using Hibernate means that the commit always occurs immediately after the delete so the extra rows should never be visible.
Jeffrey Kemp
11 March 2011 - 1:29 pm
Oh, regarding the surrogate key thing (I knew I should have removed it from the post, it wasn’t really relevant) – I simplified the table design quite a bit – they added the surrogate key because the unique constraint is actually over three columns, and it was making a fair amount of code more complicated than they were comfortable with. We haven’t used surrogate keys everywhere, thankfully 🙂
gary
11 March 2011 - 1:13 pm
Found the other issue I was looking for, and it was for deferred foreign key constraints, so shouldn’t have an impact.
http://antognini.ch/2010/01/join-elimination/
Jeffrey Kemp
11 March 2011 - 1:35 pm
I’ve added a link to an interesting discussion about Hibernate’s insert-then-delete behaviour: http://forum.hibernate.org/viewtopic.php?t=934483
Islam
26 March 2011 - 9:48 pm
Suppose that the user entered 10 payments and 2 of them only are duplicated, then the 10 payments are rolled back, which will make the user complain where are my work (about the correct 8 payments)
Jeffrey Kemp
26 March 2011 - 10:32 pm
Well, the nature of an atomic transaction means that we always want the user’s work to either all be committed successfully, or none. If we commit the 8 payment links but miss 2 of them, you would introduce a data integrity issue.