Bug #64888 Inconsistent behavior of dynamic concurrent_insert values
Submitted: 6 Apr 2012 14:43 Modified: 6 Apr 2012 15:54
Reporter: Elena Stepanova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[6 Apr 2012 14:43] Elena Stepanova
Description:
The value of global variable concurrent_insert can be changed dynamically. However, the results are inconsistent.
'How to repeat' section contains 2 test cases. Test case 1 is to check the behavior on table without holes, test case 2 -- with holes. Otherwise they are exactly the same.

The test does the following:
- set the global value of concurrent_insert (it is not important and only placed there for convenience, it can be replaced by providing this value on server startup); 
- create a table and populates it with two lines (and creates a hole if needed); 
- set the dynamic value of concurrent_insert (that's the change we are testing);
- start one thread and run a long enough SELECT there (2 sec in the test case);
- in parallel, start another thread, wait till SELECT shows up in the process list, and run INSERT;
- measure time the INSERT takes.

If INSERT is performed concurrently, it only takes a few milliseconds. If it is not performed concurrently, it takes about 2 seconds, as it waits till SELECT is finished.

Results (X=>Y means that concurrent_insert value is changed from X to Y):

Test case 1:
0 => 1: FAIL (INSERT is not concurrent)
1 => 0: FAIL (INSERT is concurrent)

Test case 2:
0 => 2: FAIL (INSERT is not concurrent)
1 => 2: OK   (INSERT is concurrent)
2 => 0: OK   (INSERT is not concurrent)
2 => 1: OK   (INSERT is not concurrent)

If the second SET concurrent_insert is removed, or placed before table creation, or is followed by FLUSH TABLES, everything works all right; so it might be made this way by design, in which case it needs to be properly documented (as the logic is not obvious).

How to repeat:
# Test case 1
# Checking that NEVER and AUTO work as expected
# can be used for 1=>0, 0=>1 
# here: AUTO=>NEVER

# The following assignment can be replaced by 
# providing the corresponding value 
# on server startup
SET GLOBAL concurrent_insert = 1;

CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);

# This is what supposed to be 
# the dynamic value change
SET GLOBAL concurrent_insert = 0;

# If I put FLUSH TABLES here,
# it starts working as expected

--connect (con0,localhost,root,,)

send SELECT SLEEP(1) FROM t1;

--connect (con1,localhost,root,,)

let $show_statement = SHOW PROCESSLIST;
let $field = State;
let $condition = = 'User sleep';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

# If INSERT is concurrent, it should take
# only a few milliseconds. Otherwise 
# it will be close to the whole 2 seconds
# while SELECT is sleeping
--start_timer
INSERT INTO t1 VALUES (2);
--end_timer

--connection con0
--reap

DROP TABLE t1;
# Restore concurrent_insert here!

# End of test case 1
##################################################

# Checking that ALWAYS works as expected
# Can be used for: 0=>2, 1=>2, 2=>0, 2=>1
# here: NEVER=>ALWAYS

# The following assignment can be replaced by 
# providing the corresponding value 
# on server startup
SET GLOBAL concurrent_insert = 0;

CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2),(3);
DELETE FROM t1 WHERE i=2;

# This is what supposed to be 
# the dynamic value change
SET GLOBAL concurrent_insert = 2;

# If I put FLUSH TABLES here,
# it starts working as expected

--connect (con0,localhost,root,,)

send SELECT SLEEP(1) FROM t1;

--connect (con1,localhost,root,,)

let $show_statement = SHOW PROCESSLIST;
let $field = State;
let $condition = = 'User sleep';
let $wait_timeout = 2;
--source include/wait_show_condition.inc

# If INSERT is concurrent, it should take
# only a few milliseconds. Otherwise 
# it will be close to the whole 2 seconds
# while SELECT is sleeping
--start_timer
INSERT INTO t1 VALUES (2);
--end_timer

--connection con0
--reap

DROP TABLE t1;
# Restore concurrent_insert here!
# End of test case 2
[6 Apr 2012 15:54] Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.61 on Mac OS X.