如何改进 INSERT INTO ... SELECT 锁定行为

2021-12-19 00:00:00 select insert locking mysql innodb

在我们的生产数据库中，我们每小时运行以下伪代码 SQL 批处理查询:

In our production database, we ran the following pseudo-code SQL batch query running every hour:

INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
     WHERE allKindsOfComplexConditions are true)

现在这个查询本身不需要很快，但我注意到它正在锁定 HighlyContentiousTableInInnoDb，即使它只是从中读取.这使得其他一些非常简单的查询需要大约 25 秒(这是其他查询需要的时间).

Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes).

然后我发现这种情况下的 InnoDB 表实际上是被 SELECT 锁定的！https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

但我不太喜欢选择进入 OUTFILE 的文章中的解决方案，这似乎是一个 hack(文件系统上的临时文件似乎很糟糕).还有其他想法吗?有没有办法制作 InnoDB 表的完整副本，而无需在复制期间以这种方式锁定它.然后我可以将 HighlyContentiousTable 复制到另一个表并在那里进行查询.

But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTable to another table and do the query there.

推荐答案

这个问题的答案现在容易多了:- 使用基于行的复制和读提交隔离级别.

The answer to this question is much easier now: - Use Row Based Replication and Read Committed isolation level.

您遇到的锁定消失了.

更长的解释:http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

相关文章