GNU Info

(mysql.info)INSERT DELAYED

---

Next: UPDATE Prev: INSERT Up: Data Manipulation

---

Enter node , (file) or (file)node

`INSERT DELAYED' syntax
-----------------------

     INSERT DELAYED ...

The `DELAYED' option for the `INSERT' statement is a MySQL-specific
option that is very useful if you have clients that can't wait for the
`INSERT' to complete.  This is a common problem when you use MySQL for
logging and you also periodically run `SELECT' and `UPDATE' statements
that take a long time to complete.  `DELAYED' was introduced in MySQL
Version 3.22.15.  It is a MySQL extension to ANSI SQL92.

`INSERT DELAYED' only works with `ISAM' and `MyISAM' tables.  Note that
as `MyISAM' tables supports concurrent `SELECT' and `INSERT', if there
is no free blocks in the middle of the data file, you very seldom need
to use `INSERT DELAYED' with `MyISAM'. Note: MyISAM.

When you use `INSERT DELAYED', the client will get an OK at once and
the row will 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 doing many separate inserts.

Note that currently the queued rows are only stored in memory until
they are inserted into the table.  This means that if you kill `mysqld'
the hard way (`kill -9') or if `mysqld' dies unexpectedly, any queued
rows that weren't written to disk are lost!

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' command 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 or not the handler has acquired a
     `DELAYED' lock already; 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 will
     wait for all `ALTER TABLE' locks or `FLUSH TABLES' 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 can't report the number of duplicates or the
     `AUTO_INCREMENT' value for the resulting row; it can't obtain them
     from the server, because the `INSERT' returns before the insert
     operation has been completed.  If you use the C API, the
     `mysql_info()' function doesn't return anything meaningful, for
     the same reason.

   * The update log is updated by the handler thread when the row is
     inserted into the table.  In case of multiple-row inserts, the
     update log is updated when the first row is inserted.

   * After every `delayed_insert_limit' rows are written, the handler
     checks whether or not 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' commands are received within
     `delayed_insert_timeout' seconds, the handler terminates.

   * If more than `delayed_queue_size' rows are pending already in a
     specific handler queue, the thread requesting `INSERT DELAYED'
     waits until there is room in the queue.  This is done to ensure
     that the `mysqld' server doesn't use all memory for the delayed
     memory queue.

   * The handler thread will show up in the MySQL process list with
     `delayed_insert' in the `Command' column.  It will be killed if
     you execute a `FLUSH TABLES' command or kill it with `KILL
     thread_id'. However, it will first store all queued rows into the
     table before exiting. During this time it will not accept any new
     `INSERT' commands from another thread. If you execute an `INSERT
     DELAYED' command after this, a new handler thread will be created.

   * Note that the above means that `INSERT DELAYED' commands have
     higher priority than normal `INSERT' commands if there is an
     `INSERT DELAYED' handler already running!  Other update commands
     will have to wait until the `INSERT DELAYED' queue is empty,
     someone kills the handler thread (with `KILL thread_id'), or
     someone executes `FLUSH TABLES'.

   * The following status variables provide information about `INSERT
     DELAYED' commands:

     *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.

Note that `INSERT DELAYED' is slower than a normal INSERT if the table
is not in use.  There is also the additional overhead for the server to
handle a separate thread for each table on which you use `INSERT
DELAYED'.  This means that you should only use `INSERT DELAYED' when
you are really sure you need it!