INSERT DELAYED ...
The DELAYED
option for the INSERT
statement is a MariaDB/MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT
to complete. This is a common situation when you use MariaDB for logging and you also periodically run SELECT
and UPDATE
statements that take a long time to complete.
When a client uses INSERT DELAYED
, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Another major benefit of using INSERT DELAYED
is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.
Note that INSERT DELAYED
is slower than a normal INSERT
if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED
only when you are really sure that you need it.
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
The number of concurrent INSERT DELAYED
threads is limited by the max_delayed_threads
server system variables. If it is set to 0, INSERT DELAYED
is disabled. The session value can be equal to the global value, or 0 to disable this statement for the current session. If this limit has been reached, the DELAYED
clause will be silently ignore for subsequent statements (no error will be produced).
There are some constraints on the use of DELAYED
:
INSERT DELAYED
works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. If you execute INSERT DELAYED with another storage engine, you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported for table 'tab_name'
INSERT DELAYED
with MyISAM. INSERT DELAYED
should be used only for INSERT
statements that specify value lists. The server ignores DELAYED
for INSERT ... SELECT
or INSERT ... ON DUPLICATE KEY UPDATE
statements. INSERT DELAYED
statement returns immediately, before the rows are inserted, you cannot use LAST_INSERT_ID()
to get the AUTO_INCREMENT
value that the statement might generate. DELAYED
rows are not visible to SELECT
statements until they actually have been inserted. INSERT DELAYED
, ROW_COUNT() returns the number of the rows you tried to insert, not the number of the successful writes. DELAYED
is ignored on slave replication servers, so that INSERT DELAYED
is treated as a normal INSERT
on slaves. This is because DELAYED
could cause the slave to have different data than the master. INSERT DELAYED
statements are not safe for replication. INSERT DELAYED
statements are lost if a table is write locked and ALTER TABLE is used to modify the table structure. INSERT DELAYED
is not supported for views. If you try, you will get an error like this: ERROR 1347 (HY000): 'view_name' is not BASE TABLE
INSERT DELAYED
is not supported for partitioned tables. INSERT DELAYED
is not supported within stored programs.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/insert-delayed/