INSERT DELAYED ...
DELAYED option for the
INSERT statement is a 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 MySQL for logging and you also
UPDATE statements that take a
long time to complete.
When a client uses
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
DELAYED is that inserts from many clients are bundled
together and written in one block. This is much faster than
performing many separate inserts.
INSERT DELAYED is
slower than a normal
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
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.
There are some constraints on the use of
INSERT DELAYEDworks only with
BLACKHOLEtables. For engines that do not support
DELAYED, an error occurs.
An error occurs for
INSERT DELAYEDif used with a table that has been locked with
LOCK TABLESbecause the insert must be handled by a separate thread, not by the session that holds the lock.
MyISAMtables, if there are no free blocks in the middle of the data file, concurrent
INSERTstatements are supported. Under these circumstances, you very seldom need to use
DELAYEDrows are not visible to
SELECTstatements until they actually have been inserted.
Prior to MySQL 5.5.7,
INSERT DELAYEDwas treated as a normal
INSERTif the statement inserted multiple rows, binary logging was enabled, and the global logging format was statement-based (that is, whenever
binlog_formatwas set to
STATEMENT). Beginning with MySQL 5.5.7,
INSERT DELAYEDis always handled as a simple
INSERT(that is, without the
DELAYEDoption) whenever the value of
MIXED. (In the latter case, the statement no longer triggers a switch to row-based logging, and so is logged using the statement-based format.)
This does not apply when using row-based binary logging mode (
ROW), in which
INSERT DELAYEDstatements are always executed using the
DELAYEDoption as specified, and logged as row-update events.
INSERT DELAYEDis not supported for views.
INSERT DELAYEDis not supported for partitioned tables.
The following describes in detail what happens when you use the
DELAYED option to
REPLACE. In this description, the
“thread” is the thread that received an
INSERT DELAYED statement and
“handler” is the thread that handles all
INSERT DELAYED statements for a
When a thread executes a
DELAYEDstatement for a table, a handler thread is created to process all
DELAYEDstatements for the table, if no such handler already exists.
The thread checks whether the handler has previously acquired a
DELAYEDlock; if not, it tells the handler thread to do so. The
DELAYEDlock can be obtained even if other threads have a
WRITElock on the table. However, the handler waits for all
ALTER TABLElocks or
FLUSH TABLESstatements to finish, to ensure that the table structure is up to date.
The thread executes the
INSERTstatement, 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 rows or the
AUTO_INCREMENTvalue for the resulting row, because the
INSERTreturns before the insert operation has been completed. (If you use the C API, the
mysql_info()function does not 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.
If more than
delayed_queue_sizerows are pending in a specific handler queue, the thread requesting
INSERT DELAYEDwaits until there is room in the queue. This is done to ensure that mysqld does not use all memory for the delayed memory queue.
The handler thread shows up in the MySQL process list with
Commandcolumn. It is killed if you execute a
FLUSH TABLESstatement or kill it with
KILL. However, before exiting, it first stores all queued rows into the table. During this time it does not accept any new
INSERTstatements from other threads. If you execute an
INSERT DELAYEDstatement after this, a new handler thread is created.
Note that this means that
INSERT DELAYEDstatements have higher priority than normal
INSERTstatements if there is an
INSERT DELAYEDhandler running. Other update statements have to wait until the
INSERT DELAYEDqueue is empty, someone terminates the handler thread (with
KILL), or someone executes a
The following status variables provide information about
Status Variable Meaning
Number of handler threads
Number of rows written with
Number of rows waiting to be written