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

[22 Jun 2006 21:14] Dmitry Filonov
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.
[23 Jun 2006 7:43] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE test_table; results. What storage engine is used for the table?
[23 Jun 2006 15:32] Dmitry Filonov
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.
[23 Jun 2006 15:44] Valeriy Kravchuk
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?
[23 Jun 2006 15:48] Dmitry Filonov
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.
[20 Jul 2006 17:39] Valeriy Kravchuk
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.
[29 Nov 2006 13:22] Bugs System
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).
[29 Nov 2006 17:56] Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[30 Nov 2006 1:30] Konstantin Osipov
Fixed in 5.0.32 and 5.1.13
[30 Nov 2006 20:02] Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs.

CONCURRENT did not work correctly for LOAD DATA INFILE.
[21 Dec 2006 23:16] Bugs System
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).
[29 Jan 2007 16:10] Andrew Duffy
This really needs to be made available in a binary community release. Users on platforms like Windows and Solaris do not compile software.