# Concurrent Inserts

The [MyISAM](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine) storage engine supports concurrent inserts. This feature allows [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) statements to be executed during [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert) operations, reducing contention.

Whether concurrent inserts can be used or not depends on the value of the [concurrent\_insert](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#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](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizing-tables/optimize-table)). 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 [binary log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log) is used, [CREATE TABLE ... SELECT](https://mariadb.com/docs/server/server-usage/tables/create-table#create-table-select) and [INSERT ... SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/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 are not used by replicas with the row-based [replication](https://mariadb.com/docs/server/ha-and-performance/standard-replication) (see [binary log formats](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats)).

If an [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert) statement contains the [HIGH\_PRIORITY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/high_priority-and-low_priority) clause, concurrent inserts cannot be used. [INSERT ... DELAYED](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-delayed) is usually unneeded if concurrent inserts are enabled.

[LOAD DATA INFILE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile) uses concurrent inserts if the `CONCURRENT` keyword is specified and [concurrent\_insert](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#concurrent_insert) is not `NEVER`. This makes the statement slower (even if no other sessions access the table) but reduces contention.

[LOCK TABLES](https://mariadb.com/docs/server/reference/sql-statements/transactions/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.

## Notes

The decision to enable concurrent insert for a table is done when the table is opened. If you change the value of [concurrent\_insert](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#concurrent_insert), it will only affect newly opened tables. If you want it to work for also for tables in use or cached, you should do [FLUSH TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) after setting the variable.

## See Also

* [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert)
* [INSERT DELAYED](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-delayed)
* [INSERT SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-select)
* [HIGH\_PRIORITY and LOW\_PRIORITY](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/high_priority-and-low_priority)
* [INSERT - Default & Duplicate Values](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-default-duplicate-values)
* [INSERT IGNORE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-ignore)
* [INSERT ON DUPLICATE KEY UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-on-duplicate-key-update)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/concurrent-inserts.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.