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,
|
1 |
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:
|
1 |
Locked - read Low priority read lock |
But on INSERT…SELECT, you’ll see this:
|
1 |
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.
Baron,
What if you would use Row Level Replication in MySQL 5.1 – will this lock go away then ?
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.
Right. Looks like potential future improvement 🙂
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/
To avoid table locks with MyISAM I use INSERT DELAYED (http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html) statements.
…of course it is not a solution for all cases 😉
Maybe, using SET SQL_LOG_BIN=0, this problem con’t be solved?