oracle-databasesequencesora-00001

oracle sequence getting messed up , and inserts failing due to that?


I am using sequence for saving domain object instance in my oracle database. I have a sequence for each table in the database. when I use the save functionality on User or Resource for example, It created a new resource on first try but the ID used was 70 ? the sequence shows the proper next number - 42 since max id in the table is 41. why was id=70 used to insert the new resource?

Also from the next try all inserts fail with this error

org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into GRARESOURCE (decomm
issioned, disabled, criticality, resourceClass, resourceGroupId, resourceName, ownerId, resourceSegmentId, resourceTypeId, riskSco
re, targetIP, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; constraint [GRA.SYS_C0012183]; nested exception is org.hibernate.e
xception.ConstraintViolationException: Could not execute JDBC batch update

Not sure whats wrong because this is happening after code restructuring ...where we moved code into new packages..

Edit: I found out the cause, see my response..Thanks guys


Solution

  • I realised the main cause of the problem. I had previously used the SequencePerTableOracleDialect, I got that from one of the previous posts by Burt Beckwith. The dialect creates a new sequence for each table when the application is starting up, similar to domain class converted to table. The dialect also makes sure that every tables id sequence is managed through its sequence only and a common sequence is not used for all inserts in the database (which is the default strategy) During the code restructuring I had removed the custom dialect and was using the default 10g dialect.

    Thats what was causing the problem!

    I see the next number in the tables associated sequence's next value field, that where I come to know that the next val =42 for resource_sequence which is right since max(id) in resource table is 41.

    Thanks a lot guys for the insight which in some way help me recollect the real cause! for those who need to know more about the custom dialect, its Here