Insert delayed syntax

Insert delayed syntax

Insert delayed...

The delayed option of the insert statement is unique to MySQL. It is useful if your client cannot wait for the completion of the insert statement. This is a common problem when you use MySQL for logging and when you open a select and update statement that uses MySQL and takes a long time to complete periodically, this will be a common problem. Delayed was introduced in MySQL 3.22.15. It is an extension of MySQL to ANSI sql92.

Insert delayed only works with isam and MyISAM tables. Note: MyISAM tables support concurrent select and insert operations. If there are no idle blocks in the data file, you rarely need to use insert delayed for MyISAM tables. View section 7.1 MyISAM table.

When you use insert delayed, the client immediately returns an OK message. When the table is not used by any other thread, this row is inserted.

Another major benefit of using insert delayed is that insert requests from many clients are packaged and written into a block. This is much faster than doing many separate inserts.

Note that the current record row queue is stored in the memory until they are inserted into the table. This means that if you use a forced method (kill-9) to kill mysqld, or if you accidentally die, any record row queues that are not written to the disk will be lost!

The following describes in detail what will happen when you use the delayed option for insert or replace. In this description, the "Thread" is the thread that encounters an insert delayed command, and the "processor" is the thread that processes all the insert delayed statements for a specific table.

When a thread executes a delayed statement on a table, a processor thread is created to process all the delayed statements on the table, unless such a processor already exists.

The thread checks whether the processor has obtained a delayed lock. If not, this tells the handler to get the lock. The delayed lock can be obtained even if other threads have added a read or write lock to the table. However, the processor will wait for all the alter table locks or flush tables to ensure that the table structure is up-to-date.

The thread executes the insert statement, but does not write the record row to the table. It places copies of the final record row in the queue managed by the processor thread. Any syntax error will be discovered by the thread and reported to the client program.

The client cannot report the number of repetitions or auto_increment values in the result record line; it cannot obtain them from the server because the insert statement is returned before the insert operation is completed. If you use c api, The mysql_info () function cannot obtain any meaningful information for the same reason.

When the record row is inserted into the table, the binary log file is updated by the processor thread. For insert of multiple record rows, the binary log is updated when the first record row is inserted.

After each write of delayed_insert_limit record rows, the processor checks whether any select statements remain unsolved. If so, the processor allows these statements to be executed first before continuing.

When the processor finds that there are not many rows in its queue, the table will be unlocked. If the new insert delayed command is not received within the second of delayed_insert_timeout, the processor thread terminates.

If there are already more than delayed_queue_size record lines in a specific processor queue that have not been resolved, the thread requires the insert delayed to wait, only to have available space in the queue. This is done to ensure that the mysqld server does not use all the memory for the delayed memory queue.

The command column of the processor thread in the MySQL process list is displayed as delayed_insert. If you execute a flush tables command or kill thread_id to kill it, it will be killed. However, it first saves the rows of records in the queue to the table before exiting. During these periods, it will no longer receive any new insert commands from other threads. If an insert delayed command is executed after this operation, a new processor thread will be created. NOTE: If an insert delayed processor is running, the insert delayed command has a higher priority than the normal INSERT command! Other update commands will have to wait until the insert delayed queue is cleared, killing the processor thread (with kill thread_id) or executing flush tables.

The following status variables provide information about the insert delayed command:
Delayed_insert_threads processor thread count
Number of record rows written by delayed_writes Using Insert delayed
Not_flushed_delayed_rows
You can view these variables by issuing a show status statement or executing a mysqladmin extended-STATUS Command.
Note: If the table is not in use, insert delayed will be slower than a normal insert. It also has additional overhead to let the server process a separate thread for each table you use insert delayed. This means that you should use insert delayed only when you are sure you need it.