From 11abe15eab3f444b600200e965cf18539af55392 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 30 Jul 2005 08:19:57 +0000 Subject: Added Non-prelocked SP execution: Now a PROCEDURE doesn't enter/leave prelocked mode for its body, but lets each statement to get/release its own locks. This allows a broader set of statements to be executed inside PROCEDUREs (but breaks replication) This patch should fix BUG#8072, BUG#8766, BUG#9563, BUG#11126 mysql-test/r/sp-security.result: Drop tables this test attempts to create mysql-test/r/sp-threads.result: Update test results mysql-test/r/sp.result: Disabled a test that triggers BUG#11986, cleanup used tables when tests start. mysql-test/r/view.result: Enabled a test case that now works with prelocking-free SPs mysql-test/t/sp-security.test: Drop tables this test attempts to create mysql-test/t/sp.test: Disabled a test that triggers BUG#11986, cleanup used tables when tests start. mysql-test/t/view.test: Enabled a test case that now works with prelocking-free SPs sql/handler.cc: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt sql/item_func.cc: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt sql/sp.cc: Non-prelocked SP execution: Added support for skipping prelocking of procedure body for "CALL proc(...)" statements. sql/sp.h: Non-prelocked SP execution: Added support for skipping prelocking of procedure body for "CALL proc(...)" statements. sql/sp_cache.h: Added comments sql/sp_head.cc: Non-prelocked SP execution: * Try to unlock tables after PROCEDURE arguments have been evaluated. * Make sp_lex_keeper be able to execute in 2 modes: A) when already in prelocked mode B) when its statement enters/leaves prelocked mode itself. sql/sp_head.h: Non-prelocked SP execution: Make sp_lex_keeper to additionally keep list of tables it needs to prelock when its statement enters/leaves prelocked mode on its own. sql/sql_base.cc: Non-prelocked SP execution: Make open_tables() to * detect 'CALL proc(...)' and not to do prelocking for procedure body statements. * Make lex->query_tables_last to point precisely to a boundary in lex->query_tables list where 'own' tables and views' tables end and added-for-prelocking tables begin. (it was not true before - view's tables could end up after query_tables_own_last) sql/sql_class.cc: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt sql/sql_class.h: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt sql/sql_lex.cc: Non-prelocked SP execution: More rigourous cleanup in st_lex::cleanup_after_one_table_open() sql/sql_parse.cc: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt, remove outdated comments sql/sql_trigger.h: Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt --- sql/handler.cc | 4 +-- sql/item_func.cc | 6 ++-- sql/sp.cc | 84 +++++++++++++++++++++++++++++++++++++++++++------------ sql/sp.h | 5 +++- sql/sp_cache.h | 16 +++++++++-- sql/sp_head.cc | 71 +++++++++++++++++++++++++++++++++++++++++++++- sql/sp_head.h | 26 ++++++++++++++++- sql/sql_base.cc | 54 ++++++++++++++++++++++++++--------- sql/sql_class.cc | 2 +- sql/sql_class.h | 6 ++-- sql/sql_lex.cc | 1 + sql/sql_parse.cc | 22 +++++---------- sql/sql_trigger.h | 6 ++-- 13 files changed, 240 insertions(+), 63 deletions(-) (limited to 'sql') diff --git a/sql/handler.cc b/sql/handler.cc index 2731cb8480d..1f13f0d5e36 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -598,7 +598,7 @@ int ha_commit_trans(THD *thd, bool all) my_xid xid= thd->transaction.xid.get_my_xid(); DBUG_ENTER("ha_commit_trans"); - if (thd->transaction.in_sub_stmt) + if (thd->in_sub_stmt) { /* Since we don't support nested statement transactions in 5.0, @@ -717,7 +717,7 @@ int ha_rollback_trans(THD *thd, bool all) THD_TRANS *trans=all ? &thd->transaction.all : &thd->transaction.stmt; bool is_real_trans=all || thd->transaction.all.nht == 0; DBUG_ENTER("ha_rollback_trans"); - if (thd->transaction.in_sub_stmt) + if (thd->in_sub_stmt) { /* If we are inside stored function or trigger we should not commit or diff --git a/sql/item_func.cc b/sql/item_func.cc index 53895cc7331..fb39a62810c 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -4844,7 +4844,7 @@ Item_func_sp::execute(Item **itp) THD *thd= current_thd; ulong old_client_capabilites; int res= -1; - bool save_in_sub_stmt= thd->transaction.in_sub_stmt; + bool save_in_sub_stmt= thd->in_sub_stmt; my_bool save_no_send_ok; #ifndef NO_EMBEDDED_ACCESS_CHECKS st_sp_security_context save_ctx; @@ -4882,11 +4882,11 @@ Item_func_sp::execute(Item **itp) */ tmp_disable_binlog(thd); /* don't binlog the substatements */ - thd->transaction.in_sub_stmt= TRUE; + thd->in_sub_stmt= TRUE; res= m_sp->execute_function(thd, args, arg_count, itp); - thd->transaction.in_sub_stmt= save_in_sub_stmt; + thd->in_sub_stmt= save_in_sub_stmt; reenable_binlog(thd); if (res && mysql_bin_log.is_open() && (m_sp->m_chistics->daccess == SP_CONTAINS_SQL || diff --git a/sql/sp.cc b/sql/sp.cc index a277c6bd253..3d513b16d07 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1175,6 +1175,44 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first) } +/* + Check if routines in routines_list require sp_cache_routines_and_add_tables + call. + + SYNOPSIS + sp_need_cache_routines() + thd + routines + need_skip_first OUT TRUE - don't do prelocking for the 1st element in + routines list. + FALSE- otherwise + NOTES + This function assumes that for any "CALL proc(...)" statement routines_list + will have 'proc' as first element (it may have several, consider e.g. + "proc(sp_func(...)))". This property is currently guaranted by the parser. + + RETURN + TRUE Need to sp_cache_routines_and_add_tables call for this statement. + FALSE Otherwise. +*/ + +bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, bool *need_skip_first) +{ + Sroutine_hash_entry *routine; + routine= (Sroutine_hash_entry*)routines_list->first; + + *need_skip_first= FALSE; + if (!routine) + return FALSE; + + if (routine->key.str[0] != TYPE_ENUM_PROCEDURE) + return TRUE; + + *need_skip_first= TRUE; + return TRUE; +} + + /* Auxilary function that adds new element to the set of stored routines used by statement. @@ -1312,11 +1350,13 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src) SYNOPSIS sp_cache_routines_and_add_tables_aux() - thd - thread context - lex - LEX representing statement - start - first routine from the list of routines to be cached - (this list defines mentioned sub-set). - + thd - thread context + lex - LEX representing statement + start - first routine from the list of routines to be cached + (this list defines mentioned sub-set). + first_no_prelock - If true, don't add tables or cache routines used by + the body of the first routine (i.e. *start) + will be executed in non-prelocked mode. NOTE If some function is missing this won't be reported here. Instead this fact will be discovered during query execution. @@ -1328,10 +1368,11 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src) static bool sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex, - Sroutine_hash_entry *start) + Sroutine_hash_entry *start, + bool first_no_prelock) { bool result= FALSE; - + bool first= TRUE; DBUG_ENTER("sp_cache_routines_and_add_tables_aux"); for (Sroutine_hash_entry *rt= start; rt; rt= rt->next) @@ -1367,9 +1408,13 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex, } if (sp) { - sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines); - result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last); + if (!(first && first_no_prelock)) + { + sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines); + result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last); + } } + first= FALSE; } DBUG_RETURN(result); } @@ -1382,20 +1427,22 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex, SYNOPSIS sp_cache_routines_and_add_tables() - thd - thread context - lex - LEX representing statement - + thd - thread context + lex - LEX representing statement + first_no_prelock - If true, don't add tables or cache routines used by + the body of the first routine (i.e. *start) + RETURN VALUE TRUE - some tables were added FALSE - no tables were added. */ bool -sp_cache_routines_and_add_tables(THD *thd, LEX *lex) +sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock) { - return sp_cache_routines_and_add_tables_aux(thd, lex, - (Sroutine_hash_entry *)lex->sroutines_list.first); + (Sroutine_hash_entry *)lex->sroutines_list.first, + first_no_prelock); } @@ -1417,8 +1464,8 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex) Sroutine_hash_entry **last_cached_routine_ptr= (Sroutine_hash_entry **)lex->sroutines_list.next; sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines); - (void)sp_cache_routines_and_add_tables_aux(thd, lex, - *last_cached_routine_ptr); + (void)sp_cache_routines_and_add_tables_aux(thd, lex, + *last_cached_routine_ptr, FALSE); } @@ -1453,7 +1500,8 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex, } (void)sp_cache_routines_and_add_tables_aux(thd, lex, - *last_cached_routine_ptr); + *last_cached_routine_ptr, + FALSE); } } diff --git a/sql/sp.h b/sql/sp.h index b8af8d3a321..716f3d90f55 100644 --- a/sql/sp.h +++ b/sql/sp.h @@ -79,10 +79,13 @@ sp_show_status_function(THD *thd, const char *wild); Procedures for pre-caching of stored routines and building table list for prelocking. */ +bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, + bool *need_skip_first); void sp_add_used_routine(LEX *lex, Query_arena *arena, sp_name *rt, char rt_type); void sp_update_sp_used_routines(HASH *dst, HASH *src); -bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex); +bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, + bool first_no_prelock); void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex); void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex, diff --git a/sql/sp_cache.h b/sql/sp_cache.h index e9efe5b2a8c..5873c763289 100644 --- a/sql/sp_cache.h +++ b/sql/sp_cache.h @@ -22,6 +22,12 @@ #pragma interface /* gcc class implementation */ #endif +/* + Stored procedures/functions cache. This is used as follows: + * Each thread has its own cache. + * When SP is used it is always in some thread's cache. +*/ + class sp_head; class sp_cache; @@ -31,16 +37,20 @@ void sp_cache_init(); /* Clear the cache *cp and set *cp to NULL */ void sp_cache_clear(sp_cache **cp); -/* Insert an SP to cache. If 'cp' points to NULL, it's set to a new cache */ +/* Insert an SP into cache. If 'cp' points to NULL, it's set to a new cache */ void sp_cache_insert(sp_cache **cp, sp_head *sp); /* Lookup an SP in cache */ sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name); -/* Remove an SP from cache. Returns true if something was removed */ +/* + Remove an SP from cache, and also bump the Cversion number so all other + caches are invalidated. + Returns true if something was removed. +*/ bool sp_cache_remove(sp_cache **cp, sp_name *name); -/* Invalidate a cache */ +/* Invalidate all existing SP caches by bumping Cversion number. */ void sp_cache_invalidate(); diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 02c006d01ee..8d56e2a0b38 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -879,7 +879,10 @@ sp_head::execute_procedure(THD *thd, List *args) octx= new sp_rcontext(csize, hmax, cmax); tmp_octx= TRUE; } + + /* Evaluate SP arguments (i.e. get the values passed as parameters) */ // QQ: Should do type checking? + DBUG_PRINT("info",(" %.*s: eval args", m_name.length, m_name.str)); for (i = 0 ; (it= li++) && i < params ; i++) { sp_pvar_t *pvar= m_pcont->find_pvar(i); @@ -916,6 +919,14 @@ sp_head::execute_procedure(THD *thd, List *args) } } + /* + Okay, got values for all arguments. Close tables that might be used by + arguments evaluation. + */ + if (!thd->in_sub_stmt) + close_thread_tables(thd, 0, 0, 0); + + DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str)); // The rest of the frame are local variables which are all IN. // Default all variables to null (those with default clauses will // be set by an set instruction). @@ -1480,8 +1491,37 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, implemented at the same time as ability not to store LEX for instruction if it is not really used. */ - reinit_stmt_before_use(thd, m_lex); + bool collect_prelocking_tail= FALSE; + + if (thd->prelocked_mode == NON_PRELOCKED) + { + /* + This statement will enter/leave prelocked mode on its own. + Entering prelocked mode changes table list and related members + of LEX, so we'll need to restore them. + */ + if (lex_query_tables_own_last) + { + /* + We've already entered/left prelocked mode with this statement. + Attach the list of tables that need to be prelocked and mark m_lex + as having such list attached. + */ + *lex_query_tables_own_last= prelocking_tables; + m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last); + } + else + { + /* + Let open_tables_calculate list of tables that this statement needs + to have prelocked. + */ + collect_prelocking_tail= TRUE; + } + } + + reinit_stmt_before_use(thd, m_lex); /* If requested check whenever we have access to tables in LEX's table list and open and lock them before executing instructtions core function. @@ -1499,6 +1539,35 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, thd->proc_info="closing tables"; close_thread_tables(thd); + if (thd->prelocked_mode == NON_PRELOCKED) + { + if (!lex_query_tables_own_last) + lex_query_tables_own_last= thd->lex->query_tables_own_last; + + if (lex_query_tables_own_last) + { + if (collect_prelocking_tail) + { + /* + This is the first time this statement has entered/left prelocked + mode on its own. open_tables() has calculated the set of tables this + statement needs to have prelocked and added them to the end of + m_lex->query_tables(->next_global)*. + Save this "tail" for subsequent calls (and restore original list + below) + */ + lex_query_tables_own_last= m_lex->query_tables_own_last; + prelocking_tables= *lex_query_tables_own_last; + } + /* + The table list now has list of tables that need to be prelocked + when this statement executes, chop it off, and mark this statement + as not requiring prelocking. + */ + *lex_query_tables_own_last= NULL; + m_lex->mark_as_requiring_prelocking(NULL); + } + } thd->rollback_item_tree_changes(); /* diff --git a/sql/sp_head.h b/sql/sp_head.h index 32dc4449174..724cbcc7fc5 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -282,6 +282,10 @@ private: /* Multi-set representing optimized list of tables to be locked by this routine. Does not include tables which are used by invoked routines. + + Note: for prelocking-free SPs this multiset is constructed too. + We do so because the same instance of sp_head may be called both + in prelocked mode and in non-prelocked mode. */ HASH m_sptabs; @@ -383,7 +387,8 @@ class sp_lex_keeper public: sp_lex_keeper(LEX *lex, bool lex_resp) - : m_lex(lex), m_lex_resp(lex_resp) + : m_lex(lex), m_lex_resp(lex_resp), + lex_query_tables_own_last(NULL) { lex->sp_lex_in_use= TRUE; } @@ -418,6 +423,25 @@ private: for LEX deletion. */ bool m_lex_resp; + + /* + Support for being able to execute this statement in two modes: + a) inside prelocked mode set by the calling procedure or its ancestor. + b) outside of prelocked mode, when this statement enters/leaves + prelocked mode itself. + */ + + /* + List of additional tables this statement needs to lock when it + enters/leaves prelocked mode on its own. + */ + TABLE_LIST *prelocking_tables; + + /* + The value m_lex->query_tables_own_last should be set to this when the + statement enters/leaves prelocked mode on its own. + */ + TABLE_LIST **lex_query_tables_own_last; }; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 65bb0ec047b..367bd2c5ade 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -391,6 +391,8 @@ static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table) LOCK_open skip_derived Set to 1 (0 = default) if we should not free derived tables. + stopper When closing tables from thd->open_tables(->next)*, + don't close/remove tables starting from stopper. IMPLEMENTATION Unlocks tables and frees derived tables. @@ -474,6 +476,7 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, We are in prelocked mode, so we have to leave it now with doing implicit UNLOCK TABLES if need. */ + DBUG_PRINT("info",("thd->prelocked_mode= NON_PRELOCKED")); thd->prelocked_mode= NON_PRELOCKED; if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES) @@ -1792,6 +1795,7 @@ err: DBUG_RETURN(1); } + /* Open all tables in list @@ -1843,10 +1847,6 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter) statement for which table list for prelocking is already built, let us cache routines and try to build such table list. - NOTE: If we want queries with functions to work under explicit - LOCK TABLES we have to additionaly lock mysql.proc table in it. - At least until Monty will fix SP loading :) - NOTE: We can't delay prelocking until we will met some sub-statement which really uses tables, since this will imply that we have to restore its table list to be able execute it in some other context. @@ -1860,19 +1860,28 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter) mode we will have some locked tables, because queries which use only derived/information schema tables and views possible. Thus "counter" may be still zero for prelocked statement... + + NOTE: The above notes may be out of date. Please wait for psergey to + document new prelocked behavior. */ - if (!thd->prelocked_mode && !thd->lex->requires_prelocking() && - thd->lex->sroutines.records) + + if (!thd->prelocked_mode && !thd->lex->requires_prelocking()) { - TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last; + bool first_no_prelocking; + if (sp_need_cache_routines(thd, &thd->lex->sroutines_list, + &first_no_prelocking)) + { + TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last; - DBUG_ASSERT(thd->lex->query_tables == *start); + DBUG_ASSERT(thd->lex->query_tables == *start); - if (sp_cache_routines_and_add_tables(thd, thd->lex) || - *start) - { - query_tables_last_own= save_query_tables_last; - *start= thd->lex->query_tables; + if (sp_cache_routines_and_add_tables(thd, thd->lex, + first_no_prelocking) || + *start) + { + query_tables_last_own= save_query_tables_last; + *start= thd->lex->query_tables; + } } } @@ -1891,14 +1900,31 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter) DBUG_RETURN(-1); } (*counter)++; + if (!tables->table && !(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, 0))) { free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC)); + if (tables->view) { /* VIEW placeholder */ (*counter)--; + + /* + tables->next_global list consists of two parts: + 1) Query tables and underlying tables of views. + 2) Tables used by all stored routines that this statement invokes on + execution. + We need to know where the bound between these two parts is. If we've + just opened the last table in part #1, and it added tables after + itself, adjust the boundary pointer accordingly. + */ + if (query_tables_last_own && + query_tables_last_own == &(tables->next_global) && + tables->view->query_tables) + query_tables_last_own= tables->view->query_tables_last; + /* Again if needed we have to get cache all routines used by this view and add tables used by them to table list. @@ -2323,6 +2349,7 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count) and was marked as occupied during open_tables() as free for reuse. */ mark_real_tables_as_free_for_reuse(first_not_own); + DBUG_PRINT("info",("prelocked_mode= PRELOCKED")); thd->prelocked_mode= PRELOCKED; } } @@ -2346,6 +2373,7 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count) if (thd->lex->requires_prelocking()) { mark_real_tables_as_free_for_reuse(first_not_own); + DBUG_PRINT("info", ("thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES")); thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES; } } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 89d5b543dfc..81cdc6562e9 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -178,7 +178,7 @@ THD::THD() rand_used(0), time_zone_used(0), last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0), in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE), - spcont(NULL) + spcont(NULL), in_sub_stmt(FALSE) { current_arena= this; host= user= priv_user= db= ip= 0; diff --git a/sql/sql_class.h b/sql/sql_class.h index d6847f5fb35..26a861bcc5d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1132,6 +1132,10 @@ public: thr_lock_type update_lock_default; delayed_insert *di; my_bool tablespace_op; /* This is TRUE in DISCARD/IMPORT TABLESPACE */ + + /* TRUE if we are inside of trigger or stored function. */ + bool in_sub_stmt; + /* container for handler's private per-connection data */ void *ha_data[MAX_HA]; struct st_transactions { @@ -1139,8 +1143,6 @@ public: THD_TRANS all; // Trans since BEGIN WORK THD_TRANS stmt; // Trans for current statement bool on; // see ha_enable_transaction() - /* TRUE if we are inside of trigger or stored function. */ - bool in_sub_stmt; XID xid; // transaction identifier enum xa_states xa_state; // used by external XA only /* diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 630a7e950f7..c66bfcbe78d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2008,6 +2008,7 @@ void st_lex::cleanup_after_one_table_open() time_zone_tables_used= 0; if (sroutines.records) my_hash_reset(&sroutines); + sroutines_list.empty(); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 23403e6e00a..6a0b5586598 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -27,6 +27,7 @@ #include "sp_head.h" #include "sp.h" +#include "sp_cache.h" #ifdef HAVE_OPENSSL /* @@ -124,7 +125,7 @@ static bool end_active_trans(THD *thd) { int error=0; DBUG_ENTER("end_active_trans"); - if (unlikely(thd->transaction.in_sub_stmt)) + if (unlikely(thd->in_sub_stmt)) { my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); DBUG_RETURN(1); @@ -147,11 +148,7 @@ static bool end_active_trans(THD *thd) static bool begin_trans(THD *thd) { int error=0; - /* - QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in - stored routines as SQL2003 suggests? - */ - if (unlikely(thd->transaction.in_sub_stmt)) + if (unlikely(thd->in_sub_stmt)) { my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); return 1; @@ -1340,11 +1337,7 @@ int end_trans(THD *thd, enum enum_mysql_completiontype completion) int res= 0; DBUG_ENTER("end_trans"); - /* - QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in - stored routines as SQL2003 suggests? - */ - if (unlikely(thd->transaction.in_sub_stmt)) + if (unlikely(thd->in_sub_stmt)) { my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); DBUG_RETURN(1); @@ -4128,9 +4121,8 @@ end_with_restore_list: goto error; /* - By this moment all needed SPs should be in cache so no need - to look into DB. Moreover we may be unable to do it becuase - we may don't have read lock on mysql.proc + By this moment all needed SPs should be in cache so no need to look + into DB. */ if (!(sp= sp_find_procedure(thd, lex->spname, TRUE))) { @@ -4195,7 +4187,7 @@ end_with_restore_list: select_limit= thd->variables.select_limit; thd->variables.select_limit= HA_POS_ERROR; - thd->row_count_func= 0; + thd->row_count_func= 0; tmp_disable_binlog(thd); /* don't binlog the substatements */ res= sp->execute_procedure(thd, &lex->value_list); reenable_binlog(thd); diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h index e751741fa34..e74297461d3 100644 --- a/sql/sql_trigger.h +++ b/sql/sql_trigger.h @@ -78,7 +78,7 @@ public: if (bodies[event][time_type]) { - bool save_in_sub_stmt= thd->transaction.in_sub_stmt; + bool save_in_sub_stmt= thd->in_sub_stmt; #ifndef EMBEDDED_LIBRARY /* Surpress OK packets in case if we will execute statements */ my_bool nsok= thd->net.no_send_ok; @@ -107,11 +107,11 @@ public: does NOT go into binlog. */ tmp_disable_binlog(thd); - thd->transaction.in_sub_stmt= TRUE; + thd->in_sub_stmt= TRUE; res= bodies[event][time_type]->execute_function(thd, 0, 0, 0); - thd->transaction.in_sub_stmt= save_in_sub_stmt; + thd->in_sub_stmt= save_in_sub_stmt; reenable_binlog(thd); #ifndef EMBEDDED_LIBRARY -- cgit v1.2.1 From b323667ffc7176482073ca7d626560dcd8d7ee75 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 3 Aug 2005 03:37:32 +0000 Subject: Prelocking-free SPs, post-review fixes: * Don't activate prelocking mode for evaluating procedure arguments when it is not necessary. * Code structure simplification and cleanup. * Cleanup in .test files mysql-test/r/sp-prelocking.result: Prelocking-free SPs, post-review fixes: Added comment, s/testdb/mysqltest/, fixed a wrong test (error wasnt reported because of known bug in mysqltestrun) mysql-test/r/sp-security.result: Don't drop the table we're not using. mysql-test/r/sp.result: Prelocking-free SPs, post-review fixes: remove redundant "drop table if exists t3" statements mysql-test/t/sp-prelocking.test: Prelocking-free SPs, post-review fixes: Added comment, s/testdb/mysqltest/, fixed a wrong test (error wasnt reported because of known bug in mysqltestrun) mysql-test/t/sp-security.test: Don't drop the table we're not using. mysql-test/t/sp.test: Prelocking-free SPs, post-review fixes: remove redundant "drop table if exists t3" statements sql/sp.cc: New, better defined, sp_get_prelocking_info() function to get info about statement prelocking options sql/sp.h: Prelocking-free SPs, post-review fixes: New, better defined, sp_get_prelocking_info() function to get info about statement prelocking options sql/sp_cache.h: Prelocking-free SPs, post-review fixes: Amended the comments sql/sp_head.cc: Prelocking-free SPs, post-review fixes: Amend the comments, simplify the code that attaches removes statement's prelocking tables. sql/sql_base.cc: Prelocking-free SPs, post-review fixes: * Use a better defined sp_get_prelocking_info() function to get info about statement prelocking options * Don't activate prelocked mode for evaluation of SP arguments that use tables but don't need prelocking. sql/sql_class.cc: Prelocking-free SPs, post-review fixes: Initialize THD members in the order they are declared. --- sql/sp.cc | 43 ++++++++++++++++++++--------------------- sql/sp.h | 4 ++-- sql/sp_cache.h | 3 ++- sql/sp_head.cc | 58 +++++++++++++++++++------------------------------------- sql/sql_base.cc | 31 +++++++++++++++--------------- sql/sql_class.cc | 4 ++-- 6 files changed, 62 insertions(+), 81 deletions(-) (limited to 'sql') diff --git a/sql/sp.cc b/sql/sp.cc index 3d513b16d07..0cc1c988217 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1176,40 +1176,39 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first) /* - Check if routines in routines_list require sp_cache_routines_and_add_tables - call. + Check if + - current statement (the one in thd->lex) needs table prelocking + - first routine in thd->lex->sroutines_list needs to execute its body in + prelocked mode. SYNOPSIS - sp_need_cache_routines() - thd - routines - need_skip_first OUT TRUE - don't do prelocking for the 1st element in - routines list. - FALSE- otherwise + sp_get_prelocking_info() + thd Current thread, thd->lex is the statement to be + checked. + need_prelocking OUT TRUE - prelocked mode should be activated + before executing the statement + FALSE - Don't activate prelocking + first_no_prelocking OUT TRUE - Tables used by first routine in + thd->lex->sroutines_list should be + prelocked. + FALSE - Otherwise. NOTES This function assumes that for any "CALL proc(...)" statement routines_list will have 'proc' as first element (it may have several, consider e.g. "proc(sp_func(...)))". This property is currently guaranted by the parser. - - RETURN - TRUE Need to sp_cache_routines_and_add_tables call for this statement. - FALSE Otherwise. */ -bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, bool *need_skip_first) +void sp_get_prelocking_info(THD *thd, bool *need_prelocking, + bool *first_no_prelocking) { Sroutine_hash_entry *routine; - routine= (Sroutine_hash_entry*)routines_list->first; - - *need_skip_first= FALSE; - if (!routine) - return FALSE; + routine= (Sroutine_hash_entry*)thd->lex->sroutines_list.first; - if (routine->key.str[0] != TYPE_ENUM_PROCEDURE) - return TRUE; + DBUG_ASSERT(routine); + bool first_is_procedure= (routine->key.str[0] == TYPE_ENUM_PROCEDURE); - *need_skip_first= TRUE; - return TRUE; + *first_no_prelocking= first_is_procedure; + *need_prelocking= !first_is_procedure || test(routine->next); } diff --git a/sql/sp.h b/sql/sp.h index 716f3d90f55..3c837f8b586 100644 --- a/sql/sp.h +++ b/sql/sp.h @@ -79,8 +79,8 @@ sp_show_status_function(THD *thd, const char *wild); Procedures for pre-caching of stored routines and building table list for prelocking. */ -bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, - bool *need_skip_first); +void sp_get_prelocking_info(THD *thd, bool *need_prelocking, + bool *first_no_prelocking); void sp_add_used_routine(LEX *lex, Query_arena *arena, sp_name *rt, char rt_type); void sp_update_sp_used_routines(HASH *dst, HASH *src); diff --git a/sql/sp_cache.h b/sql/sp_cache.h index 5873c763289..1ea71160a3a 100644 --- a/sql/sp_cache.h +++ b/sql/sp_cache.h @@ -25,7 +25,8 @@ /* Stored procedures/functions cache. This is used as follows: * Each thread has its own cache. - * When SP is used it is always in some thread's cache. + * Each sp_head object is put into its thread cache after creation and is + removed from there on its deletion. */ class sp_head; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 8d56e2a0b38..7002bc7b020 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -921,7 +921,8 @@ sp_head::execute_procedure(THD *thd, List *args) /* Okay, got values for all arguments. Close tables that might be used by - arguments evaluation. + arguments evaluation. If arguments evaluation required prelocking mode, + we'll leave it here. */ if (!thd->in_sub_stmt) close_thread_tables(thd, 0, 0, 0); @@ -1492,8 +1493,6 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, instruction if it is not really used. */ - bool collect_prelocking_tail= FALSE; - if (thd->prelocked_mode == NON_PRELOCKED) { /* @@ -1511,14 +1510,6 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, *lex_query_tables_own_last= prelocking_tables; m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last); } - else - { - /* - Let open_tables_calculate list of tables that this statement needs - to have prelocked. - */ - collect_prelocking_tail= TRUE; - } } reinit_stmt_before_use(thd, m_lex); @@ -1539,34 +1530,25 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, thd->proc_info="closing tables"; close_thread_tables(thd); - if (thd->prelocked_mode == NON_PRELOCKED) + if (m_lex->query_tables_own_last) { - if (!lex_query_tables_own_last) - lex_query_tables_own_last= thd->lex->query_tables_own_last; - - if (lex_query_tables_own_last) - { - if (collect_prelocking_tail) - { - /* - This is the first time this statement has entered/left prelocked - mode on its own. open_tables() has calculated the set of tables this - statement needs to have prelocked and added them to the end of - m_lex->query_tables(->next_global)*. - Save this "tail" for subsequent calls (and restore original list - below) - */ - lex_query_tables_own_last= m_lex->query_tables_own_last; - prelocking_tables= *lex_query_tables_own_last; - } - /* - The table list now has list of tables that need to be prelocked - when this statement executes, chop it off, and mark this statement - as not requiring prelocking. - */ - *lex_query_tables_own_last= NULL; - m_lex->mark_as_requiring_prelocking(NULL); - } + /* + We've entered and left prelocking mode when executing statement + stored in m_lex. + m_lex->query_tables(->next_global)* list now has a 'tail' - a list + of tables that are added for prelocking. (If this is the first + execution, the 'tail' was added by open_tables(), otherwise we've + attached it above in this function). + Now we'll save the 'tail', and detach it. + */ + DBUG_ASSERT(!lex_query_tables_own_last || + lex_query_tables_own_last == m_lex->query_tables_own_last && + prelocking_tables == *(m_lex->query_tables_own_last)); + + lex_query_tables_own_last= m_lex->query_tables_own_last; + prelocking_tables= *lex_query_tables_own_last; + *lex_query_tables_own_last= NULL; + m_lex->mark_as_requiring_prelocking(NULL); } thd->rollback_item_tree_changes(); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 367bd2c5ade..90e31edd9bb 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1865,23 +1865,21 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter) document new prelocked behavior. */ - if (!thd->prelocked_mode && !thd->lex->requires_prelocking()) + if (!thd->prelocked_mode && !thd->lex->requires_prelocking() && + thd->lex->sroutines_list.elements) { - bool first_no_prelocking; - if (sp_need_cache_routines(thd, &thd->lex->sroutines_list, - &first_no_prelocking)) - { - TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last; + bool first_no_prelocking, need_prelocking; + TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last; - DBUG_ASSERT(thd->lex->query_tables == *start); + DBUG_ASSERT(thd->lex->query_tables == *start); + sp_get_prelocking_info(thd, &need_prelocking, &first_no_prelocking); - if (sp_cache_routines_and_add_tables(thd, thd->lex, - first_no_prelocking) || - *start) - { - query_tables_last_own= save_query_tables_last; - *start= thd->lex->query_tables; - } + if ((sp_cache_routines_and_add_tables(thd, thd->lex, + first_no_prelocking) || + *start) && need_prelocking) + { + query_tables_last_own= save_query_tables_last; + *start= thd->lex->query_tables; } } @@ -1917,8 +1915,9 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter) 2) Tables used by all stored routines that this statement invokes on execution. We need to know where the bound between these two parts is. If we've - just opened the last table in part #1, and it added tables after - itself, adjust the boundary pointer accordingly. + just opened a view, which was the last table in part #1, and it + has added its base tables after itself, adjust the boundary pointer + accordingly. */ if (query_tables_last_own && query_tables_last_own == &(tables->next_global) && diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 81cdc6562e9..9b0decb3950 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -174,11 +174,11 @@ THD::THD() :Statement(CONVENTIONAL_EXECUTION, 0, ALLOC_ROOT_MIN_BLOCK_SIZE, 0), Open_tables_state(), lock_id(&main_lock_id), - user_time(0), global_read_lock(0), is_fatal_error(0), + user_time(0), in_sub_stmt(FALSE), global_read_lock(0), is_fatal_error(0), rand_used(0), time_zone_used(0), last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0), in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE), - spcont(NULL), in_sub_stmt(FALSE) + spcont(NULL) { current_arena= this; host= user= priv_user= db= ip= 0; -- cgit v1.2.1