How to re-generate deleted sequence numbers in hibernate?

As we know the below hibernate annotation generates a new number each time from the sequence starting from 1. Consider a situation wherein i have a set of records with ids(1-5).Now a record is deleted from the table which had id as 3. If we see number 3 is missing from the sequence 1-5 now because of the operation. I have a requirement for the sequence to re-generate and reassign that number 3 when i will be adding new record in the table. How to do this ?

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

Answer

I don’t think this is a great idea. A sequence is just a number incremented of 1 each time. This allows it to be fast but already this is a bottleneck for a distributed database for writes as all the nodes need to synchronize on that number.

If you try to get the first available integer, you need basically to do a full table scan, order the records by id and check the first missing one. That’s extremely costly and inefficient for something that shall be as cheap as possible.

You should view the id as a technical ID without functional meaning and thus do not care if there are holes in the sequence or not.

Leave a Reply

Your email address will not be published. Required fields are marked *