运行结果及报错内容
程序中报错 Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
数据库死锁日志:
SHOW ENGINE INNODB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-08-04 05:01:26 0x7feefb651700
*** (1) TRANSACTION:
TRANSACTION 106317343, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 26764, OS thread handle 140664341653248, query id 152316867 127.0.0.1 meita-zhenjian updating
UPDATE table1
SET
toreadflag = 1
WHERE
id = 758489
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 534 page no 117 n bits 160 index PRIMARY of table `db1`.`table1` /* Partition `p90` */ trx id 106317343 lock_mode X locks rec but not gap waiting
Record lock, heap no 94 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 106317342, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 26758, OS thread handle 140664396650240, query id 152316868 127.0.0.1 meita-zhenjian updating
UPDATE table1
SET
toreadflag = 1
WHERE
id = 893363
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 534 page no 117 n bits 160 index PRIMARY of table `db1`.`table1` /* Partition `p90` */ trx id 106317342 lock_mode X locks rec but not gap
Record lock, heap no 94 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 508 page no 199 n bits 136 index PRIMARY of table `db1`.`table1` /* Partition `p64` */ trx id 106317342 lock_mode X locks rec but not gap waiting
Record lock, heap no 70 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (2)
我的解答思路和尝试过的方法
之前感觉是表数据量太大, 多线程不停的update 数据,
索引更新时短暂的 锁表导致 此报错的出现,
后 将表分区, 但仍然出现这个报错此表 下 id 为 自增主键, 共有 3个索引
1个主键 索引
2个普通索引
update 语句中 where 条件为 id 是主键索引
set 条件中 无索引
