The MyISAM storage engine supports concurrent inserts. This feature allows SELECT
statements to be executed during INSERT
operations, reducing contention.
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.
Concurrent inserts is not used by slaves with the row based replication (see binary log formats).
If an INSERT
statement contain the HIGH_PRIORITY
clause, concurrent inserts cannot be used. INSERT ... DELAYED
is usually unneeded if concurrent inserts are enabled.
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.
https://mariadb.com/kb/en/concurrent-inserts/