Concurrent inserts on MyISAM and the binary log

最新推荐文章于 2025-09-10 20:20:06 发布

weixin_30413739

最新推荐文章于 2025-09-10 20:20:06 发布

阅读量60

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/xiaotengyi/p/4202599.html

本文探讨了在MyISAM表中并发插入与SELECT操作遇到的问题，特别是当INSERT...SELECT语句运行时，如何导致并发插入阻塞。文章深入分析了原因，并提供了几种可能的解决方案，包括使用OUTFILE和INFILE技巧、切换到InnoDB存储引擎或应用特定的复杂解决方案。

Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status.

My first thought was that the customer had deleted from the table, which leaves “holes” in the middle of it and prevents concurrent inserts. (You can configure the server to permit concurrent inserts even when there are holes, but it’s disabled by default.) However, that turned out not to be the cause; the table was only inserted into (and selected from). Instead, the blocked statements were because of INSERT… SELECT statements that were running against the table, selecting data from it and inserting into another table.

Let’s look at what happens here: suppose you have two tables tbl1 and tbl2 and concurrent inserts into tbl2 are running fine. If you now run the following query,

INSERT INTO tbl1 SELECT * FROM tbl2

The concurrent inserts into tbl2 can block. This happens if you have the binary log enabled. If you think about it, this makes sense and is correct behavior. The statements have to be serialized for the binary log; otherwise replaying the binary log can result in a different order of execution.

The MySQL manual actually says this, but not in the clearest way. It just says

If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE … SELECT or INSERT … SELECT statements.

If you use mysqladmin debug, you’ll see an ordinary SELECT gets a lock on the table like this:

Locked - read         Low priority read lock

But on INSERT…SELECT, you’ll see this:

Read lock  without concurrent inserts

That read lock is what’s blocking the concurrent inserts from happening.

There’s no solution to this, if you need the binary log enabled. (It needs to be enabled for replication.) There are workarounds, though. You can use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something more elaborate and application-specific, but that’s a topic for another post.

参考：

http://www.percona.com/blog/2008/05/14/concurrent-insert-select-myisam/

转载于:https://www.cnblogs.com/xiaotengyi/p/4202599.html