LATEST DETECTED DEADLOCK
------------------------
2017-11-27 11:26:22 2b80ab881700
*** (1) TRANSACTION:
TRANSACTION 84983140, ACTIVE 3.138 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 102
LOCK BLOCKING MySQL thread id: 4188408 block 4188409
MySQL thread id 4188409, OS thread handle 0x2b80c834c700, query id 1119236930 119.23.142.11 all5 update
INSERT INTO t_flow_real_time( `spot`,`group_quantity`,`booking`,`statistics_time` )
values ( 1,1,'2017-11-24','2017-11-27 11:26:21.007' )
ON DUPLICATE KEY UPDATE
`group_quantity` = group_quantity + 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983140 lock_mode X waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc378; asc x;;
2: len 4; hex 8000984d; asc M;;*** (2) TRANSACTION:
TRANSACTION 84983143, ACTIVE 1.554 sec inserting
mysql tables in use 1, locked 1
13 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 46
MySQL thread id 4188408, OS thread handle 0x2b80ab881700, query id 1119237503 119.23.142.11 all580 update
INSERT INTO t_flow_real_time ( `spot`, `group_quantity`, `booking`, `statistics_time` )
values ( 1, 1, '2017-11-26', '2017-11-27 11:26:22.407' )
ON DUPLICATE KEY UPDATE
`group_quantity` = group_quantity + 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983143 lock_mode X
Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc378; asc x;;
2: len 4; hex 8000984d; asc M;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983143 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc37a; asc z;;
2: len 4; hex 8000987b; asc {;;*** WE ROLL BACK TRANSACTION (2)
------------------------
2017-11-27 11:26:22 2b80ab881700
*** (1) TRANSACTION:
TRANSACTION 84983140, ACTIVE 3.138 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 102
LOCK BLOCKING MySQL thread id: 4188408 block 4188409
MySQL thread id 4188409, OS thread handle 0x2b80c834c700, query id 1119236930 119.23.142.11 all5 update
INSERT INTO t_flow_real_time( `spot`,`group_quantity`,`booking`,`statistics_time` )
values ( 1,1,'2017-11-24','2017-11-27 11:26:21.007' )
ON DUPLICATE KEY UPDATE
`group_quantity` = group_quantity + 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983140 lock_mode X waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc378; asc x;;
2: len 4; hex 8000984d; asc M;;*** (2) TRANSACTION:
TRANSACTION 84983143, ACTIVE 1.554 sec inserting
mysql tables in use 1, locked 1
13 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 46
MySQL thread id 4188408, OS thread handle 0x2b80ab881700, query id 1119237503 119.23.142.11 all580 update
INSERT INTO t_flow_real_time ( `spot`, `group_quantity`, `booking`, `statistics_time` )
values ( 1, 1, '2017-11-26', '2017-11-27 11:26:22.407' )
ON DUPLICATE KEY UPDATE
`group_quantity` = group_quantity + 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983143 lock_mode X
Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc378; asc x;;
2: len 4; hex 8000984d; asc M;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1258 page no 4 n bits 104 index `idx_unique` of table `a_analysis`.`t_flow_real_time` trx id 84983143 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 3; hex 8fc37a; asc z;;
2: len 4; hex 8000987b; asc {;;*** WE ROLL BACK TRANSACTION (2)
Create TableCREATE TABLE `t_flow_real_time` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`spot` int(11) DEFAULT NULL COMMENT '景区',
`group_quantity` int(11) DEFAULT '0' COMMENT '今日团队人数',
`quantity` int(11) DEFAULT '0' COMMENT '今日票数',
`consume_quantity` int(11) DEFAULT '0' COMMENT '核销数量',
`consume_group_quantity` int(11) DEFAULT '0' COMMENT '核销团队人数',
`refund_quantity` int(11) DEFAULT '0' COMMENT '退票数',
`refund_group_quantity` int(11) DEFAULT '0' COMMENT '退票团队人数',
`booking` date DEFAULT NULL COMMENT '游玩日期',
`statistics_time` datetime DEFAULT NULL COMMENT '统计时间',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique` (`spot`,`booking`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=46451 DEFAULT CHARSET=utf8 COMMENT='客流实时数据'
查看死锁发送时的sql
日志是否有缺失呀?通过提供的日志分析出如下:
事务2(tx_id:84983143)应该有两条SQL,用sql1和sql2表示。
sql1对应的idx_unique数据库结构为:
hex 80000001
hex 8fc378
hex 8000984d
sql2对应的idx_unique数据库结构为:
hex 80000001
hex 8fc37a
hex 8000987b事务1(tx_id:84983140)从日志只看到一条SQL,用sql1表示。
sql1对应的idx_unique数据库结构为:
hex 80000001
hex 8fc378
hex 8000984d结果:
从日志分析能看出:
1、事务1 的sql1正在等待 事务2的sql1对应的二级索引(idx_unique(hex 80000001,hex 8000984d))记录上的X锁。
2、事务2的sql2正在等待 另一个事务的sql=sql2对应的二级索引 (idx_unique(hex 80000001,hex 8000987b))记录上的X锁。
因此出现事务1 等待 事务2, 而事务2 又等待 另一个事务的困境。若这里的 另一个事务 就是事务1的话,那就彻底死锁了; 若另一个事务 是 事务3的话,那就只有等事务3释放了事务2等待的X锁,事务2执行后再释放了事务1等待的X锁,这时候事务1才可以正常执行。
通过附件附上分析过程,供参考指正。