InnoDB 上的 MySQL UPDATE 操作偶尔会超时

Question

这些是 InnoDB 数据库中非常小的表上的简单 UPDATE。有时，操作似乎已锁定，但不会超时。然后每个后续 UPDATE 都以超时结束。现在唯一的办法是让我的 ISP 重新启动守护程序。表中的每个字段都用于查询，因此所有字段都有索引，包括主字段。

我不确定是什么原因导致初始锁定，而且我的 ISP 没有提供足够的信息来诊断问题。他们也不愿让我访问任何设置。

在之前的工作中，我被要求处理类似的信息，但我会做 INSERT。我定期运行一个脚本来从表中DELETE 旧记录，这样就不需要过滤太多记录。当 SELECTing 时，我使用了外推技术，因此拥有的不仅仅是最新的数据是有用的。这个设置非常可靠，它永远不会挂起，即使在非常高的使用率下也是如此。

用 INSERT 和定期 DELETE 替换 UPDATE 没问题，但它看起来太笨重了。有没有人遇到过类似的问题并更优雅地解决了它？

当前配置

• max_heap_table_size:16 MiB
• count(*):4(不是打字错误，是四条记录!)
• innodb_buffer_pool_size:1 GiB

编辑:数据库现在失败了； locations 有 5 条记录。以下示例错误。

MySQL查询:

UPDATE locations SET x = "43.630181733", y = "-79.882244160", updated = NULL
    WHERE uuid = "6a5c7e9d-400f-c098-68bd-0a0c850b9c86";

MySQL 错误:

错误 #1205 - 超过锁定等待超时；尝试重启交易

locations
Field      Type         Null  Default
uuid       varchar(36)  No
x          double       Yes    NULL
y          double       Yes    NULL
updated    timestamp    No     CURRENT_TIMESTAMP 


Indexes:
Keyname    Type     Cardinality  Field
PRIMARY    PRIMARY  5            uuid
x          INDEX    5            x
y          INDEX    5            y
updated    INDEX    5            updated

Answer 1

这是 InnoDB 的一个已知问题，请参阅 MySQL rollback with lost connection .我会欢迎那里提到的像 innodb_rollback_on_disconnect 这样的东西。发生在您身上的是您提前断开了连接，就像在 Web 上发生的那样，如果这种情况发生在修改查询的中间，执行该操作的线程将挂起但会保留对表的锁定。

现在，直接使用 Web 服务访问 InnoDB 容易受到此类断开连接的影响，除了要求他们为您重新启动服务外，您在 FatCow 中无能为力。您使用 MyISAM 和低优先级的想法是可以的，并且可能不会出现此问题，但是如果您想使用 InnoDB，建议采用如下方法。

1) 使用存储过程，然后保证事务运行完成并且在断开连接的情况下不会挂起。这是很多工作，但会大大提高可靠性。

2) 不要依赖auto commit，最好将其设置为零，并使用BEGIN TRANSACTION 和COMMIT .