mysql-建立类似于Oracle序列的机制 [英] mysql - making a mechanism similar to Oracle's seqences
问题描述
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?
谢谢.
推荐答案
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的ASAP发行(显然在更新后不需要)，而继续执行耗时的代码，则在发行之前，对于其他客户端，在超时期限之后可能会发生以下情况等待序列号:
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:
错误1205(HY000):超出了锁定等待超时；尝试重新启动 交易
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
希望这绝不是问题.
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;
这篇关于mysql-建立类似于Oracle序列的机制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
