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：OFF
  • interactive_timeout：28800
  • lock_wait_timeout：31536000
  • net_read_timeout：30
  • net_write_timeout：60
  • slave_net_timeout：3600
  • wait_timeout：28800

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

有什么办法可以减少这些错误吗？

注：以下其他SO答案对我没有帮助：

MySQL锁等待超时

MySQL：“锁等待超时”

如何在通过Python连接时更改默认的Mysql连接超时时间？

- Isma Row

---

你使用哪个数据库引擎？ - Keval

你如何连接到数据库？你使用哪个驱动程序？你使用连接池吗？ - Eduard

我正在使用InnoDB，并且我使用JPA进行持久化。 - Isma Row

Answer 1

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

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

这将显著提高性能并避免超时。

注意：ORM不是大型更新的好解决方案。最好使用标准JDBC。使用ORM检索、更新、删除少量记录。它可以加速编码阶段，但不能提高执行时间。

- Davide Lorenzo MARINO

---

我确定这个可以帮助我，但是，如何进行筛选呢？我的意思是，如果我需要更新一个表的 50,000 行数据，并且这些行与我的条件匹配，而这个表总共有 110,000 行数据，我该如何分解这个查询呢？ - Isma Row

你必须选择一个排序标准。一个好的标准是id，但你可以选择任何你喜欢的标准。 - Davide Lorenzo MARINO

例如 ORDER BY id DESC，您能理解吗？ - Isma Row

我不确定修改数据的来源。如果来源是另一个表，您可以按ID对修改后的数据进行排序。如果修改数据的来源是文件，则可以选择连续的行组。如果数据的来源是Java算法循环，则将结果分组。 - Davide Lorenzo MARINO

好的！我要试一下。也许我可以使用“LIMIT x，y”将整个查询分成几个部分。 - Isma Row

如果修改数据的源是查询，您可以使用limit进行拆分。 - Davide Lorenzo MARINO

Answer 2

作为评论而非答案，如果您处于开发的早期阶段，您可能需要考虑是否实际上需要将这个特定的数据存储在关系型数据库中。根据数据的计划使用情况，存储移动应用程序数据的更快速和更大的替代方案是存在的。[对于大文件，只需存储一次，经常读取（并且可以缓存）的S3；对于非结构化大量写入、多次读取等情况的NoSQL（Mongo等）]

- George J. Nassef Jr.