Concurrent inserts on MyISAM and the binary log

Concurrent inserts on MyISAM and the binary log

PREVIOUS POST
NEXT POST

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,

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:

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

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (7)

  • peter Reply

    Baron,

    What if you would use Row Level Replication in MySQL 5.1 – will this lock go away then ?

    May 14, 2008 at 5:27 pm
  • Baron Schwartz Reply

    I tried setting binlog_format=row in my.cnf, verified that it worked, and then ran an INSERT and an INSERT..SELECT. The results were the same: this code does not seem to be affected by the binary logging format at all.

    May 14, 2008 at 6:44 pm
  • peter Reply

    Right. Looks like potential future improvement 🙂

    May 15, 2008 at 2:18 pm
  • Rob Reply

    Do you mean use innodb with 5.1? On 5.0 the behaviour is the same for innodb. I don’t think you can get around the issue without running with “innodb_locks_unsafe_for_binlog=1” which was mentioned here:

    http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

    May 20, 2008 at 12:36 pm
  • esudnik Reply

    To avoid table locks with MyISAM I use INSERT DELAYED (http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html) statements.

    June 19, 2008 at 5:24 am
  • esudnik Reply

    …of course it is not a solution for all cases 😉

    June 19, 2008 at 5:25 am
  • WB Reply

    Maybe, using SET SQL_LOG_BIN=0, this problem con’t be solved?

    November 4, 2008 at 4:56 am

Leave a Reply