MySQL Reference Manual :: 13.2.4.2 INSERT DELAYED Syntax

INSERT DELAYED works only with ISAM, MyISAM, and (beginning with MySQL 4.1) MEMORY tables. For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. See Section 14.1, “The MyISAM Storage Engine”. See Section 14.3, “The MEMORY (HEAP) Storage Engine”.

INSERT DELAYED should be used only for INSERT statements that specify value lists. This is enforced as of MySQL 4.0.18. The server ignores DELAYED for INSERT DELAYED ... SELECT statements.

The server ignores DELAYED for INSERT DELAYED ... ON DUPLICATE UPDATE statements.

Because the statement returns immediately before the rows are inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value the statement might generate.

DELAYED rows are not visible to SELECT statements until they actually have been inserted.

When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler previously exists.

The thread checks whether the handler has previously acquired a DELAYED lock; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler waits for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.

The thread executes the INSERT statement, but instead of writing the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.

The client cannot obtain from the server the number of duplicate records or the AUTO_INCREMENT value for the resulting row, because the INSERT returns before the insert operation has been completed. (If you use the C API, the mysql_info() function doesn't return anything meaningful, for the same reason.)

The binary log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the binary log is updated when the first row is inserted.

After every delayed_insert_limit rows are written, the handler checks whether any SELECT statements are still pending. If so, it allows these to execute before continuing.

When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED statements are received within delayed_insert_timeout seconds, the handler terminates.

If more than delayed_queue_size rows are pending in a specific handler queue, the thread requesting INSERT DELAYED waits until there is room in the queue. This is done to ensure that the mysqld server doesn't use all memory for the delayed memory queue.

The handler thread shows up in the MySQL process list with delayed_insert in the Command column. It is killed if you execute a FLUSH TABLES statement or kill it with KILL thread_id. However, before exiting, it first stores all queued rows into the table. During this time it does not accept any new INSERT statements from another thread. If you execute an INSERT DELAYED statement after this, a new handler thread is created.

Note that this means that INSERT DELAYED statements have higher priority than normal INSERT statements if there is an INSERT DELAYED handler running. Other update statements have to wait until the INSERT DELAYED queue is empty, someone terminates the handler thread (with KILL thread_id), or someone executes FLUSH TABLES.

The following status variables provide information about INSERT DELAYED statements:

You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.