Recently, we need to migrate the project from DB2 database to MySQL database. Because MySQL has no sequence, we find the sequence implementation of MySQL on the internet, which is basically based on stored procedures and functions. The first and second implementations are seen from the internet, and the third, fourth and fifth implementations are our own ideas. The fifth method is feasible, but it has not passed the high concurrency test.

1. Create tables to store current sequence values

CREATE TABLE `TBL_SEQUENCE_1` (
    `sequence_name`  varchar(64) NOT NULL COMMENT 'Sequence name' ,
    `value`  bigint NOT NULL DEFAULT 0 COMMENT 'Current value' ,
    PRIMARY KEY (`sequence_name`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;

The first implementation, unlocked, may return duplicate values with high concurrency

DELIMITER //
CREATE FUNCTION NEXT_VAL(sequence_name varchar(64)) 
RETURNS bigint 
BEGIN
    declare current_val bigint;
    set current_val = 0;

    update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;
    select t.value into current_val from t_sequence t where t.sequence_name = sequence_name;

    return current_val;
END//
DELIMITER ;

The second implementation, pessimistic lock, guarantees unique return values under high concurrency, but may result in dramatic performance degradation

DELIMITER //
CREATE FUNCTION NEXT_VAL(sequence_name varchar(64)) 
RETURNS bigint 
BEGIN
    declare current_val bigint;
    set current_val = 0;

    select t.value into current_val from t_sequence t where t.sequence_name = sequence_name for update;
    update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;

    return current_val + 1;
END//
DELIMITER ;

The third implementation, optimistic lock, tries at most three times (application needs to determine whether the serial number is 0, 0 means that the acquisition of the serial number failed)

DELIMITER //
CREATE FUNCTION NEXT_VAL(sequence_name VARCHAR(64)) 
RETURNS BIGINT 
BEGIN
    DECLARE current_val BIGINT;
    DECLARE tryTimes INT;

    SET tryTimes = 0;

    WHILE tryTimes < 3 DO
        SELECT t.value INTO current_val FROM TBL_SEQUENCE_1 t WHERE t.sequence_name = sequence_name;
        UPDATE TBL_SEQUENCE_1 t SET t.value = t.value + 1 WHERE t.sequence_name = sequence_name AND t.value=current_val;

        IF ROW_COUNT() > 0 THEN
            RETURN current_val + 1;
        END IF;
        SET tryTimes = tryTimes + 1;
    END WHILE;
    RETURN 0;
END//
DELIMITER ;

The fourth way is to reduce competition by decentralizing and concurrently increasing the number of records.

A sequence uses multiple records, and each time the serial number is acquired, a record is randomly selected for updating (using exclusive locks, multiple records can effectively reduce lock competition)
For example, use 10 records, each with an initial value of 1-10 and a growth step of 10.

The fifth implementation, the ultimate implementation, does not lock, guarantees that the return value is unique and does not affect performance.

DELIMITER //
CREATE FUNCTION NEXT_VAL(sequence_name VARCHAR(64)) 
RETURNS BIGINT 
BEGIN
    UPDATE TBL_SEQUENCE_1 t, (SELECT @current_val:=`value` FROM TBL_SEQUENCE_1 t2 WHERE t2.sequence_name=sequence_name) t3 SET t.value = t.value + 1 WHERE t.sequence_name =sequence_name AND @current_val=t.value;
    RETURN @current_val+1;
END//
DELIMITER ;