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

Question

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

我不确定最初锁定的原因，我的ISP也没有提供足够的信息来诊断问题。他们也对允许我访问任何设置保持沉默。

在以前的工作中，我被要求处理类似的信息，但我会做INSERT。我定期运行一个脚本，从表中DELETE个旧记录，这样就不需要过滤那么多记录。当SELECTing时，我使用了外推技术，因此拥有的不仅仅是最新的数据是有用的。这种设置坚如磐石，即使在使用量很大的情况下也从未悬挂过。

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

当前配置

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

编辑：DB现在失败；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回滚丢失连接。我欢迎这里提到的innodb_rollback_on_disconnect。发生在您身上的情况是，您很早就断开了连接，这在web上可能会发生，如果这种情况发生在修改查询的过程中，那么执行此操作的线程将挂起，但会保留表上的锁。

现在，使用web服务直接访问InnoDB很容易受到这种断开连接的影响，在FatCow中，除了要求他们为您重新启动服务之外，您别无选择。你使用MyISAM和低优先级的想法是可以的，可能不会有这个问题，但如果你想使用InnoDB，建议使用以下方法。

1) 使用存储过程，然后保证事务运行到完成，并且在断开连接时不会挂起。这是一项艰巨的工作，但大大提高了可靠性。

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

Answer 2

transaction1> START TRANSACTION;
transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE;
+------+
| i |
+------+
| 21 |
| 25 |
| 30 |
+------+

---

transaction2> START TRANSACTION;
transaction2> INSERT INTO t VALUES(26);
transaction2> COMMIT;
transaction1> select * from t where i > 20 FOR UPDATE;

---

+------+
| i |
+------+
| 21 |
| 25 |
| 26 |
| 30 |
+------+

什么是间隙锁？

1. 间隙锁是索引记录之间间隙的锁。幸亏这个间隙锁，当你运行同一个查询两次时，你会得到相同的结果，而不管该表上的其他会话修改。

2. 这使读取保持一致，从而使复制服务器之间的一致性。如果执行SELECT*FROM id>1000对于UPDATE两次，您希望获得两次相同的值。

3. 为了实现这一点，InnoDB锁定带有排他锁的WHERE子句以及它们之间的间隙共享间隙锁。

此锁不仅影响SELECT…FOR UPDATE。这是一个DELETE语句的例子：

transaction1 > SELECT * FROM t;
+------+
| age |
+------+
| 21 |
| 25 |
| 30 |
+------+

启动交易并删除记录25:

transaction1 > START TRANSACTION;
transaction1 > DELETE FROM t WHERE age=25;

在这一点上，我们假设只有记录25被锁定。然后，我们尝试在第二个会话中插入另一个值：

transaction2 > START TRANSACTION;
transaction2 > INSERT INTO t VALUES(26);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(29);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(31);
Query OK, 1 row affected (0.00 sec)

在第一个会话上运行delete语句后，不仅受影响的索引记录被锁定，而且该记录前后的间隙也被锁定，共享间隙锁定防止将数据插入其他会话。

Answer 3

如果您的UPDATE实际上是：

UPDATE locations SET updated = NULL;

您正在锁定表中的所有行。如果您在保留所有行的锁的同时放弃事务，当然所有行都将保持锁定状态。InnoDB在您的环境中并不"不稳定"，它似乎正按照您的要求行事。您不需要放弃未结交易。