*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2023-09-0813:02:430x7f224b8f0700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 0 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1913166 srv_active, 0 srv_shutdown, 1788 srv_idle srv_master_thread log flush and writes: 1914954 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 36736282 OS WAIT ARRAY INFO: signal count 286633583 RW-shared spins 0, rounds 192061476, OS waits 6425935 RW-excl spins 0, rounds 612396810, OS waits 6259209 RW-sx spins 50861198, rounds 296338326, OS waits 1184937 Spin rounds per wait: 192061476.00 RW-shared, 612396810.00 RW-excl, 5.83 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-09-0811:58:010x7f224c6df700 *** (1) TRANSACTION: TRANSACTION 1818540369, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 1315 lock struct(s), heap size 123088, 46885 row lock(s), undo log entries 3134 MySQL thread id 21203269, OS thread handle 139785450133248, query id 22714701081127.0.0.1 databases updating
DELETE FROM table_order WHERE `status` > 2 AND `is_archive` = 1 AND `update_time` < 1694173681653 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540369 lock_mode X waiting Record lock, heap no 31 PHYSICAL RECORD: n_fields 54; compact format; info bits 0 0: len 8; hex 0000000005xxxxx; asc o ;;
*** (2) TRANSACTION: TRANSACTION 1818540612, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 9 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 20 MySQL thread id 21199366, OS thread handle 139785287890688, query id 22714706392 127.0.0.1 databases updating
UPDATE table_order SET `status` = 2 `update_time` = 1694174281908 WHERE `uid` = '2748' AND `order_id` = 91205746 AND `status` in ( 1, 2 ) AND `num` >= 69
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540612 lock_mode X locks rec but not gap Record lock, heap no 31 PHYSICAL RECORD: n_fields 54; compact format; info bits 0 0: len 8; hex 00000000056xxxx; asc o ;;
Record lock, heap no 32 PHYSICAL RECORD: n_fields 54; compact format; info bits 0 0: len 8; hex 000000000xxxx; asc o ;;
Record lock, heap no 38 PHYSICAL RECORD: n_fields 54; compact format; info bits 0 0: len 8; hex 00000000056fb08b; asc o ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 484 page no 5890 n bits 112 index PRIMARY of table `sdatabase`.`table_order` trx id 1818540612 lock_mode X locks rec but not gap waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 54; compact format; info bits 0 0: len 8; hex 00000000056fb072; asc o r;;
从上面的日志中可以看出到两张同表的死锁
SQL 1 (HOLDS THE LOCK(S))
UPDATE table_order SET `status` = 2 `update_time` = 1694174281908 WHERE `uid` = '2748' AND `order_id` = 91205746 AND `status` in ( 1, 2 ) AND `num` >= 69 `` ### SQL 2 (WAITING FOR THIS LOCK TO BE GRANTED)
DELETE FROM table_order WHERE status > 2 AND is_archive = 1 AND update_time < 1694173681653
