Why does my SequenceGenerator skip id values?

Question

I set the artistId in the following code:

@Id
    @SequenceGenerator(
            name = "artist_sequence",
            sequenceName = "artist_sequence",
            allocationSize = 1
    )
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "artist_sequence"
    )
    private Long artistId;

Can someone explain to me why my ArtistId sometimes skips values ​​and gets out of order? I noticed that even if I try to insert invalid data into the table (when the exception is thrown), the data will be rejected by the table entry, but my artistId will be populated invisibly. This leaves my table in the following state:

artist_id	name	Surname
1	Marshall	Mathers
3	Tupac	Shakur

As I said, my ArtistId value of 2 is being skipped because I'm trying to insert an artist that already exists, but I set it to have to be unique. So, the exception is thrown, the data is rejected, but my id with value 2 is somehow filled (or skipped). Can you guys help me, how can I solve this problem to avoid this situation?

Answer 1

Sounds like you are using an Oracle database. The ID is generated before inserting, so if the insert fails, the ID is lost. Additionally, when using server clusters, ID ranges are assigned to each server and can vary significantly between consecutive inserts. The short answer is that you can't rely on the IDs being contiguous and without missing values. This will most likely work for other databases.

Answer 2

This is almost normal behavior.

The only responsibility of a sequence generator is to generate different integer values, nothing more.