mysql 类似oracle,mysql – 制作类似于Oracle的seqences的机制

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

解决方案应该很简单：

1)创建一个表来托管所有需要的序列,

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

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

从理论上讲,它看起来很简单……但……

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

两个理论选择：

a – 使用UPDATE语句,该语句将在单个镜头中返回新值,或者

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

不幸的是,似乎MySQL不允许在函数/过程中锁定表,并且在尝试在单个语句中完成整个操作(如UPDATE … RETURNING …)时,必须使用@ -type变量才能生存完成功能/程序.

有没有人有这个想法/工作解决方案？

谢谢.

解决方法:

以下是FOR UPDATE intention lock的简单示例.使用INNODB引擎的行级锁定.该示例显示了下一个可用序列的四行,这些序列不会受到众所周知的INNODB Gap Anomaly的影响(在使用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或臃肿的代码,这会延迟Lock Wait上的其他客户端.意思是,尽快使用下一个序列,执行更新(递增部分)和COMMIT.

以上在存储过程中：

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

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

如果您不遵守ASOCK的ASAP版本(更新后显然不需要),并且在发布之前继续执行耗时的代码,则在等待序列的其他客户端的超时期限之后可能会发生以下情况数：

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

transaction

希望这绝不是一个问题.

show variables where variable_name='innodb_lock_wait_timeout';

在我的系统上,它的值为50(秒).在大多数情况下,等待一两秒以上可能是无法忍受的.

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

SHOW ENGINE INNODB STATUS;

标签：sequences,mysql

来源： https://codeday.me/bug/20190916/1807447.html