Bug #42243 LOAD DATA CONCURRENT INFILE locks table even when concurrent_insert = 2

• Submitted: 21 Jan 2009 15:30
• Modified: 13 Feb 2009 12:25
• Reporter: Bogdan Kecman
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 5.1.30
• OS: Any
• CPU Architecture: Any
• Tags: regression

[21 Jan 2009 15:30] Bogdan Kecman

Description:
LOAD DATA CONCURRENT INFILE into MyISAM table, with concurrent_isert=2, lock the table for read if the table contain gaps.

when table does not contain gaps the behaviour is as expected.

How to repeat:
use one thread to constantly execute (select * from City limit 10;) while performing this test in another terminal:

mysql> delete from City where Name like 'A%';
Query OK, 9933168 rows affected (7 min 23.66 sec)

mysql> show variables like '%concurrent%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | 2     | 
+-------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 125787 | 
| Table_locks_waited    | 4      | 
+-----------------------+--------+
2 rows in set (0.00 sec)

mysql> LOAD DATA CONCURRENT INFILE '/tmp/gradovi' INTO TABLE City;
Query OK, 19554726 rows affected (14 min 0.13 sec)
Records: 19554726  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 147519 | 
| Table_locks_waited    | 5      | 
+-----------------------+--------+
2 rows in set (0.00 sec)

Suggested fix:
.

[6 Feb 2009 13:35] Sergey Vojtovich

Likely a duplicate of BUG#37282.

[13 Feb 2009 12:25] Sergei Golubchik

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.