MySQL過程真要慎用if not exists寫法

壹頁書發表於2017-07-18
MySql
接前文
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 的查詢居然上了鎖..

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2142262/，如需轉載，請註明出處，否則將追究法律責任。

相關文章