Understand concurrent inserts in MyISAM. This feature allows SELECT statements to run simultaneously with INSERT operations, reducing lock contention and improving performance.
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 ). This is the default.
ALWAYS (2) always enables concurrent inserts, in which case new rows are added at the end of a table if the table is being used by another thread.
If the is used, and 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 are not used by replicas with the row-based (see ).
If an statement contains the clause, concurrent inserts cannot be used. is usually unneeded if concurrent inserts are enabled.
uses concurrent inserts if the CONCURRENT keyword is specified and is not NEVER. This makes the statement slower (even if no other sessions access the table) but reduces contention.
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 , it will only affect newly opened tables. If you want it to work for also for tables in use or cached, you should do after setting the variable.
This page is licensed: CC BY-SA / Gnu FDL