InnoDB上的MySQL UPDATE操作偶尔会超时 [英] MySQL UPDATE operations on InnoDB occasionally timeout

问题描述

这些是在InnoDB数据库中很小的表上的简单UPDATE.有时，操作似乎已锁定，并且不会超时.然后，每个随后的UPDATE都以超时结束.现在唯一的办法就是要求我的ISP重新启动守护程序.表中的每个字段都在查询中使用，因此所有字段都被索引了，包括一个主字段.

These are simple UPDATEs on very small tables in an InnoDB database. On occasion, an operation appears to lock, and doesn't timeout. Then every subsequent UPDATE ends with a timeout. The only recourse right now is to ask my ISP to restart the daemon. Every field in the table is used in queries, so all the fields are indexed, including a primary.

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

I'm not sure what causes the initial lock, and my ISP doesn't provide enough information to diagnose the problem. They are reticent about giving me access to any settings as well.

在上一份工作中，我被要求处理类似的信息，但是我会做一个INSERT.定期地，我运行了一个脚本来处理表中的DELETE旧记录，因此不需要过滤太多的记录.当SELECT ing时，我使用了外推技术，因此，除了最新数据之外，还有更多有用的东西.此设置坚如磐石，即使在非常繁重的使用情况下也无法挂起.

In a previous job, I was required to handle similar information, but instead I would do an INSERT. Periodically, I had a script run to DELETE old records from the table, so that not so many records needed to be filtered. When SELECTing I used extrapolation techniques so having more than just the most recent data was useful. This setup was rock solid, it never hung, even under very heavy usage.

我没问题用INSERT和定期的DELETE替换UPDATE，但是看起来很笨拙.有没有人遇到过类似的问题，并且更优雅地解决了这个问题?

I have no problem replacing the UPDATE with an INSERT and periodic DELETEs, but it just seems so clunky. Has anyone encountered a similar problem and fixed it more elegantly?

• max_heap_table_size:16 MiB
• count(*):4(不是错字，有4条记录！)
• innodb_buffer_pool_size:1 GiB

• max_heap_table_size: 16 MiB
• count(*): 4 (not a typo, four records!)
• innodb_buffer_pool_size: 1 GiB

编辑:数据库现在失败； locations有5条记录.下面的示例错误.

Edit: DB is failing now; locations has 5 records. Sample error below.

MySQL查询:

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

MySQL错误:

错误#1205-超出了锁定等待超时；尝试重新启动事务

Error #1205 - Lock wait timeout exceeded; try restarting transaction

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

推荐答案

InnoDB的已知问题，请参见

It's a known issue with InnoDB, see MySQL rollback with lost connection. I would welcome something like innodb_rollback_on_disconnect as mentioned there. What's happening to you is that you're getting connections disconnected early, as can happened on the web, and if this happens in the middle of a modifying query, the thread doing that will hang but retain a lock on the table.

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

Right now, accessing InnoDB directly with web services is vulnerable to these kinds of disconnects and there's nothing you can do within FatCow other than ask them to restart the service for you. Your idea to use MyISAM and low priority is okay, and will probably not have this problem, but if you want to go with InnoDB, would recommend an approach like the following.

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

1) Go with stored procedures, then the transactions are guaranteed to run to completion and not hang in the event of a disconnect. It's a lot of work, but improves reliability big time.

2)不要依赖auto commit，最好将其设置为零，并以BEGIN TRANSACTION和COMMIT显式开始和结束每个事务.

2) Don't rely on auto commit, ideally set it to zero, and explicitly begin and end each transaction with BEGIN TRANSACTION and COMMIT.

这篇关于InnoDB上的MySQL UPDATE操作偶尔会超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！