接前文

http://blog.itpub.net/29254281/viewspace-2133936/

除了前文提到的问题,今天还发现了MySQL 过程 if not exists 的另外一个问题.

https://bugs.mysql.com/bug.php?id=85431

版本 5.6.14

环境

CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`batchid` int(11) DEFAULT NULL,

`state` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO `t1` (`id`,`batchid`,`state`) VALUES (1,1,1);

INSERT INTO `t1` (`id`,`batchid`,`state`) VALUES (2,1,1);

drop procedure if exists p;

delimiter $$

CREATE PROCEDURE `p`()

begin

if not exists (select * from t1 where id=1) then

select 'id=1 not exists';

end if;

end $$

delimiter ;

按照如下时间轴执行 ,窗口二居然被阻塞了.

查看 show engine innodb status;

发现如下信息:

---TRANSACTION 15252974, ACTIVE 2 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)

MySQL thread id 38, OS thread handle 0x1d88, query id 4663 localhost 127.0.0.1 root statistics

select * from t1 where id=1

LIMIT 0, 1000

for update

------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2344 page no 3 n bits 72 index `PRIMARY` of table `mvbox`.`t1` trx id 15252974 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc     ;;

1: len 6; hex 000000e8bdc4; asc       ;;

2: len 7; hex 030003c0100437; asc       7;;

3: len 4; hex 80000001; asc     ;;

4: len 4; hex 80000001; asc     ;;

------------------

---TRANSACTION 15252973, ACTIVE 15 sec

2 lock struct(s), heap size 376, 1 row lock(s)

MySQL thread id 36, OS thread handle 0x35c, query id 4615 localhost 127.0.0.1 root cleaning up

过程中 if not exists 的查询居然上了锁..