Bug #25103 myisam concurrent inserts lock table
Submitted: 15 Dec 2006 20:14 Modified: 16 Dec 2006 12:42
Reporter: Matt Ryan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.18+ OS:Windows (windows)
Assigned to: CPU Architecture:Any
Tags: concurrent, lock, myisam

[15 Dec 2006 20:14] Matt Ryan
Description:
We have been using older versions of mysql without any problem (5.0.17)

I updated our test server to 5.0.27

scenario is.. temp table performs data conversion, master table is never deleted/updated, only new records insert, insert takes 30-40 minutes for bulk loads

under old mysql versions we had no table locking

under 5.0.27 the select locks, and after a few minutes I get this error

select * from table limit 1;
ERROR 5 (HY000): Out of memory (Needed 1371552 bytes)

Job running that causes the lock is, load data concurrent infile 'file' ignore into table

did something change? Do we need to switch to innodb (WAY slow in test) to do concurrent inserts & reads? I never delete from the tables, and concurrent was supposed to allow insert/read at the same time as long as there's no data holes, and there are none in my case, a delete statement has never been ran on this table, never will it's all historical data.

from the documentation..

For a |MyISAM| table, you can use concurrent inserts to add rows at the same time that |SELECT| statements are running if there are no deleted rows in middle of the table.

is this no longer the case?

I tried making it do inserts in batch form, ie 250 records at a time, but a 30 min load turned into a 3 hour load using that method, and it locks tables longer than I like.

We have been using older versions of mysql without any problem (5.0.19)

I updated our test server to 5.0.27

senario is.. temp table performs data conversion, master table is never deleted/updated, only new records insert, insert takes 30-40 minutes for bulk loads

under old mysql versions we had no table locking

under 5.0.27 the select locks, and after a few minutes I get this error

select * from table limit 1;
ERROR 5 (HY000): Out of memory (Needed 1371552 bytes)

Job running that causes the lock is, load data concurrent infile 'file' ignore into table

did something change? I never delete from the tables, and concurrent was supposed to allow insert/read at the same time as long as there's no data holes, and there are none in my case, a delete statement has never been ran on this table, never will it's all historical data.

show variables like 'concur%'
concurrent_insert 1 

How to repeat:
test table

CREATE TABLE `table_test` (
`column1` int(11) NOT NULL,
`column2` char(10) default NULL,
`column3` char(10) default NULL,
`column4` char(10) default NULL,
`column5` char(10) default NULL,
`column6` char(10) default NULL,
`column7` char(10) default NULL,
`column8` char(10) default NULL,
`column9` char(10) default NULL,
`column10` char(10) default NULL,
PRIMARY KEY (`column1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;

load 1 million rows of junk

LOAD DATA CONCURRENT INFILE '/temp/table_test.txt' REPLACE INTO TABLE
TABLE_TEST fields terminated by '|' optionally enclosed by '' lines
terminated by '\n';

select while the load is running, with 5.0.17 it returns results, with 5.0.18 and newer it locks until the load is complete

select * from table_test;  

5.0.17 reports 0 records, 5.0.27 locks until the insert/replace is done, it does this with all selects, not just the count 

If you repeat without trunicating the table, select * from table_test where column1='0'; you get the first record with no lock, under 5.0.19 and greater, it locks until the entire load is complete

Our normal tables take 15-30 minutes to load input files, so the locks run mysql client out of memory when it retries over and over

sample data to load can be downloaded from..

http://216.130.145.190/testdata.zip

Suggested fix:
compare 5.0.17 to 5.0.27 concurrent code
[16 Dec 2006 12:42] MySQL Verification Team
Duplicate for #20637
[15 Oct 2012 16:41] Dhruv Matani
I have the same problem with MyISAM table on 5.1.63.
SELECTs block while LOAD DATA INFILE is running.