MySQL Amazon RDS: 锁等待超时

Question

在Mysql（Amazon RDS）上，当我尝试运行以下SQL查询时：

UPDATE
    table1 INNER JOIN table2 USING (CommonColumn)
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y

我持续约52秒后不断地遇到这个错误：

Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

我该如何解决这个问题？

表2有大约1700万条记录，而作为表1子集的表2有400万条记录。是否表的大小是问题所在，还是我的查询存在问题？

- ErJab

Answer 1

如果您正在使用InnoDB引擎，请尝试增加默认的IO线程数。RDS默认只有4个，这太少了。要使用新值，您需要重新启动DB实例以使参数组生效，因为它们不是“动态的”（需要重启）。

show variables like 'innodb_%io_threads'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_read_io_threads | 4 | | innodb_write_io_threads | 4 | +-------------------------+-------+

- Slawomir

---

我无法在RDS aurora上编辑它，“Variable 'innodb_write_io_threads'是只读变量”。 - Jordan

Aurora DB是一种类似于MySQL的专有产品。它的配置变量可能会受到保护，不兼容或者直接用于展示。上述说明适用于托管的MySQL实例（RDS MySQL）。您需要联系Aurora支持团队以解决您的具体问题。 - Slawomir

评论来得有些晚了，但是 RDS 参数可以从 AWS 控制台进行编辑。某些更改可能需要重新启动。 - anup

Answer 2

在AWS RDS上，我的DB实例已经填满，这导致客户端出现了这个错误。

- uberdog

Answer 3

我重新启动了MySQL实例，同样的查询起作用了。

- ErJab

---

就像我告诉你的那样，有人锁定了其中一些资源。因此，重新启动关闭了所有连接并释放了这些资源 :P - jorgeu

问题仍然存在怎么办？ - Robson

如果问题仍然存在，可以使用innotop查看是什么阻塞了锁。然后，您可以关闭与阻塞锁相关联的连接（在5.5版本中杀死有问题的进程可能会导致mysqld崩溃）。 - ives