search

Home  >  Q&A  >  body text

Why does my SequenceGenerator skip id values?

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?

P粉919464207P粉919464207312 days ago418

reply all(2)I'll reply

  • P粉547420474

    P粉5474204742024-03-29 16:10:10

    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.

    reply
    0
  • P粉898049562

    P粉8980495622024-03-29 13:28:53

    This is almost normal behavior.

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

    reply
    0
  • Cancelreply