The Ultimate Guide to High Availability with MariaDB

Concurrent inserts

You are viewing an old version of this article. View the current version here.

The MyISAM storage engine supports concurrent inserts. This feature allows SELECT statements to be executed during INSERT operations, reducing contetion.

Wether the 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 have been deleted after last OPTIMIZE TABLE). This is the default.
  • ALWAYS (2) always enables concurrent inserts.

If the binary log is used, CREATE TABLE ... SELECT and insert-selectINSERT ... SELECT statements cannot use concurrent insert. Also these statements acquire a read lock on the table, so concurrent insert will need to wait. This way the log can be safely used to restore data.

If an INSERT statement contain the HIGH_PRIORITY clause, concurrent inserts cannot be used. INSERT ... DELAYED keyword are usually unneeded if concurrent inserts are enabled.

LOAD DATA INFILE uses concurrent inserts if the CONCURRENT keyword is specified. This makes the statement slower (even if no other sessions access the table) but reduces contetion.

LOCK TABLES allowes non-conflicting concurrent inserts if READ LOCAL lock is used. Concurrent inserts are not allowed if the LOCAL keyword is omitted.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.