(mysql.info) insert-delayed
Info Catalog
(mysql.info) insert-select
(mysql.info) insert
(mysql.info) insert-on-duplicate
13.2.4.2 `INSERT DELAYED' Syntax
................................
INSERT DELAYED ...
The `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 periodically run `SELECT'
and `UPDATE' 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 `INSERT DELAYED' is that inserts from
many clients are bundled together and written in one block. This is
much faster than performing many separate inserts.
Note that `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.
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', and `ARCHIVE'
tables. See myisam-storage-engine,
memory-storage-engine, and archive-storage-engine.
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
DELAYED ... SELECT' statements.
* 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.
* `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 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 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' 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
THREAD_ID'. However, 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 `INSERT
DELAYED' statement after this, a new handler thread is created.
Note that 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 THREAD_ID'), or someone executes a
`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.
Info Catalog
(mysql.info) insert-select
(mysql.info) insert
(mysql.info) insert-on-duplicate
automatically generated byinfo2html