mysql - 使用主键分块从大表中删除时仍然看到锁定等待超时

Question

我有一个分布式应用程序，可以将数百万条记录记录到 MySQL。有时一天或一周达到一百万，具体取决于用户。

我最近重写了一个自动删除过时记录的“清除”系统。它每 12 小时运行一次，并根据用户设置的规则清除数据。由于数据库平均通常包含 50+ 百万条记录，因此我将查询设计为使用 primary key chunking .

每个删除查询仅通过主键扫描有限数量的行。据我了解，这减少了“包含”其他 where 条件所需的锁的数量。下一个删除查询会在几秒钟后运行。

但是，我们的许多用户仍然看到“锁定等待超时”，并且他们总是指向清除查询。

DELETE FROM prism_data WHERE prism_data.id >= 7564001 AND prism_data.id < 7568001 AND prism_data.epoch <= '1388566847'

这是引擎状态报告的一部分:

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1248, 1 row lock(s)
MySQL thread id 458, OS thread handle 0x7efed0c62700, query id 779832 localhost 127.0.0.1 prism updating
DELETE FROM prism_data WHERE prism_data.id >= 7564001 AND prism_data.id < 7568001 AND prism_data.epoch <= '1388566847'
------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 606 n bits 1272 index `epoch` of table `prism`.`prism_data` trx id 208A7E lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 52d7d976; asc R  v;;
 1: len 4; hex 00000001; asc     ;;

如你所见，这是表的架构:

CREATE TABLE IF NOT EXISTS `prism_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `epoch` int(10) unsigned NOT NULL,
  `action_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `world_id` int(10) unsigned NOT NULL,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `z` int(11) NOT NULL,
  `block_id` mediumint(5) DEFAULT NULL,
  `block_subid` mediumint(5) DEFAULT NULL,
  `old_block_id` mediumint(5) DEFAULT NULL,
  `old_block_subid` mediumint(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `epoch` (`epoch`),
  KEY `location` (`world_id`,`x`,`z`,`y`,`action_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

增加锁定等待超时通常会有所帮助，但令人惊讶的是，减少每次清除查询扫描的记录范围似乎没有什么不同。有些用户无权更改 mysql 设置。我可以做些什么来改进我们删除记录的方式以避免导致锁定等待超时？

更新每条评论的附加信息:

我们的一位用户报告了这个错误:

[13:43:47 INFO]: [Prism]: Database connection error: Lock wait timeout exceeded; try restarting transaction
[13:43:47 WARN]: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
[13:43:47 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[13:43:47 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[13:43:47 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
[13:43:47 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
[13:43:47 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
[13:43:47 WARN]:        at me.botsko.prism.actionlibs.ActionsQuery.delete(ActionsQuery.java:346)
[13:43:47 WARN]:        at me.botsko.prism.purge.PurgeTask.run(PurgeTask.java:84)

然后立即运行 SHOW FULL PROCESSLIST，它只显示一个事件的清除查询:

http://i.imgur.com/DxL4Rk1.png

Link to code mentioned in error

Answer 1

您可以使用 INFORMATION_SCHEMA.LOCK_WAITS 找到阻止您删除的源查询和 INNODB_TRX 表。

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;

查看更多信息 http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema.html#innodb-information-schema-examples ，在“示例 14.2 识别阻塞事务”下。

---

回复你的评论和截图:

因为 blocking_query 是 NULL，这向我暗示另一个线程完成了它的查询，但保留了它的锁。

事务将保留其锁直到事务结束，即使它不再处理任何给定查询也是如此。

您应该在事务完成后立即提交或回滚事务。这将减少锁定的持续时间，并减少阻塞其他线程的机会。

---

另一个提示:听起来您开发了与 pt-archiver 相同的工具.例如:

$ pt-archiver h=localhost,D=mydatabase,t=prism_data
    --purge --bulk-delete --commit-each --limit 1000 --where "epoch <= 1388566847"

将根据需要遍历尽可能多的 block ，以 1000 行的 block 为单位，每次都提交。