• unknown's avatar
    Better approach for prelocking of tables for stored routines execution · ac9f68b9
    unknown authored
    and some SP-related cleanups.
    
    - We don't have separate stage for calculation of list of tables
      to be prelocked and doing implicit LOCK/UNLOCK any more.
      Instead we calculate this list at open_tables() and do implicit
      LOCK in lock_tables() (and UNLOCK in close_thread_tables()).
      Also now we support cases when same table (with same alias) is
      used several times in the same query in SP.
    
    - Cleaned up execution of SP. Moved all common code which handles
      LEX and does preparations before statement execution or complex
      expression evaluation to auxilary sp_lex_keeper class. Now 
      all statements in SP (and corresponding instructions) that
      evaluate expression which can contain subquery have their
      own LEX.
    
    
    mysql-test/r/lock.result:
      Replaced wrong error code with the correct one after fixing bug in
      SP-locking.
    mysql-test/r/mysqldump.result:
      Added dropping of view which is used in test to its beginning.
    mysql-test/r/sp.result:
      Added tests for improved SP-locking.
      Temporarily disabled tests for SHOW PROCEDURE STATUS and alike
      (Until Monty will allow to open mysql.proc under LOCK TABLES without
      mentioning it in lock list).
      Replaced wrong results of test for bug #5240 with correct results after
      fixing bug in handling of cursors.
    mysql-test/t/lock.test:
      Replaced wrong error code with the correct one after fixing bug in
      SP-locking.
    mysql-test/t/mysqldump.test:
      Added dropping of view which is used in test to its beginning.
    mysql-test/t/sp.test:
      Added tests for improved SP-locking.
      Temporarily disabled tests for SHOW PROCEDURE STATUS and alike
      (Until Monty will allow to open mysql.proc under LOCK TABLES without
      mentioning it in lock list).
      Removed test for bug #1654 since we already test exactly this function
      in one of SP-locking tests.
      Removed comment about cursor's wrong behavior in test for bug #5240
      after fixing bug which was its cause.
    sql/item_func.cc:
      Removed comment which is no longer true.
    sql/mysql_priv.h:
      Changed open_tables() signature.
      Now its 2nd parameter is in/out since it can add elements to table list.
    sql/sp.cc:
      sp_find_procedure():
       Added one more parameter which enforces cache only lookup.
      
      sp_merge_hash():
       Now uses its return value to indicate that first of two hashes changed
       as result of merge.
      
      sp_cache_routines():
       This function caches all stored routines used in query now.
    sql/sp.h:
      - sp_find_procedure() now has one more parameter which enforces cache only
        lookup.
      - sp_merge_hash() now uses its return value to indicate that first of two
        hashes changed as result of merge.
      - sp_cache_routines() caches all stored routines now. So it does not need
        third argument any more.
    sql/sp_head.cc:
      sp_head::sp_head():
       Added initialization of new m_spfuns and m_spprocs members.
      
      sp_head::execute():
       Let us save/restore part of thread context which can be damaged by
       execution of instructions.
      sp_head::execute_function()/execute_procedure():
       Now it is responsibility of caller to close tables used in
       subqueries which are passed as routine parameters.
      
      sp_head::restore_lex():
       Let us accumulate information about routines used by this one
       in new m_spfuns, m_spprocs hashes.
      
      sp_lex_keeper::reset_lex_and_exec_core()
       Main method of new auxilary sp_lex_keeper class to which instructions 
       delegate responsibility for handling LEX and preparations before
       executing statement or calculating complex expression.
      
      Since all instructions which calculate complex expression or execute
      command now use sp_lex_keeper they have to implement
      sp_instr::exec_core() method. Most of instruction specific logic
      has moved from sp_instr::execute() to this new method.
      
      Removed sp_instr_set_user_var class which is no longer used, because
      nowdays we allow execution of statements in stored functions and
      triggers.
      
      sp_merge_table_list() became sp_head::merge_table_list() method. It
      also treats sp_head::m_sptabs as multi-set of tables now.
      
      sp_hash_to_table_list() became sp_head::add_used_tables_to_table_list().
      It takes into account that sp_head::m_sptabs is multi-set and allocates
      object into persistent arena of PS.
      
      Removed sp_merge_table_hash(), sp_open_and_lock_tables(),
      sp_unlock_tables(), sp_merge_routine_tables() methods since they are not
      used by new prelocking mechanism.
      
      Added sp_add_sp_tables_to_table_list() which serves for adding tables needed
      by routines used in query to the query table list for prelocking.
    sql/sp_head.h:
      class sp_head:
      - Added m_spfuns, m_spprocs members for storing names of routines used
        by this routine.
      - Added add_used_tables_to_table_list() method which allows to add
        tables needed by this routine to query's table list.
      - Converted sp_merge_table_list() to sp_head::merge_table_list() method.
      - Changed semantics of THD::m_sptabs. Now it is multi-set which contains
        only tables which are used by this routine and not routines that are
        called from this one.
      
      Removed sp_merge_routine_tables(), sp_merge_table_hash(),
      sp_open_and_lock_tables(), sp_unlock_tables() calls since they are not
      used for our prelocking list calculation.
      
      Added auxilary sp_lex_keeper class to which instructions delegate
      responsibility for handling LEX and preparations before executing
      statement or calculating complex expression. This class uses
      new sp_instr::exec_core() method which is responsible for executing
      instruction's core function after all preparations were made.
      
      All instructions which hold and calculate complex expression now have
      their own LEX (by aggregating sp_lex_keeper instance). sp_instr_stmt
      now uses sp_lex_keeper too.
      
      Removed sp_instr_set_user_var class which is no longer used, because
      nowdays we allow execution of statements in stored functions and
      triggers.
    sql/sp_rcontext.cc:
      Now sp_cursor holds pointer to sp_lex_keeper instead of LEX.
    sql/sp_rcontext.h:
      Now sp_cursor holds pointer to sp_lex_keeper instead of LEX.
    sql/sql_acl.cc:
      acl_init(), grant_init():
        Now we use simple_open_n_lock_tables() instead of explicit
        calls to open_tables() and mysql_lock_tables().
    sql/sql_base.cc:
      Implemented support for execution of statements in "prelocked" mode.
      
      When we have statement which uses stored routines explicitly or
      implicitly (via views or triggers) we have to open and lock all tables
      for these routines at the same time as tables for the main statement.
      In fact we have to do implicit LOCK TABLES at the begining of such
      statement and implict UNLOCK TABLES at its end. We call such mode
      "prelocked".
      
      When open_tables() is called for the statement tables which are needed
      for execution of routines used by it are added to its tables list
      (this process also caches all routines used). Implicit use of routines
      is discovered when we open view or table with trigger and apropriate
      tables are added to the table list at this moment. Statement which has
      such extra tables in its list (well actually any that uses functions)
      is marked as requiring prelocked mode for its execution.
      
      When lock_tables() sees such statement it will issue implicit LOCK TABLES
      for this extended table list instead of doing usual locking, it will also
      set THD::prelocked_mode to indicate that we are in prelocked mode.
      
      When open_tables()/lock_tables() are called for statement of stored
      routine (substatement), they notice that we are running in prelocked mode
      and use one of prelocked tables from those that are not used by upper
      levels of execution.
      
      close_thread_tables() for substatement won't really close tables used
      but will mark them as free for reuse instead.
      
      Finally when close_thread_tables() is called for the main statement it
      really unlocks and closes all tables used.
      
      Everything will work even if one uses such statement under real LOCK
      TABLES (we are simply not doing implicit LOCK/UNLOCK in this case).
    sql/sql_class.cc:
      Added initialization of THD::prelocked_mode member.
    sql/sql_class.h:
      - Added prelocked_mode_type enum and THD::prelocked_mode member
        which are used for indication whenever "prelocked mode" is on 
        (i.e. that statement uses stored routines and is executed under
         implicit LOCK TABLES).
      - Removed THD::shortcut_make_view which is no longer needed.
        We use TABLE_LIST::prelocking_placeholder for the same purprose
        now.
    sql/sql_handler.cc:
      Changed open_tables() invocation.
      Now its 2nd parameter is in/out since it can add elements to table list.
    sql/sql_lex.cc:
      lex_start():
        Added initialization of LEX::query_tables_own_last.
        Unused LEX::sptabs member was removed.
      st_lex::unlink_first_table()/link_first_table_back():
        We should update LEX::query_tables_last properly if table list
        contains(ed) only one element.
    sql/sql_lex.h:
      LEX:
      - Removed sptabs member since it is no longer used.
      - Added query_tables_own_last member, which if non-0 indicates that
        statement requires prelocking (implicit LOCK TABLES) for its execution
        and points to last own element in query table list. If it is zero
        then this query does not need prelocking.
      - Added requires_prelocking(), mark_as_requiring_prelocking(),
        first_not_own_table() inline methods to incapsulate and simplify
        usage of this new member.
    sql/sql_parse.cc:
      dispatch_command():
        To properly leave prelocked mode when needed we should call
        close_thread_tables() even if there are no open tables.
      mysql_execute_command():
      - Removed part of function which were responsible for doing implicit
        LOCK TABLES before statement execution if statement used stored 
        routines (and doing UNLOCK TABLES at the end).
        Now we do all this in open_tables()/lock_tables()/close_thread_tables()
        instead.
      - It is also sensible to reset errors before execution of statement
        which uses routines.
      - SQLCOM_DO, SQLCOM_SET_OPTION, SQLCOM_CALL
        We should always try to open tables because even if statement has empty
        table list, it can call routines using tables, which should be preopened
        before statement execution.
      - SQLCOM_CALL
        We should not look up routine called in mysql.proc, since it should be
        already cached by this moment by open_tables() call.
      - SQLCOM_LOCK_TABLES
        it is better to use simple_open_n_lock_tables() since we want to avoid
        materialization of derived tables for this command.
    sql/sql_prepare.cc:
      mysql_test_update():
        Changed open_tables() invocations. Now its 2nd parameter is in/out
        since it can add elements to table list.
      check_prepared_statement():
        Since now we cache all routines used by statement in open_tables() we 
        don't need to do it explicitly.
      mysql_stmt_prepare():
        Now we should call close_thread_tables() when THD::lex points to the
        LEX of statement which opened tables.
      reset_stmt_for_execute():
        Commented why we are resetting all tables in table list.
    sql/sql_trigger.h:
      Table_triggers_list::process_triggers():
        We should surpress sending of ok packet when we are calling trigger's
        routine, since now we allow statements in them.
    sql/sql_update.cc:
      Changed open_tables() invocations.
      Now its 2nd parameter is in/out since it can add elements to table list.
    sql/sql_view.cc:
      mysql_make_view():
      - Removed handling of routines used in view. Instead we add tables which
        are needed for their execution to statement's table list in 
        open_tables().
      - Now we use TABLE_LIST::prelocking_placeholder instead of 
        THD::shortcut_make_view for indicating that view is opened
        only to discover which tables and routines it uses (this happens
        when we build extended table list for prelocking). Also now we try
        to avoid to modify main LEX in this case (except of its table list).
      - Corrected small error we added tables to the table list of the main
        LEX without updating its query_tables_last member properly.
    sql/sql_yacc.yy:
      Now each expression which is used in SP statements and can contain
      subquery has its own LEX. This LEX is stored in corresponding sp_instr
      object and used along with Item tree for expression calculation.
      
      We don't need sp_instr_set_user_var() anymore since now we allow
      execution of statements in stored functions and triggers.
    sql/table.h:
      Added TABLE_LIST::prelocking_placeholder member for distinguishing
      elements of table list which does not belong to the statement itself
      and added there only for prelocking (as they are to be used by routines
      called by this statement).
    sql/tztime.cc:
      my_tz_init():
        Now we use more simplier simple_open_n_lock_tables() call instead of 
        open_tables()/lock_tables() pair.
    ac9f68b9
sql_parse.cc 189 KB