DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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