MySQL锁等待超时和死锁错误

Question

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

我们在有很多行( 400.000到3.000.000之间)的数据库表中有一些插入和更新操作。每个操作通常不需要访问表的每个寄存器，但可能会同时调用它们来更新其中的20%。

有时我会犯这样的错误：

Deadlock found when trying to get lock; try restarting transaction

和

Lock wait timeout exceeded; try restarting transaction

我已经改进了我的查询，使它们变得更小、更快，但是当一些操作无法执行时，我仍然有一个大问题。

到目前为止，我的解决办法是：

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

​

但我不确定这些事情是否降低了性能。

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

注意:这些其他的答案对我没有帮助：

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检索、更新、删除少量记录。它加快了编码阶段，而不是执行时间。

Answer 2

作为一种评论而不仅仅是回答，如果您正处于开发的早期阶段，您可能希望考虑在关系数据库中是否确实需要这些特定的数据。根据数据的计划使用情况，可以更快更大地存储移动应用程序中的数据。S3用于大文件，存储一次，经常读取(并且可以缓存)；NoSQL (Mongo等)用于非结构化大文件，写一次，读多，等等。