MySQL Bugs: #20637: "load data concurrent infile" locks the table

Bug #20637	"load data concurrent infile" locks the table
Submitted:	22 Jun 2006 21:14	Modified:	30 Nov 2006 20:02
Reporter:	Dmitry Filonov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.25-BK, 5.0.19-5.0.21	OS:	Linux (Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any
Tags:	backport_050030SP1, bfsm_2006_11_02

Description:
mysql server versions 5.0.19 to 5.0.21 is locking tables when performing "load data concurrent infile ....". It was OK with 5.0.18, but starting with 5.0.19 you can not execute select queries during load (table is locked), which is a problem in some cases. 

Was tested on several linux boxes with a fresh created table to ensure there are no holes in existing database. 

How to repeat:
TRUNCATE TABLE test_table;
LOAD DATA CONCURRENT INFILE 'test_data' INTO TABLE test_table; 

during load from another thread do 

SELECT COUNT(*) FROM test_table; 

on 5.0.18 you get 0 right away. In 5.0.19 you have to wait until LOAD DATA finishes.

Thank you for a problem report. Please, send the SHOW CREATE TABLE test_table; results. What storage engine is used for the table?

CREATE TABLE `test_table` (
  `pid` int(10) NOT NULL auto_increment,
  `author_id` mediumint(8) NOT NULL default '0',
  `author_name` varchar(32) NOT NULL default '',
  `real_id` mediumint(8) NOT NULL default '0',
  `real_name` varchar(32) NOT NULL default '',
  `use_sig` tinyint(1) NOT NULL default '0',
  `use_emo` tinyint(1) NOT NULL default '0',
  `ip_address` varchar(16) NOT NULL default '',
  `post_date` int(10) NOT NULL default '0',
  `last_update` int(10) NOT NULL default '0',
  `icon_id` smallint(3) NOT NULL default '0',
  `file_id` bigint(20) NOT NULL default '0',
  `queued` tinyint(1) NOT NULL default '0',
  `topic_id` int(10) NOT NULL default '0',
  `forum_id` smallint(5) NOT NULL default '0',
  `attach_size` varchar(20) NOT NULL default '',
  `new_topic` tinyint(1) NOT NULL default '0',
  `quoted_pid` bigint(20) NOT NULL default '0',
  `edit_name` varchar(32) NOT NULL default '',
  `rating_total` int(11) NOT NULL default '0',
  `rating_total_voters` int(11) NOT NULL default '0',
  `edit_time` int(10) NOT NULL default '0',
  `append_edit` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`pid`),
  KEY `post_date` (`post_date`),
  KEY `forum_id` (`forum_id`,`topic_id`,`new_topic`),
  KEY `queued` (`queued`,`forum_id`,`topic_id`,`post_date`),
  KEY `author_id` (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

As you can see it's MyISAM table.

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/load-data.html):

"If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing."

So, are you sure your table has "no free blocks in the middle"? Can you demonstrate the same behaviour with initially empty table, where you just inserted some data into before LOAD?

Absolutely. That's why I put TRUNCATE into "how to repeat" section. 
I do LOAD DATA on the empty table. No data at all. 

Again, mysql server prior to 5.0.19 has no problem with the same script. 

P.S. Just tried 5.0.22 - same problem.

Verified with 5.0.25-BK on Linux. Like that:

create table tload(c1 int auto_increment primary key, c2 char(10)) ENGINE=MyISAM;
load data concurrent infile '/tmp/tload.txt' into table tload;

While this statement is running:

select count(*) from tload;

from another session is waiting. So, even for absolutely empty table CONCURRENT does not work as documented.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16103

ChangeSet@1.2303, 2006-11-29 15:51:53+03:00, kroki@moonlight.intranet +1 -0
  BUG#20637: "load data concurrent infile" locks the table
  
  Note that we ignore CONCURRENT if LOAD DATA CONCURRENT is used from
  inside a stored routine and MySQL is compiled with Query Cache support
  (this is not in the manual).
  
  The problem was that the condition test of "we are inside stored routine"
  was reversed, thus CONCURRENT _worked only_ from stored routine.  The
  solution is to use proper condition test.
  
  No test case is provided because the test case would require a large
  amount of input, and it's hard to tell is SELECT is really blocked or
  just slow (subject to race).

Queued to 5.0-runtime and 5.1-runtime.

Fixed in 5.0.32 and 5.1.13

Noted in 5.0.32, 5.1.14 changelogs.

CONCURRENT did not work correctly for LOAD DATA INFILE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17287

ChangeSet@1.2308, 2006-12-22 00:16:34+01:00, msvensson@neptunus.(none) +1 -0
  BUG#20637: "load data concurrent infile" locks the table
    
  Note that we ignore CONCURRENT if LOAD DATA CONCURRENT is used from
  inside a stored routine and MySQL is compiled with Query Cache support
  (this is not in the manual).
    
  The problem was that the condition test of "we are inside stored routine"
  was reversed, thus CONCURRENT _worked only_ from stored routine.  The
  solution is to use proper condition test.
    
  No test case is provided because the test case would require a large
  amount of input, and it's hard to tell is SELECT is really blocked or
  just slow (subject to race).

This really needs to be made available in a binary community release. Users on platforms like Windows and Solaris do not compile software.