MySQL過程真要慎用if not exists寫法
接前文
http://blog.itpub.net/29254281/viewspace-2133936/
除了前文提到的問題,今天還發現了MySQL 過程 if not exists 的另外一個問題.
版本 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 的查詢居然上了鎖..
http://blog.itpub.net/29254281/viewspace-2133936/
除了前文提到的問題,今天還發現了MySQL 過程 if not exists 的另外一個問題.
版本 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL過程慎用if not exists寫法MySql
- 儲存過程中慎用 execute immediate儲存過程
- MySQL資料寫入過程介紹MySql
- MySQL的寫入資料儲存過程MySql儲存過程
- MySql儲存過程的作用及語法MySql儲存過程
- MySQL:MGR 學習(2):Write set(寫集合)的寫入過程MySql
- Oracle儲存過程中跳出迴圈的寫法Oracle儲存過程
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- MySQL之in與existsMySql
- HDFS寫過程分析
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- MySql 儲存過程 臨時表 無法插入資料MySql儲存過程
- not exists 中from 後面不同寫法帶來的效率區別
- MySQL exists 優化 in 效率MySql優化
- t_c_b_s之儲存過程寫法小記儲存過程
- MySQL的session過程MySqlSession
- MySQL Order BY 排序過程MySql排序
- MySQL恢復過程MySql
- mysql關閉過程MySql
- mysql 儲存過程MySql儲存過程
- 儲存過程語法儲存過程
- 【Mysql】Mysql儲存過程學習MySql儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- mysql 關於exists 和in分析MySql
- MySQL 5.7 NOT EXISTS用法介紹MySql
- MySQL冷備份過程MySql
- mysql儲存過程整理MySql儲存過程
- MySQL過程和遊標MySql
- MySQL之儲存過程MySql儲存過程
- [MYSQL -23儲存過程]MySql儲存過程
- MySQL 事務提交過程MySql
- MYSQL儲存過程管理MySql儲存過程
- mysql儲存過程例子MySql儲存過程
- mysql的儲存過程MySql儲存過程
- MySQL---------儲存過程MySql儲存過程
- 要慎用mysql的enum欄位的原因MySql
- mysql 儲存過程和事件排程MySql儲存過程事件
- 寫了一個MySQL的行轉列的儲存過程薦MySql儲存過程