MySQL锁等待超时和死锁错误

Question

我正在开发一个移动应用程序，其后端是用 Java 开发的，数据库是 MySQL。

我们在包含大量行(400.000 到 3.000.000 之间)的数据库表中进行一些插入和更新操作。每个操作通常不需要触及表的每个寄存器，但也许它们会被同时调用以更新其中的 20%。

有时我会遇到这样的错误:

尝试获取锁时发现死锁；尝试重启事务

和

超过锁定等待超时；尝试重启事务

我改进了我的查询，使它们更小、更快，但当某些操作无法执行时，我仍然遇到一个大问题。

到目前为止，我的解决方案是:

• 提高服务器性能(AWS 实例从 m2.large 到 c3.2xlarge)
• SET GLOBAL tx_isolation = 'READ-COMMITTED';
• 避免检查外键:SET FOREIGN_KEY_CHECKS = 0;(我知道这不安全，但我的首要任务不是锁定数据库)
• 为超时变量设置此值(SHOW VARIABLES LIKE '%timeout%';):
  • connect_timeout:10
  • delayed_insert_timeout:300
  • innodb_lock_wait_timeout:50
  • innodb_rollback_on_timeout:关闭
  • interactive_timeout:28800
  • lock_wait_timeout:31536000
  • net_read_timeout:30
  • net_write_timeout:60
  • slave_net_timeout: 3600
  • wait_timeout:28800

但我不确定这些东西是否降低了性能。

知道如何减少这些错误吗？

注意:这些其他的 SO 回答对我没有帮助:

MySQL Lock wait timeout exceeded

MySQL: "lock wait timeout exceeded"

How can I change the default Mysql connection timeout when connecting through python?

Answer 1

尝试在每个事务中更新较少的行。

不是在单个事务中更新 20% 或行，而是更新 1% 的行 20 次。

这将显着提高您的表现，并且您将避免超时。

注意:ORM 不是大更新的好解决方案。最好使用标准 JDBC。使用 ORM 每次检索、更新、删除少量记录。它加快了编码阶段，而不是执行时间。