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