innodb - 如何实现mysql线程安全的nextval()方法

Question

尝试通过建立序列表与函数实现，可以正常返回下条序列，但通过jmeter测试并发100个线程，发现返回的结果中存在重复的记录。

下面是链接中的实现方法

-- 表
CREATE TABLE `sequence_data` (
    `sequence_name` varchar(100) NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`sequence_name`)
);
-- 函数
delimiter //
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);
 
    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_name = seq_name
    ;
 
    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_name = seq_name
        ;
    END IF;
 
    RETURN cur_val;
END

//
delimiter ;

-- insert new sequence
-- INSERT INTO sequence_data (sequence_name) VALUE ('sq_my_sequence');

-- 调用
-- SELECT nextval('sq_my_sequence') as nextval

Answer 1

mysql預設隔離等級是repeatable_read,在你事務提交之前，其他執行緒讀不到你的修改。可以在結尾增加commit提交update的結果。如果還不就使用行級鎖定(select * sequence_data from WHERE sequence_name = seq_name for update)，一個執行緒不提交，其他執行緒都不允許讀，這樣就一定可以保證並發安全。