mysql - 制作类似于 Oracle 的序列的机制

2021-11-20 00:00:00 mysql sequences

MySQL 提供了一种自动增加记录 ID 的机制.这对于许多目的来说都可以，但我需要能够使用 ORACLE 提供的序列.显然，为此目的创建表是没有意义的.

MySQL provides an automatic mechanism to increment record IDs. This is OK for many purposes, but I need to be able to use sequences as offered by ORACLE. Obviously, there is no point in creating a table for that purpose.

解决方案应该很简单:

1) 创建一个表来存放所有需要的序列，

1) Create a table to hosts all the needed sequences,

2) 创建一个函数，增加特定序列的值并返回新值，

2) Create a function that increases the value of a specific sequence and returns the new value,

3) 创建一个返回序列当前值的函数.

3) Create a function that returns the current value of a sequence.

理论上，它看起来很简单……但是……

In theory, it looks simple... BUT...

当增加一个序列的值时(与Oracle中的nextval非常相似)，需要阻止其他会话执行此操作(甚至获取当前值)，直到更新完成.

When increasing the value of a sequence (much the same as nextval in Oracle), you need to prevent other sessions to perform this operation (or even fetch the current value) till the updated is completed.

两个理论选项:

a - 使用一个 UPDATE 语句，该语句将在一次拍摄中返回新值，或

a - Use an UPDATE statement that would return the new value in a single shot, or

b - 在 UPDATE 和 SELECT 之间锁定表.

b - Lock the table between the UPDATE and SELECT.

不幸的是，MySQL 似乎不允许在函数/过程中锁定表，并且尝试在单个语句中完成整个事情(例如 UPDATE... RETURNING...)时，您必须使用 @-type在函数/过程完成后仍然存在的变量.

Unfortunately, it would appear that MySQL does not allow to lock tables within functions / procedures, and while trying to make the whole thing in a single statement (like UPDATE... RETURNING...) you must use @-type variables which survive the completion of the function/procedure.

有没有人对此有想法/可行的解决方案?

Does anyone have an idea/working solution for this?

谢谢.

推荐答案

以下是一个带有 FOR UPDATE 的简单示例 意图锁.带有 INNODB 引擎的行级锁.该示例显示了下一个可用序列的四行，这些序列不会受到众所周知的 INNODB 间隙异常(在使用 AUTO_INCREMENT 失败后出现间隙的情况)的影响.

The following is a simple example with a FOR UPDATE intention lock. A row-level lock with the INNODB engine. The sample shows four rows for next available sequences that will not suffer from the well-known INNODB Gap Anomaly (the case where gaps occur after failed usage of an AUTO_INCREMENT).

架构:

-- drop table if exists sequences;
create table sequences
(   id int auto_increment primary key,
    sectionType varchar(200) not null,
    nextSequence int not null,
    unique key(sectionType)
) ENGINE=InnoDB;

-- truncate table sequences;
insert sequences (sectionType,nextSequence) values
('Chassis',1),('Engine Block',1),('Brakes',1),('Carburetor',1);

示例代码:

START TRANSACTION; -- Line1
SELECT nextSequence into @mine_to_use from sequences where sectionType='Carburetor' FOR UPDATE; -- Line2 
select @mine_to_use; -- Line3
UPDATE sequences set nextSequence=nextSequence+1 where sectionType='Carburetor'; -- Line4
COMMIT; -- Line5

理想情况下，您根本没有 Line3 或臃肿的代码，这会延迟其他客户端的锁定等待.意思是，让你的下一个序列使用，执行更新(递增部分)，然后COMMIT，尽快.

Ideally you do not have a Line3 or bloaty code at all which would delay other clients on a Lock Wait. Meaning, get your next sequence to use, perform the update (the incrementing part), and COMMIT, ASAP.

以上存储过程:

DROP PROCEDURE if exists getNextSequence;
DELIMITER $$
CREATE PROCEDURE getNextSequence(p_sectionType varchar(200),OUT p_YoursToUse int)
BEGIN
    -- for flexibility, return the sequence number as both an OUT parameter and a single row resultset
    START TRANSACTION;
    SELECT nextSequence into @mine_to_use from sequences where sectionType=p_sectionType FOR UPDATE;
    UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType;
    COMMIT; -- get it and release INTENTION LOCK ASAP
    set p_YoursToUse=@mine_to_use; -- set the OUT parameter
    select @mine_to_use as yourSeqNum; -- also return as a 1 column, 1 row resultset
END$$
DELIMITER ;

测试:

set @myNum:= -1;
call getNextSequence('Carburetor',@myNum);
+------------+
| yourSeqNum |
+------------+
|          4 |
+------------+
select @myNum; -- 4

根据您的需要相应地修改存储过程，例如只有两种机制中的一种来检索序列号(OUT 参数或结果集).换句话说，很容易抛弃 OUT 参数概念.

Modify the stored procedure accordingly for you needs, such as having only 1 of the 2 mechanisms for retrieving the sequence number (either the OUT parameter or the result set). In other words, it is easy to ditch the OUT parameter concept.

如果你不坚持尽快释放LOCK(更新后显然不需要)，并在释放之前继续执行耗时的代码，那么在其他客户端超时后可能会发生以下情况等待序列号:

If you do not adhere to ASAP release of the LOCK (which obviously is not needed after the update), and proceed to perform time consuming code, prior to the release, then the following can occur after a timeout period for other clients awaiting a sequence number:

ERROR 1205 (HY000):超出锁定等待超时；尝试重新启动交易

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

希望这永远不会成为问题.

Hopefully this is never an issue.

show variables where variable_name='innodb_lock_wait_timeout';

innodb_lock_wait_timeout 的

MySQL 手册页.

MySQL Manual Page for innodb_lock_wait_timeout.

目前在我的系统上它的值为 50(秒).在大多数情况下，等待超过一两秒钟可能是无法忍受的.

On my system at the moment it has a value of 50 (seconds). A wait of more than a second or two is probably unbearable in most situations.

在 TRANSACTIONS 期间还感兴趣的是以下命令的输出部分:

Also of interest during TRANSACTIONS is that section of the output from the following command:

SHOW ENGINE INNODB STATUS;

相关文章