先来查看 死锁日志
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2023-09-08 13:02:43 0x7f224b8f0700 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-08 11:58:01 0x7f224c6df700
*** (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 22714701081 127.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
先下表索引PRIMARY KEY (order_id),
KEY idx_origin_ts (status,num),
KEY idx_uid (uid),
从上面的表索引 可以看出 `SQL 1` 使用的是 主键索引 拿锁也是行级锁.
`SQL 2` 理论上应该能用到 `idx_origin_ts` 索引, 但实际上并没有用到索引, `SQL2`是没用的索引.
没用到索引的原因比较简单 因为字段重复内容过多 基数较大索引, mysql优化器认为全表扫描比走索引快，它就会放弃索引。
解决问题就简单了, 偷懒弄个覆盖索引 `status`,`is_archive` , `update_time` 构建联合索引