js里执行mysql锁_MySQL锁系列（9）之 long transaction_mysql

1. 一开始，所有的故障排除全部来自监控系统和show processlist，然后查看锁的神器没有使用，就是show engine innodb status /G ---TRANSACTION 51055827249, ACTIVE 20 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 1060068541, OS thread handle 0x7fba06c6c700, query id 55990809665 xx aea updating UPDATE approve SET `operator` = '0',`operator_name` = 'system',`comment` = '离职',`status` = '1' WHERE (`id` = '49384') ------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 746 page no 624 n bits 216 index `PRIMARY` of table `aea`.`approve` trx id 51055827249 lock_mode X locks rec but not gap waiting Record lock, heap no 148 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 4; hex 8000c0e8; asc ;; 1: len 6; hex 000be32a10cb; asc * ;; 2: len 7; hex 7a000004540557; asc z T W;; 3: len 4; hex 80002884; asc ( ;; 4: len 6; hex e69da8e58b87; asc ;; 5: len 6; hex 3b363430353b; asc ;6405;;; 6: len 19; hex 7979616e6740616e6a756b65696e632e636f6d; asc yy.com;; 7: len 1; hex 81; asc ;; 8: len 2; hex 8015; asc ;; 9: len 2; hex 8001; asc ;; 10: len 1; hex 80; asc ;; 11: len 4; hex 80000001; asc ;; 12: len 6; hex 73797374656d; asc system;; 13: len 6; hex e7a6bbe8818c; asc ;; 14: len 4; hex 59a4c993; asc Y ;; 15: len 4; hex 80000000; asc ;; 16: len 1; hex 80; asc ;; 17: len 1; hex 81; asc ;; 18: len 1; hex 81; asc ;; ------------------ ---TRANSACTION 51055825099, ACTIVE 21 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 1060025172, OS thread handle 0x7fba05ad0700, query id 55990809629 xx aea cleaning up 2. 通过以上片段信息可以得知如下结论 2.1 UPDATE approve 语句等待主键索引的record lock，lock_mode X locks rec but not gap ， space id 746 page no 624， 记录为主键49384的row 2.2 TRANSACTION 51055827249, ACTIVE 20 sec , 这个事务持续20秒 2.3 TRANSACTION 51055825099, ACTIVE 21 sec , 这个事务持续21秒，根据这个信息，很有可能由于这个事务持有UPDATE approve需要的record lock 2.4 TRANSACTION 51055825099, 1 row lock(s) , 根据这个信息，可以更进一步推论出该事务，该thread id 1060025172 持有该记录锁。 3. 很可惜，并不知道是什么SQL语句，说明已经执行完毕