Simple and Advanced Approaches to Order Number Generation Using Database Sequences

Today we discuss simple and practical solutions for generating order numbers in high‑concurrency scenarios, focusing on database‑based approaches.

Simple method : Use the MySQL auto_increment_increment variable to obtain IDs. Create a sequence table with columns seq_name , current_value , and _increment to support multiple business identifiers and distributed hash strategies.

CREATE TABLE `sequence` (
`seq_name` varchar(200) NOT NULL,
`current_value` bigint(20) NOT NULL,
`_increment` int(4) NOT NULL,
PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Retrieve the next ID with SELECT LAST_INSERT_ID() while updating the table:

<insert timeout="30" id="update" parameterType="Seq">
    UPDATE sequence
    SET
    current_value = LAST_INSERT_ID(current_value + _increment)
    WHERE
    seq_name = #{seqName}
<selectKey resultType="long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
</insert>

When calling this service from a transactional method, use PROPAGATION_REQUIRES_NEW to avoid ID duplication caused by uncommitted updates.

@Autowired
private SeqDao seqDao;

@Autowired
private PlatformTransactionManager transactionManager;

@Override
public long getSeq(final String seqName) throws Exception {
    TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
    transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    return (Long) transactionTemplate.execute(new TransactionCallback() {
        public Object doInTransaction(TransactionStatus status) {
            try {
                Seq seq = new Seq();
                seq.setSeqName(seqName);
                if (seqDao.update(seq) == 0) {
                    throw new RuntimeException("seq update failure.");
                }
                return seq.getId();
            } catch (Exception e) {
                throw new RuntimeException("seq update error.");
            }
        }
    });
}

More complex method : To reduce database pressure under high load, allocate a block of IDs in memory. Create a sequence_value table to store the maximum allocated ID per business name.

CREATE TABLE `sequence_value` (
`Name` varbinary(50) DEFAULT NULL,
`ID` int(11) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8

Define a Step class that holds currentValue and endValue for a memory segment.

class Step {
    private long currentValue;
    private long endValue;

    Step(long currentValue, long endValue) {
        this.currentValue = currentValue;
        this.endValue = endValue;
    }

    public void setCurrentValue(long currentValue) { this.currentValue = currentValue; }
    public void setEndValue(long endValue) { this.endValue = endValue; }
    public long incrementAndGet() { return ++currentValue; }
}

The get method first checks an in‑memory map; if the segment is exhausted, it fetches the next block from the database, updates the table, and refreshes the Step object.

private Map
stepMap = new HashMap
();

public synchronized long get(String sequenceName) {
    Step step = stepMap.get(sequenceName);
    if(step == null) {
        step = new Step(startValue, startValue+blockSize);
        stepMap.put(sequenceName, step);
    } else {
        if (step.currentValue < step.endValue) {
            return step.incrementAndGet();
        }
    }
    if (getNextBlock(sequenceName,step)) {
        return step.incrementAndGet();
    }
    throw new RuntimeException("No more value.");
}

private boolean getNextBlock(String sequenceName, Step step) {
    // Long value = select id from sequence_value where name = ?
    Long value = getPersistenceValue(sequenceName);
    if (value == null) {
        try {
            // insert into sequence_value (id,name) values (?,?)
            value = newPersistenceValue(sequenceName);
        } catch (Exception e) {
            value = getPersistenceValue(sequenceName);
        }
    }
    // update sequence_value set id = ? where name = ? and id = ?
    boolean b = saveValue(value,sequenceName) == 1;
    if (b) {
        step.setCurrentValue(value);
        step.setEndValue(value+blockSize);
    }
    return b;
}

Using this segment‑allocation technique reduces the number of database round‑trips, but developers must still consider transaction isolation; if the method runs inside a transaction that is delayed, other threads may read uncommitted IDs, leading to duplicates.

Conclusion : Order number generation is simple in concept, yet achieving high performance and reliability under concurrency requires careful design of database tables, transaction boundaries, and optional in‑memory ID segment caching.