(mysql.info.gz) INSERT DELAYED

 
 13.1.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 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 3.22.15.
 
 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 doing many separate inserts.
 
 There are some constraints on the use of `DELAYED':
 
    * `INSERT DELAYED' works only with `MyISAM' and `ISAM' tables.  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'.   `MyISAM' storage engine
      MyISAM storage engine.
 
    * `INSERT DELAYED' should be used only for `INSERT' statements that
      specify value lists. This is enforced as of MySQL 4.0.18.  The
      server ignores `DELAYED' for `INSERT DELAYED ... SELECT'
      statements.
 
    * The server ignores `DELAYED' for `INSERT DELAYED ... ON DUPLICATE
      UPDATE' statements.
 
    * Because the statement returns immediately before the rows are
      inserted, you cannot use `LAST_INSERT_ID()' to get the
      `AUTO_INCREMENT' value the statement might generate.
 
    * `DELAYED' rows are not visible to `SELECT' statements until they
      actually have been inserted.
 
 
 Note that currently 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!
 
 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 previously 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 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 cannot obtain from the server the number of duplicate
      records 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
      doesn't 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.
 
    * After every `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 the
      `mysqld' server doesn't 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 will be killed if
      you execute a `FLUSH TABLES' statement or kill it with `KILL
      thread_id'. However, before exiting, it will first store all
      queued rows into the table. During this time it will not accept
      any new `INSERT' statements from another thread. If you execute an
      `INSERT DELAYED' statement after this, a new handler thread will
      be 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 will have to
      wait until the `INSERT DELAYED' queue is empty, someone terminates
      the handler thread (with `KILL thread_id'), or someone executes
      `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.
 
 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 for which there are
 delayed rows.  This means that you should use `INSERT DELAYED' only
 when you are really sure that you need it!
Info Catalog
(mysql.info.gz) INSERT SELECT
(mysql.info.gz) INSERT
automatically generated byinfo2html