在开发中，经常会做这类的判断：根据主键值查询，如果不存在，则插入；否则更新。
例如
表结构

点击(此处)折叠或打开

CREATE TABLE `t` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`val` BIGINT(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

使用方式

点击(此处)折叠或打开

START TRANSACTION;
-- 方式一
SELECT id FROM `t` WHERE `id` ='N' LOCK IN SHARE MODE;
IF (FOUND_ROWS() <= 0) THEN
-- do insert
end if;
-- 方式二
UPDATE `t` SET `val` = 'XX' WHERE `id` ='N';
IF (ROW_COUNT() <= 0) THEN
-- do insert
end if;
commit;

当N超过表里的最大值时，两种方式都会对primary索引的 supremum pseudo-record加一个X锁，导致其他的insert会被阻塞
supremum pseudo-record :相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum”pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

建议改成原子操作, 不会阻塞其他insert

点击(此处)折叠或打开

START TRANSACTION;
insert into ... on duplicate key update `val`='XX';
commit;

ITPub博客

supremum pseudo-record锁影响

最新文章