I have the artistId set 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 is out of order? What I noticed is that even if I try to insert data into the table that is invalid (when an exception is thrown), that data is also rejected by the table entry, but my artistId will be filled invisibly. This brings me to the following state in my table:

As I said, my artistId with value 2 is skipped because I tried to insert the artist that already exists, but I set up that it must be unique. So, the exception is thrown, data is rejected, but my id with value 2 is filled somehow(or it is skipped). Can you guys help me, how can I solve this problem to avoid this?

CodePudding user response：

Sounds like you are using an Oracle database. IDs are generated before the insert, so they are lost if the insert fails. Also when using a cluster of servers ranges of IDs are dolled out to each server and can vary wildly between consecutive inserts. Short answer is you cannot rely on IDs being consecutive with no missing values. This could well apply to other databases.

CodePudding user response：

This is pretty much normal behavior.

The sole responsibility of the sequence generator is to produce distinct integer values, nothing more.

CodePudding user response：

This is normal db behaviour. Unique id is generated before each insert and incase transaction fails or any other error happens, Id is simply discarded. This is done to not make Id generation process complex.

In short, only purpose of sequence generator is to generate unique Ids, So you should not worry about this