如何提高INSERT INTO ... SELECT锁定行为 [英] How to improve INSERT INTO ... SELECT locking behavior
问题描述
在生产数据库中,我们每小时运行以下伪代码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的文章中的解决方案,它看起来像是骇客(文件系统上的临时文件似乎很烂).还有其他想法吗?有没有一种方法可以在不复制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
这篇关于如何提高INSERT INTO ... SELECT锁定行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!