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
