MySQL で sequence 作ってみたが... (再チャレンジ)

先日 MySQL の sequence をちょっと作ってみたが、あれからちょっと進化させてトランザクション外でも大丈夫なようにしてみた(SELECT FOR UPDATE はやめて、ワンライン update + last_insert_id() で済ますようにした)。

ただ、トランザクション内に nextval() や setval() を入れてしまうと、他のセッションで同一 sequence を nextval(), setval() しようとしたときにロックかかってしまう状態になる。

うーーんこのあたりなんとかならんか。トランザクション内でも特定のテーブルもしくは行だけはロックがかからないようにしたいのだが。

-- ### sequence テーブル定義 ###
CREATE TABLE sequence (
    sequence_name VARCHAR(50) NOT NULL,
    current_value BIGINT,
    increment BIGINT NOT NULL DEFAULT 1,
    start_value BIGINT,
    min_value BIGINT,
    max_value BIGINT,
    is_cycle BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (sequence_name)
);

-- ### sequence テーブルデフォルト定義 ###
DELIMITER //
CREATE TRIGGER sequence_default BEFORE INSERT ON sequence
FOR EACH ROW
BEGIN
    IF NEW.increment > 0 THEN
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = POWER(2, 63) - 1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.min_value;
        END IF;
    ELSE
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = -1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = POWER(-2, 63) - 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.max_value;
        END IF;
    END IF;
END
//
-- ### シーケンス関数内部利用のテンポラリテーブル定義 ###
DELIMITER //
CREATE PROCEDURE _create_tmp_sequence ()
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sequence (
        tmp_sequence_name VARCHAR(50) KEY,
        tmp_current_value BIGINT
    );
END
//
DELIMITER ;
-- ### currval() ###
DELIMITER //
CREATE FUNCTION currval (seq VARCHAR(50))
     RETURNS BIGINT
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     DECLARE mes varchar(128);
     DECLARE my_currval INT;
     CALL _create_tmp_sequence();
     SELECT tmp_current_value
     INTO my_currval
     FROM tmp_sequence
     WHERE tmp_sequence_name = seq;
 
     IF my_currval IS NULL THEN
         IF (SELECT 1 FROM sequence WHERE sequence_name = seq) THEN
             -- 一度も nextval() or setval() してない
             SET mes = concat('currval of sequence "',
                               seq,
                              '" is not yet defined in this session');
         ELSE
             -- そもそも sequence がない
             SET mes = concat('sequence "', seq, '" does not exist');
         END IF;
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
     END IF;
     RETURN my_currval;
END
//
DELIMITER ;
-- ### setval() ###
DELIMITER //
CREATE FUNCTION setval (seq VARCHAR(50), val INTEGER)
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE my_max_value BIGINT;
    DECLARE my_sequence_name varchar(50);
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();

    SELECT sequence_name, max_value
    INTO my_sequence_name, my_max_value
    FROM sequence
    WHERE sequence_name = seq;
    IF my_sequence_name IS NULL THEN
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    IF val > my_max_value THEN
        SET mes = concat('setval: over maximum value of sequence ',
                         seq,
                         '(', my_max_value,')');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    UPDATE sequence
    SET current_value = last_insert_id(val)
    WHERE sequence_name = seq;
    REPLACE INTO tmp_sequence VALUES (seq, last_insert_id());
    RETURN last_insert_id();
 END
//
DELIMITER ;
DELIMITER //
CREATE FUNCTION nextval (seq VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 BEGIN
    DECLARE mes VARCHAR(128);
    DECLARE my_next_value BIGINT;
    DECLARE my_max_value BIGINT;
    CALL _create_tmp_sequence();

    UPDATE sequence
    SET current_value =
        last_insert_id(
             CASE WHEN (current_value is NULL) OR
                       (is_cycle && (current_value > (max_value - increment)))
                      THEN start_value
                  ELSE current_value + increment
             END)
    WHERE sequence_name = seq;
    IF (SELECT ROW_COUNT()) != 1 THEN
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    SELECT last_insert_id(), max_value
    INTO my_next_value, my_max_value
    FROM sequence
    WHERE sequence_name = seq;
    IF my_next_value > my_max_value THEN
        SET mes = concat('nextval: reached maximum value of sequence ',
                         seq,
                         '(', my_max_value,')');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;
    REPLACE INTO tmp_sequence VALUES (seq, my_next_value);
    RETURN my_next_value;
 END
//
DELIMITER ;

-- ## sequence 定義 (create sequence みたいなもん)
-- デフォルト(値は PostgreSQL チック)で良い場合
INSERT INTO sequence (sequence_name) VALUES ('myseq1');
-- 2 ずつ increment の場合
INSERT INTO sequence (sequence_name, incremetn) VALUES ('myseq2', 2);
-- 1 ずつ increment(デフォルト) かつ start を 10 から, 最大値 9999, 最小値 10
INSERT INTO sequence (sequence_name, start_value, min_value, max_value)
  VALUES ('myseq3', 10, 10, 9999);