小编典典
以下是带有FOR UPDATE 意向锁的简单示例。使用INNODB引擎的行级锁。该示例显示了四行用于下一个可用序列,这些序列不会受到众所周知的INNODB间隙异常(在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,
尽快 。
上面的一个存储过程:
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参数概念。
如果您不遵循LOCK的ASAP发行(显然在更新后不需要),并继续执行耗时的代码,则在发行之前,对于等待序列的其他客户端,可能会在超时后发生以下情况数:
错误1205(HY000):超出了锁定等待超时;尝试重新启动事务
希望这绝不是问题。
show variables where variable_name='innodb_lock_wait_timeout';
目前在我的系统上,它的值为50(秒)。在大多数情况下,超过一两秒钟的等待可能无法忍受。
在TRANSACTIONS期间,另一个有趣的地方是以下命令的输出部分:
SHOW ENGINE INNODB STATUS;
2020-05-17