死鎖: delete+insert不存在PK

###### 表結構及資料
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



引用