INSERT DELAYED ...

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to SQL-92.

INSERT DELAYED works only with ISAM and MyISAM tables. Note that as MyISAM tables support concurrent SELECT and INSERT , if there is no free blocks in the middle of the datafile, you very seldom need to use INSERT DELAYED with MyISAM .

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.

When you use INSERT DELAYED , the client will get an OK at once and the row will 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 doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way ( kill -9 ) or if mysqld dies unexpectedly, any queued rows that weren't written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE . In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

• 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 already exists.
• The thread checks whether the handler has acquired a DELAYED lock already; 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 will wait 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 can't report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can't obtain them from the server, 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 commands are received within delayed_insert_timeout seconds, the handler terminates.
• If more than delayed_queue_size rows are pending already 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 will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id . However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created. Note that the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler already running! Other update commands will have to wait until the INSERT DELAYED queue is empty, someone kills the handler thread (with KILL thread_id ), or someone executes FLUSH TABLES .
• The following status variables provide information about INSERT DELAYED commands:

  Variable	Meaning
  Delayed_insert_threads	Number of handler threads
  Delayed_writes	Number of rows written with INSERT DELAYED
  Not_flushed_delayed_rows	Number of rows waiting to be written

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

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED . This means that you should use INSERT DELAYED only when you are really sure you need it!

back to MySQL reference