Generate sequence number without gaps by multiple app instances

Question

I want to write the device number generator which consists of SERIES and incremented NUMBER. When specific the SERIES reach max which is allowed to them then next SERIES will be in use starting with NUMBER 1 eg.

SERIES|MAX_NUM|CURRENT_NUM
AA|3|1
AA|3|2
AA|3|3
BB|2|1
BB|2|2
CC|5|1
....

Ihave multiple module instances which needs to generate this device number and the most important thing is that this NUMBER cannot contains gaps (what in pk db sequence is possible in case of rollback). We need ensure that there are no gaps during incrementation.

Is there any other solution than locking record by pessimistic lock in PESSIMISTIC_WRITE mode? In such case if I have 3 records in db eg:

AA|4|1
AA|4|2
AA|4|3

and I will do execute findMax() method to find the max NUMBER (in that case row AA|4|3) I suppose that this row will be locked for READ/WRITE/UPDATE. Then after incrementation the new record will be added and lock will be relesed for record AA|4|3. But what in case when during this executing findMax() other thread will try to query for max() NUMBER? It will be wait for release or the NUMBER 2 will be returned due to the locking AA|4|3?

I'm trying to understand locking mechanism. I'm working on PostgreSQL.

Thanks in advance!

Answer 1

I worked on something similar recently. This is how i've accomplished it.

1. I created a separate table in which i hold the latest value without having to use the findMax function. So in your case, everytime you add a new series you'll add a new enty in the series_counter table

series_counter
series_id | current_counter

2. whenever you're trying to fetch the latest value, you'll have something like this

@Transaction
public doBusinessLogic(Long seriesId) {
    var latestCounter = seriesCounterRepo.fetchLatest(seriesId)
    
    Series series = new Series(..);
    series.setCurrentCounter = latestCounter;
    seriesRepo.save(series)
    latestCounter.incrementValue();
}

public interface SeriesCounterRepo .. {

  @Lock(LockModeType.PESSIMISTIC_WRITE)
  @Transactional // when i didn't add this, i got some errors, but i don't remember exactly though
  public SeriesCounter fetchLatest(Long seriesId)

}


class SeriesCounter {

      private Long id;
      private Long currentCounter;


    public incrementValue() {
        currentCounter++;
    }
}

Some explanations:

1. Given it will run into a transaction if any of the line throw an exception, the changes won't be commited to the db
2. when another call comes and tries to fetch the same row, it will block until the above transaction finishes.