Whether concurrent inserts can be used or not depends on the value of the
concurrent_insert server system variable:
NEVER(0) disables concurrent inserts.
AUTO(1) allows concurrent inserts only when the target table has no free blocks (no data in the middle of the table has been deleted after the last
OPTIMIZE TABLE). This is the default.
ALWAYS(2) always enables concurrent inserts.
If the binary log is used,
CREATE TABLE ... SELECT and
INSERT ... SELECT statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way the log can be safely used to restore data.
LOAD DATA INFILE uses concurrent inserts if the
CONCURRENT keyword is specified and
concurrent_insert is not
NEVER. This makes the statement slower (even if no other sessions access the table) but reduces contention.
LOCK TABLES allows non-conflicting concurrent inserts if a
READ LOCAL lock is used. Concurrent inserts are not allowed if the
LOCAL keyword is omitted.
The decision to enable concurrent insert for a table is done when the table is opened. If you change the value of
concurrent_insert it will only affect new opened tables. If you want it to work for also for tables in use or cached, you should do
FLUSH TABLES after setting the variable.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.