死锁: delete+insert不存在PK
来源:互联网 发布:unity3d 5.0 入门教程 编辑:程序博客网 时间:2024/06/06 04:24
###### 表结构及数据
CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) ,
`b` int(11) ,
`c` int(11) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
/*Data for the table `tb` */
insert into `tb`(`id`,`a`,`b`,`c`) values (1,1,0,1);
insert into `tb`(`id`,`a`,`b`,`c`) values (2,2,1,2);
insert into `tb`(`id`,`a`,`b`,`c`) values (3,3,2,3);
insert into `tb`(`id`,`a`,`b`,`c`) values (4,4,2,4);
insert into `tb`(`id`,`a`,`b`,`c`) values (5,5,3,5);
insert into `tb`(`id`,`a`,`b`,`c`) values (6,6,3,6);
insert into `tb`(`id`,`a`,`b`,`c`) values (7,7,4,7);
insert into `tb`(`id`,`a`,`b`,`c`) values (8,8,5,8);
insert into `tb`(`id`,`a`,`b`,`c`) values (10,10,6,10);
###### 操作流程
事务1
START TRANSACTION;
DELETE FROM tb WHERE id=9 AND b=2;
------- 持有GAP(8,10)锁
事务2
START TRANSACTION;
DELETE FROM tb WHERE id=9 AND b=2;
------- 持有GAP(8,10)锁
事务1
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0);
------- 阻塞等待锁,TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
------- INNODB_LOCKS 表数据
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
1169A0:0:67344:27 1169A0 X,GAP RECORD `test`.`tb` `PRIMARY` 0 67344 27 10
11699F:0:67344:27 11699F X,GAP RECORD `test`.`tb` `PRIMARY` 0 67344 27 10
事务2
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0);
------- 发生死锁,都在等待同一个GAP
###### 死锁信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
150318 17:19:31
*** (1) TRANSACTION:
TRANSACTION 1169A0, ACTIVE 34 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 185, OS thread handle 0x164c, query id 1884365 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) TRANSACTION:
TRANSACTION 11699F, ACTIVE 39 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 187, OS thread handle 0x2a88, query id 1884373 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 11699F lock_mode X locks gap before rec
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 11699F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
---TRANSACTION 1169A0, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 185, OS thread handle 0x164c, query id 1884365 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
------------------
---TRANSACTION 11699F, ACTIVE 29 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 187, OS thread handle 0x2a88, query id 1884362 localhost 127.0.0.1 root
CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) ,
`b` int(11) ,
`c` int(11) ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
/*Data for the table `tb` */
insert into `tb`(`id`,`a`,`b`,`c`) values (1,1,0,1);
insert into `tb`(`id`,`a`,`b`,`c`) values (2,2,1,2);
insert into `tb`(`id`,`a`,`b`,`c`) values (3,3,2,3);
insert into `tb`(`id`,`a`,`b`,`c`) values (4,4,2,4);
insert into `tb`(`id`,`a`,`b`,`c`) values (5,5,3,5);
insert into `tb`(`id`,`a`,`b`,`c`) values (6,6,3,6);
insert into `tb`(`id`,`a`,`b`,`c`) values (7,7,4,7);
insert into `tb`(`id`,`a`,`b`,`c`) values (8,8,5,8);
insert into `tb`(`id`,`a`,`b`,`c`) values (10,10,6,10);
###### 操作流程
事务1
START TRANSACTION;
DELETE FROM tb WHERE id=9 AND b=2;
------- 持有GAP(8,10)锁
事务2
START TRANSACTION;
DELETE FROM tb WHERE id=9 AND b=2;
------- 持有GAP(8,10)锁
事务1
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0);
------- 阻塞等待锁,TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
------- INNODB_LOCKS 表数据
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data
1169A0:0:67344:27 1169A0 X,GAP RECORD `test`.`tb` `PRIMARY` 0 67344 27 10
11699F:0:67344:27 11699F X,GAP RECORD `test`.`tb` `PRIMARY` 0 67344 27 10
事务2
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0);
------- 发生死锁,都在等待同一个GAP
###### 死锁信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
150318 17:19:31
*** (1) TRANSACTION:
TRANSACTION 1169A0, ACTIVE 34 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 185, OS thread handle 0x164c, query id 1884365 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) TRANSACTION:
TRANSACTION 11699F, ACTIVE 39 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 187, OS thread handle 0x2a88, query id 1884373 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 11699F lock_mode X locks gap before rec
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 11699F lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
---TRANSACTION 1169A0, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 185, OS thread handle 0x164c, query id 1884365 localhost 127.0.0.1 root update
INSERT INTO `tb` (`id`,`a`, `b`,`c`) VALUES (9,1,2,0)
------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 67344 n bits 104 index `PRIMARY` of table `test`.`tb` trx id 1169A0 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 27 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000038a02; asc ;;
2: len 7; hex 3c00010702031e; asc < ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 8000000a; asc ;;
------------------
---TRANSACTION 11699F, ACTIVE 29 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 187, OS thread handle 0x2a88, query id 1884362 localhost 127.0.0.1 root
0 0
- 死锁: delete+insert不存在PK
- insert 死锁
- SQL Server:触发器品读 -- Insert、 Delete、 Update(“updated ”是不存在的,不能想当然)
- new/delete PK malloc/free
- 有趣的insert死锁
- RBTree(Insert & Delete)
- Insert Update Delete Select
- Creat Delete and Insert
- Insert , Update , Delete 触发器
- Insert,Update,Delete操作
- 35 min_max_heap insert delete
- insert、delete、update、replace
- mysql 存在update不存在insert
- Think\Model:insert方法不存在!
- INSERT,UPDATE,DELETE时不写日志
- SSIS 实现update,delete,insert
- 數據庫訪問類(Query,Insert,Update,Delete)
- returning into(update,delete,insert)
- Openjudge 8465 马走日
- 二叉树系列问题——求二叉树中最远节点的距离
- 重新登录 页面跳转到之前页面的一种解决办法
- C++primer plus第9-10章笔记
- iOS中OC调用js方法简述(一)
- 死锁: delete+insert不存在PK
- jQuery自定义插件
- PyQt5教程(十)——自定义控件
- android之文件存储路径问题
- 在TextView中添加超链接
- 【opencv的学习】播放avi视频和视频播放控制
- 认识CoreData-使用进阶
- STA 进程内Com组件剖析
- 博弈_______Stone Game(hdu 4387)