通过MYSQL日志定位死锁有关问题

通过MYSQL日志定位死锁问题

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 meizu_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 `MEIZU_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 meizu_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 `MEIZU_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 `MEIZU_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锁限制了双方的都在等待对方的锁导致。