MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
INSERT DELAYED ...
The DELAYED
option for the
INSERT
statement is a MySQL
extension to standard SQL that can be used for certain kinds of
tables (such as MyISAM
). 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.
INSERT DELAYED
is slower than a
normal 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
INSERT DELAYED
only when you
are really sure that you need it.
As of MySQL 5.6.6, INSERT
DELAYED
is deprecated; expect it to be removed in a
future release. Use INSERT
(without DELAYED
) instead.
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
DELAYED
:
INSERT DELAYED
works only
with MyISAM
, MEMORY
,
ARCHIVE
, and BLACKHOLE
tables. For engines that do not support
DELAYED
, an error occurs.
An error occurs for INSERT
DELAYED
if used with a table that has been locked
with LOCK TABLES
because the insert must
be handled by a separate thread, not by the session that
holds the lock.
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
.
INSERT DELAYED
should be used
only for INSERT
statements
that specify value lists. The server ignores
DELAYED
for
INSERT ...
SELECT
or
INSERT
... ON DUPLICATE KEY UPDATE
statements.
Because the INSERT DELAYED
statement returns immediately, before the rows are inserted,
you cannot use
LAST_INSERT_ID()
to get the
AUTO_INCREMENT
value that the statement
might generate.
DELAYED
rows are not visible to
SELECT
statements until they
actually have been inserted.
INSERT DELAYED
is handled as
a simple INSERT
(that is,
without the DELAYED
option) whenever the
value of binlog_format
is
STATEMENT
or MIXED
.
(In the latter case, the statement does not trigger 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
(binlog_format
set to
ROW
), in which
INSERT DELAYED
statements are
always executed using the DELAYED
option
as specified, and logged as row-update events.
DELAYED
is ignored on replica servers, so
that INSERT DELAYED
is
treated as a normal INSERT
on
replicas. This is because DELAYED
could
cause the slave to have different data than the source.
Pending INSERT DELAYED
statements are lost if a table is write locked and
ALTER TABLE
is used to modify
the table structure.
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
INSERT
or
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
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 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
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
client program.
The client cannot obtain from the server the number of
duplicate rows 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
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.
Each time that
delayed_insert_limit
rows
are written, the handler checks whether any
SELECT
statements are still
pending. If so, it permits 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
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
execute a FLUSH TABLES
statement 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
thread_id
INSERT
statements from other
threads. If you execute an INSERT
DELAYED
statement after this, a new handler thread
is created.
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
), or someone
executes a thread_id
FLUSH TABLES
.
The following status variables provide information about
INSERT DELAYED
statements.
Status 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.