INSERT DELAYED Syntax
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
complete. This is a common situation when you use MySQL for
logging and you also periodically run
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
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
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
DELAYED only when you are really sure that you need
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 with
ARCHIVE tables. See
Section 14.1, “The
MyISAM Storage Engine”,
Section 14.4, “The
HEAP) Storage Engine”, and
Section 14.8, “The
ARCHIVE Storage Engine”.
MyISAM tables, if there are no free
blocks in the middle of the data file, concurrent
statements are supported. Under these circumstances, you
very seldom need to use
INSERT DELAYED should be used only for
INSERT statements that specify value
lists. The server ignores
INSERT DELAYED ... SELECT statements.
The server ignores
INSERT ... SELECT or
... ON DUPLICATE KEY UPDATE statements.
INSERT DELAYED statement
returns immediately, before the rows are inserted, you
LAST_INSERT_ID() to get the
AUTO_INCREMENT value that the statement
DELAYED rows are not visible to
SELECT statements until they actually
have been inserted.
DELAYED is ignored on slave replication
servers because it could cause the slave to have different
data than the master.
The following describes in detail what happens when you use
DELAYED option to
this description, the “thread” is the thread that
INSERT DELAYED statement and
“handler” is the thread that handles all
INSERT DELAYED statements for a particular
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
DELAYED lock can be obtained even if
other threads have a
WRITE lock on the table. However, the
handler waits for all
ALTER TABLE locks
FLUSH TABLES statements to finish,
to ensure that the table structure is up to date.
The thread executes the
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 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, 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
Each time that
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
statements are received within
delayed_insert_timeout seconds, the
If more than
are pending in a specific handler queue, the thread
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
FLUSH 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
statement after this, a new handler thread is created.
Note that this means that
DELAYED statements have higher priority than
INSERT statements if there is an
INSERT DELAYED handler running. Other
update statements have to wait until the
DELAYED queue is empty, someone terminates the
handler thread (with
someone executes a
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
You can view these variables by issuing a
STATUS statement or by executing a
mysqladmin extended-status command.