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 DELAYED works only
tables. For engines that do not support
DELAYED, an error occurs.
An error occurs for
DELAYED if used with a table that has been locked
LOCK TABLES because the insert must
be handled by a separate thread, not by the session that
holds the lock.
MyISAM tables, if there are no free
blocks in the middle of the data file, concurrent
INSERT statements are
supported. Under these circumstances, you very seldom need
INSERT DELAYED with
DELAYED rows are not visible to
SELECT statements until they
actually have been inserted.
Prior to MySQL 5.5.7,
DELAYED was treated as a normal
INSERT if the statement
inserted multiple rows, binary logging was enabled, and the
global logging format was statement-based (that is, whenever
binlog_format was set to
STATEMENT). Beginning with MySQL 5.5.7,
INSERT DELAYED is always
handled as a simple
(that is, without the
whenever the value of
(In the latter case, the statement no longer triggers a
switch to row-based logging, and so is logged using the
This does not apply when using row-based binary logging mode
binlog_format set to
ROW), in which
INSERT DELAYED statements are
always executed using the
as specified, and logged as row-update events.
INSERT DELAYED is not
supported for views.
INSERT DELAYED is 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
statement for a table, a handler thread is created to
DELAYED statements for the
table, if no such handler already exists.
The thread checks whether the handler has previously
DELAYED lock; if not, it tells
the handler thread to do so. The
lock can be obtained even if other threads have a
WRITE lock on
the table. However, the handler waits for all
ALTER TABLE locks or
TABLES statements to finish, 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
The client cannot obtain from the server the number of
duplicate rows or the
value for the resulting row, because the
INSERT returns before the
insert operation has been completed. (If you use the C API,
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_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
mysqld does not 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
TABLES statement or kill it with
before exiting, it first stores all queued rows into the
table. During this time it does not accept any new
INSERT statements from other
threads. If you execute an
DELAYED statement after this, a new handler thread
Note that this means that
DELAYED statements have higher priority than
INSERT statements if
there is an
handler running. Other update statements have to wait until
INSERT DELAYED queue is
empty, someone terminates the handler thread (with
), or someone
The following status variables provide information about
INSERT DELAYED statements.
|Number of handler threads|
|Number of rows written with |
|Number of rows waiting to be written|