MySQL Bugs: #51966: INSERT ... SELECT should allow concurrent inserts when binary log is disabled.

Bug #51966	INSERT ... SELECT should allow concurrent inserts when binary log is disabled.
Submitted:	11 Mar 2010 19:08	Modified:	14 Apr 2010 9:00
Reporter:	Julie Silvestri	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1.40-community	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binary log, concurrent inserts, INSERT ... SELECT

Description:
Based on testing in version 5.1.40, even if the binary log is turned off, insert ... select statements still cause concurrent inserts to block on innodb.  

This has caused us some problems, as if we try to update the database with a small change while a long insert ... select is running, we get a timeout error ("Lock wait timeout exceeded; try restarting transaction").

How to repeat:
Create a table, my_table, large enough so that the following queries will take a few seconds.  Create at least two columns, either indexed or not, my_id and my_other_id.

Run

CREATE TABLE my_other_table LIKE my_table;

On two separate connections, run the following queries:

INSERT INTO my_other_table			
  SELECT * FROM my_table;

UPDATE my_table SET my_id = 5;

You should get a timeout exception.

Suggested fix:
allow concurrent insert ... select's when the binary log is disabled.

Thank you for the report.

This looks likd bug #40360, but later should not exist in 5.1.40. Please try current version 5.1.44 and if problem still exists there provide output of SHOW CREATE TABLE my_table and your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".