MySQL Forge

Resources for the MySQL Community

Categories: Contributing | Development | MySQLUniversity

Execution of INSERT (DELAYED) and UPDATE statements

Contents 1 Execution of INSERT (DELAYED) and UPDATE statements 2 Presentation 2.1 Introduction 2.2 Structures important for understanding query processing 2.3 Files important to understanding this query processing 2.3.1 Types of INSERT and UPDATE 2.3.2 INSERT/UPDATE based on select (common) 2.3.3 INSERT ... SELECT ... (SQLCOM_INSERT_SELECT) 2.4 UPDATE of multiple tables (SQLCOM_UPDATE_MULTI) 2.5 Insert with a list of values (common loop of SQLCOMM_INSERT) 2.6 Delayed insert 2.7 Single table update (SQLCOM_UPDATE) 3 Questions 4 Voice recording and other links

← Back to MySQL University main page

[edit] Execution of INSERT (DELAYED) and UPDATE statements

• Date: 2008-01-24
• Presenter: Oleksandr Byelkin
• Scribe: Paul DuBois
• Attendees (please register by filling in your name below, and read the Instructions for Attendees):
  1. Sergey Petrunia
  2. ...

[edit] Presentation

[edit] Introduction

It is overview (not really very detailed) of insert/update/replace command execution

• Important structures / files to understand command execution
• Types of the commands
• Every type execution in more details

[edit] Structures important for understanding query processing

• THD - thread handler - thread specific information (sql/sql_parse.cc sql/sql_parse.h)
• LEX - collects information provided by parser and then it is modified during the query processing (sql/sql_lex.h)
• SELECT_LEX_UNIT - single select or union of selects (sql/sql_lex.h)
• SELECT_LEX - separate SELECT part of single select or unit (also insert update delete and other command parsing fill the structure) (sql/sql_lex.h)
• TABLE_LIST - descriptor of the table created by parser (sql/table.h)
• The tables linked in 2 lists:
  • local for each SELECT (also INSERT/UPDATE/DELETE and so on)
  • global list of all tables (for open / locking and so on)

[edit] Files important to understanding this query processing

• sql/sql_parse.cc - main loop of command execution and some procedures of prechecking the queries.
• sql/sql_insert.cc - insert/replace related procedures and classes.
• sql/sql_update.cc - update related procedures and classes.

[edit] Types of INSERT and UPDATE

• By command (sql/sql_parse.cc:mysql_execute_command())
  • SQLCOM_INSERT (almost the same as SQLCOM_REPLACE)
  • SQLCOM_INSERT_SELECT (almost the same as SQLCOM_REPLACE_SELECT)
  • SQLCOM_UPDATE
  • SQLCOM_UPDATE_MULTI
• By execution
  • Based on SELECT (SQLCOM_INSERT_SELECT, SQLCOM_UPDATE_MULTI and update of multi-table view)
  • Own procedure (SQLCOM_INSERT (without DELAYED clause) and SQLCOM_UPDATE)
  • Different thread (SQLCOM_INSERT (without DELAYED clause)).

[edit] INSERT/UPDATE based on select (common)

• Checks tables rights (insert_precheck(), update_precheck()) (it only starts the rights check, some rights (for fields or view parts) will be checked during preparation tables and fields))
• Makes Special preparations for select and construct class for collecting results of select (mysql_insert_select_prepare(), mysql_multi_update_prepare(), select_insert, multi_update)
• Executes select (mysql_select()) with special class for collecting results (inherited from select_result_interceptor). The procedures which execute select:
  • call prepare() method of the given class before execution the query.
  • call send_data() method for every row found in the join of select.
  • call send_eof() method when select succeed and all data are sent.
  • call error() and abort() for error reporting and in case of aborting query.
  • cleanup() method and destructor will be called at the end.
• Cleans (unlock tables and so on)

[edit] INSERT ... SELECT ... (SQLCOM_INSERT_SELECT)

• The class for result processing is select_insert.
  • select_insert::send_data() just insert the given row.
• Turns delayed inserts to normal ones (by changing lock type)

 if (first_table->lock_type == TL_WRITE_DELAYED)
   first_table->lock_type= TL_WRITE; 

• If the same table used for select and insert select forced use buffered results for select (i.e. results will be put into temporary table before be given to the client (client here is insert process)).

 lex->current_select->options|= OPTION_BUFFER_RESULT; 
 lex->current_select->join->select_options|= OPTION_BUFFER_RESULT; 

• Specially treat the first table (it do not belong to select).

[edit] UPDATE of multiple tables (SQLCOM_UPDATE_MULTI)

• Function sql_update.c:mysql_multi_update().
• Updates table in-place if it is possible (table will not be read twice).
• Store row id and field which should to be updated to temporary table then updates changed tables by it after select execution.
• The class for result processing is multi_update.
  • multi_update::send_data() check tables which should be changed and update the row in-place if it is OK or fill the temporary table correspondent to the table which should be updated.
  • multi_update::do_updates() transfer data from temporary tables to the real ones which called from multi_update::abort() and multi_update::send_eof()

[edit] Insert with a list of values (common loop of SQLCOMM_INSERT)

• Checks table rights
• function sql/sql_insert.cc:mysql_insert()
• Checks locks and lock tables (here it can upgrade lock and switch from delayed insert to normal) (upgrade_lock_type(), open_and_lock_for_insert_delayed()/open_and_lock_tables()).
• Prepare fields and value list items (special process which need separate presentation about MySQL expressions)
• Iterates through values list and put values to the table (for delayed insert into temporary table)
• Clears (Invalidate query cache / unlock tables and so on)

[edit] Delayed insert

• Served by class Delayed_insert (sql/sql_insert.cc)
• After locking checks presence of thread which process the delayed inserts for this table or try to create new.
• If above failed it rollback to normal insert.
• Inserts value in the temporary table.
• Triggers the thread tables inserts and send OK to client after some cleanup.

[edit] Single table update (SQLCOM_UPDATE)

• Function sql/sql_update.cc:mysql_update()
• Opens tables and if finds multi-table view insertion return to perform multi-update.
• Simplified select execution (we have only one table):
  • Processes derived tables (views here) (mysql_handle_derived(), fill_derived_tables())
  • Prepare tables and fields
  • Optimize conditions (remove_eq_conds())
  • make_select() and code after it - it is select-row-by-row preparation (low level function of select) but it do not perform select, chooses table access method (by index, scan and so on)
  • If we update the same index we using for search then we store found row IDs (in IO_CACHE) and then will loop through the list instead of the table index or the table
  • Reads and update the rows
• Cleanups (query cache invalidation / unlocking and so on)

[edit] Questions

[edit] Voice recording and other links

• Voice Recording: Ogg Audio (4MB)
• IRC log: IRC Text (9KB)