经过MYSQL日志定位死锁问题ITeye - 凯时娱乐

经过MYSQL日志定位死锁问题ITeye

2019-01-11 14:38:21 | 作者: 忆之 | 标签: 数据,多个,上述 | 浏览: 206


LATEST DETECTED DEADLOCK
------------------------
140121 21:28:15
*** (1) TRANSACTION:
TRANSACTION AC690EFA, ACTIVE 0 sec, process no 2040, OS thread id 139751216285440 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 1072445, query id 990357233 192.168.16.71 xxxx_push4app update
INSERT IGNORE INTO T_BS_PUSH_SUB_EXTEND(FSEQUENCENO, FUSERID, FDEVICEID, FPACKAGEID, FSERVICEID, FSERVICETOKEN,  FSUBSTATUS, FSUBTIME, FVERSIONID, FPRODUCT,FFIRMWARE)  VALUES(27021094, 0, 862845025904090, 3, 8, 862845025904090100004, 1,  1390310896, 103140, M351, 4.2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6915 page no 323879 n bits 568 index `UDX_PKG_DEVICE` of table `xxxx_PUSH`.`T_BS_PUSH_SUB_EXTEND` trx id AC690EFA lock_mode X waiting
Record lock, heap no 214 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 03; asc  ;;
1: len 15; hex 383632383435303235393034303930; asc 862845025904090;;
2: len 2; hex 0008; asc  ;;
3: len 8; hex 00000000019c4697; asc  F ;;

*** (2) TRANSACTION:
TRANSACTION AC690EFB, ACTIVE 0 sec, process no 2040, OS thread id 139751337277184 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 5
MySQL thread id 1072539, query id 990357236 192.168.16.78 xxxx_push4app update
INSERT IGNORE INTO T_BS_PUSH_SUB_EXTEND(FSEQUENCENO, FUSERID, FDEVICEID, FPACKAGEID, FSERVICEID, FSERVICETOKEN,  FSUBSTATUS, FSUBTIME, FVERSIONID, FPRODUCT,FFIRMWARE)  VALUES(27018907, 0, 862845025904090, 3, 1, 862845025904090100004, 1,  1390310896, 156050, M351, null)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6915 page no 323879 n bits 568 index `UDX_PKG_DEVICE` of table `xxxx_PUSH`.`T_BS_PUSH_SUB_EXTEND` trx id AC690EFB lock_mode X locks rec but not gap
Record lock, heap no 214 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 03; asc  ;;
1: len 15; hex 383632383435303235393034303930; asc 862845025904090;;
2: len 2; hex 0008; asc  ;;
3: len 8; hex 00000000019c4697; asc  F ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6915 page no 323879 n bits 568 index `UDX_PKG_DEVICE` of table `xxxx_PUSH`.`T_BS_PUSH_SUB_EXTEND` trx id AC690EFB lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 214 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 03; asc  ;;
1: len 15; hex 383632383435303235393034303930; asc 862845025904090;;
2: len 2; hex 0008; asc  ;;
3: len 8; hex 00000000019c4697; asc  F ;;

*** WE ROLL BACK TRANSACTION (1)


上述场景是一次性刺进多个数据,也就是说多个数据放在一个业务中统一一次提交。当192.168.16.71和192.168.16.78服务器一起发来恳求批量增加数据时,因为UDX_PKG_DEVICE锁约束了两边的都在等候对方的锁导致。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯时娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章