mysql update超时_InnoDB上的MySQL UPDATE操作偶尔会超时

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.

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.

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.

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?

Current Configuration

max_heap_table_size: 16 MiB

count(*): 4 (not a typo, four records!)

innodb_buffer_pool_size: 1 GiB

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

MySQL query:

UPDATE locations SET x = "43.630181733", y = "-79.882244160", updated = NULL

WHERE uuid = "6a5c7e9d-400f-c098-68bd-0a0c850b9c86";

MySQL error:

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

解决方案

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.

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) 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) Don't rely on auto commit, ideally set it to zero, and explicitly begin and end each transaction with BEGIN TRANSACTION and COMMIT.