Duplicate entry error in MySQL/Hibernate

Question

Hi all,

I am facing the following problem and can't find a proper solution. I have a relation A with the attributes id, x (foreign key), y (sequence number) and z (content). Further there is a uniqueness constraint on x-y. That is usually I have tuples like (455, 159, 1, ...), (456, 159, 2, ...), (457, 159, 3, ...), etc. I'm using Hibernate and that is the mapping table for a certain class. Now I have the use case, where I want to insert a new object, or remove an already existing one and update the sequence numbers of the other objects. For example if I insert a new object right after 1, it should get the sequence number 2 and subsequently the object that previously was 2 should become 3, 3 should become 4, etc. Something similar should happen when deleting object 2 - the old 3 should then become 2, etc.

However I get a ConstraintViolationException when an update is triggered.

SEVERE: Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update ... Caused by: java.sql.BatchUpdateException: Duplicate entry '159-2' for key 2

I can't quite explain why this happens. There is no commit/session flush, so I though Hibernate/MySQL will be able to handle the temporal inconsistency and will check the constraints on commit. Is there anything I'm missing or is there really no way of doing this without major workarounds?

Cheers

Answer 1

Some id generators need to go straight to the DB to get an id.

For instance using the native generator with an AUTOINCREMENT column on MySQL when you call save() it will perform the insert and retrieve the id, even if you have not committed the session.

Try moving to a Hibernate-managed generator.