例如
表结构
点击(此处)折叠或打开
-
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;