MySQL Bugs: #37282: LOAD DATA CONCURRENT blocks selects when set to 2 and hole in table

Bug #37282	LOAD DATA CONCURRENT blocks selects when set to 2 and hole in table
Submitted:	9 Jun 2008 14:09	Modified:	13 Feb 2009 12:24
Reporter:	Scott Noyes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.60	OS:	Any
Assigned to:	V Venkateswaran	CPU Architecture:	Any

Description:
A running LOAD DATA CONCURRENT locks the table, so new SELECT statements must wait for the load to finish. Occurs when concurrent_inserts=2 and there is a hole in the data.

If there is no hole in the table, then subsequent selects return quickly, before the LOAD DATA has finished.

Similar to bug 20637.

How to repeat:
mysqltest:

connect (conn1, localhost);
connect (conn2, localhost);

connection conn1;

SET GLOBAL concurrent_insert = 2;
USE test;
DROP TABLE IF EXISTS dataTest;
CREATE TABLE dataTest (id int, index(id)) ENGINE = MyISAM;

INSERT INTO dataTest VALUES (1), (2), (3);
DELETE FROM dataTest WHERE id = 2;

send LOAD DATA CONCURRENT LOCAL INFILE 'intList.txt' INTO TABLE dataTest; -- 500K rows of integers

connection conn2;
USE test;
SELECT COUNT(*) FROM dataTest;

Suggested fix:
Either fix the concurrent operation so selects started after a LOAD DATA CONCURRENT can execute simultaneously, or adjust the documentation to show this is expected behavior.

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/66029

2804 V Narayanan	2009-02-12
      Bug#37282 LOAD DATA CONCURRENT blocks selects when set to 2 and hole in table
      
      A running LOAD DATA CONCURRENT locks the table, 
      so new SELECT statements must wait for the load to finish. 
      Occurs when concurrent_inserts=2 and there is a hole in 
      the data.
      
      Currently in MyISAM concurrent_inserts = 2 disallows concurrent inserts
      on the table when there are concurrent readers on the table. This causes
      the selects to wait when a load concurrent operation is in progress.
      
      The current patch fixes the behaviour by introducing a new mode
      
      concurrent_inserts = 3
      
      In this mode concurrent inserts are disable only when there are concurrent
      writers and not when there are concurrent readers.

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/66148

2804 V Narayanan	2009-02-13
      Bug#37282 LOAD DATA CONCURRENT blocks selects when set to 2 and hole in table
      
      A running LOAD DATA CONCURRENT locks the table, so new
      SELECT statements must wait for the load to finish.Occurs
      when concurrent_inserts=2 and there is a hole in the data.
      
      When concurrent_inserts is 2 and there is a hole in the data
      file, MyISAM allows insert to run concurrently only if there
      were readers at the moment it was acquiring a lock. If there
      were no readers, MyISAM converted concurrent insert to regular
      insert, blocking new concurrent readers until insert is
      completed.
      
      This patch introduces a third mode for concurrent inserts, 
      which is similar to the second. The only difference being that
      inserts are always run concurrently, even if there were no readers
      when a lock has been acquired.
      
      This will be useful for loading a lot of data into a table and
      still have a table accessible by new concurrent readers.
      
      The side effect of this mode is that inserts will never reuse 
      holes in data file. Thus it is not recommended to use this mode by
      default.

Patch approved subject to architectural approval of the general concept.
I leave the bug state "in review" due to the pending decision. You may change it yourself when you have approval from architects.
Please see email for details.

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/66177

2806 V Narayanan	2009-02-13
      Bug#37282 LOAD DATA CONCURRENT blocks selects when set to 2 and hole in table
      
      A running LOAD DATA CONCURRENT locks the table, so new
      SELECT statements must wait for the load to finish.Occurs
      when concurrent_inserts=2 and there is a hole in the data.
      
      When concurrent_inserts is 2 and there is a hole in the data
      file, MyISAM allows insert to run concurrently only if there
      were readers at the moment it was acquiring a lock. If there
      were no readers, MyISAM converted concurrent insert to regular
      insert, blocking new concurrent readers until insert is
      completed.
      
      This patch introduces a third mode for concurrent inserts,
      which is similar to the second. The only difference being that
      inserts are always run concurrently, even if there were no readers
      when a lock has been acquired.
      
      This will be useful for loading a lot of data into a table and
      still have a table accessible by new concurrent readers.
      
      The side effect of this mode is that inserts will never reuse
      holes in data file. Thus it is not recommended to use this mode by
      default.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The manual is very clear about it:
"
concurrent_insert

     *Value* *Description*
     0       Off
     1       (Default) Enables concurrent insert for `MyISAM'
             tables that don't have holes
     2       Enables concurrent inserts for all `MyISAM'
             tables, even those that have holes. For a table
             with a hole, new rows are inserted at the end of
             the table if it is in use by another thread.
             Otherwise, MySQL acquires a normal write lock and
             inserts the row into the hole.
"

It says that if insert has started when the table was in use, it'll be executed as concurrent insert, otherwise - as a normal blocking insert.