li.siyuan 2022-08-04 10:39 采纳率: 100%
浏览 38
已结题

mysql 死锁 Deadlock found when trying to get lock; try restarting transaction

运行结果及报错内容

程序中报错 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 条件中 无索引

  • 写回答

1条回答 默认 最新

  • 三千烦恼丝xzh 2022-08-04 11:51
    关注

    你应该带上数据库版本和事务隔离级别,这么容易死锁我猜就是RC隔离

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 8月15日
  • 已采纳回答 8月7日
  • 创建了问题 8月4日

悬赏问题

  • ¥15 求一下解题思路,完全不懂
  • ¥15 tensorflow
  • ¥15 densenet网络结构中,特征以cat方式复用后是怎么进行误差回传的
  • ¥15 STM32G471芯片spi设置了8位,总是发送16位
  • ¥15 R语言并行计算beta-NTI中tree文件的类型
  • ¥15 如何解读marsbar导出的ROI数据?
  • ¥20 求友友协助弄一下基于STC89C52单片机的声光控制灯原理图
  • ¥15 arduino双向交通灯设计
  • ¥15 有没有会粒子群算法的大能(○゜ε^○)求带不会出收敛图😭
  • ¥15 Matlab读取根元素出错