Hibernate throws unique constraint violation exception while updating field part of unique key

Question

Below is the use case: I have a unique index defined on 3 columns say A,B,C. Assume the values in them are A1,B1,C1. My java code is adding a new record say A1,B1,C1 but before this record is added, i update the previous value from C1 to C2. While trying to add the new record (after the update), hibernate is throwing an unique constraint violation exception. Any reason as to why it does? All the above statements are executed within the same transaction. My assumption is the insert happens before the update and hence the reason for the exception.

Any thoughts/suggestions ?

Microsurgery

Answer 1

Try to use session.flush() after the update.

Yep

can issues prop up if i m flushing within a transaction ? – Microsurgery

I never have had problems with this. I don't specifically remember how it works or what this do, but you can find an explanation on Java Persistence with Hibernate (it's where I found out the first time I tried to do this). – Yep

Answer 2

My java code is adding a new record say A1,B1,C1 but before this record is added, i update the previous value from C1 to C2. While trying to add the new record (after the update), hibernate is throwing an unique constraint violation exception. Any reason as to why it does? All the above statements are executed within the same transaction.

That's how Hibernate behaves by design, it will insert the values at save() time (A1, B1, C1) and then update them (C1 to C2), it won't insert A1, B1, C2). Quoting the King in Insert and Update in same flush:

Expected Hibernate behavior (we have debated whether this is really correct or not!) is that the INSERT statement will insert exactly the data that was set when save() was called. This gives the user a bit finer grained control, especially in a context where there are triggers etc. However, if you are not careful, it could perform badly.

Suggestion: delay the save to insert (A1, B1, C2) directly.

Bradlybradman

There is a miscommunication. The query order is: begin update table set c = c2 where a=a1,b=b1,c=c1 insert into table(a1,b1,c1) commit Somewhere hibernate runs the insert before the update is my assumption. I think session.flush after the update query will solve – Microsurgery

@Microsurgery Ahhhhhh, you're not updating the "new" record, you're updating the "existing" one, got it now. In that case, use flush() indeed to "force" the update to happen. – Bradlybradman