Draft patch that fixes and a sketches test cases for:

Bug#20837 Apparent change of isolation level during transaction,
Bug#46527 COMMIT AND CHAIN RELEASE does not make sense,
Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
preserve the isolation level
Bug#53346 completion_type has strange effect in a stored 
procedure/prepared statement

Make thd->tx_isolation mean strictly "current transaction 
isolation level"
Make thd->variables.tx_isolation mean "current session isolation
level".
The current transaction isolation level is now established
at transaction start. If there was a SET TRANSACTION
ISOLATION LEVEL statement, the value is taken from it.
Otherwise, the session value is used.
A change in a session value, made while a transaction is active,
whereas still allowed, no longer has any effect on the
current transaction isolation level. This is an incompatible
change.
A change in a session isolation level, made while there is
no active transaction, overrides SET TRANSACTION statement,
if there was any.
Changed the impelmentation to not look at @@session.completion_type
in the parser, and thus fixed Bug#53346.
Changed the parser to not allow AND NO CHAIN RELEASE,
and thus fixed Bug#46527.
Changed the transaction API to take the current transaction
isolation level into account:
- BEGIN/COMMIT now do preserve the current transaction
isolation level if chaining is on.
- implicit commit, XA COMMIT or XA ROLLBACK or autocommit don't.

1 files changed, 17 insertions, 9 deletions

diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index aa336f3c072..b49e271c06c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -766,6 +766,7 @@ static bool add_create_index (LEX *lex, Key::Keytype type,
   enum index_hint_type index_hint;
   enum enum_filetype filetype;
   enum Foreign_key::fk_option m_fk_option;
+  enum enum_yes_no_unknown m_yes_no_unk;
   Diag_condition_item_name diag_condition_item_name;
 }
 
@@ -1433,12 +1434,15 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         table_option opt_if_not_exists opt_no_write_to_binlog
         opt_temporary all_or_any opt_distinct
         opt_ignore_leaves fulltext_options spatial_type union_option
-        start_transaction_opts opt_chain opt_release
+        start_transaction_opts
         union_opt select_derived_init option_type2
         opt_natural_language_mode opt_query_expansion
         opt_ev_status opt_ev_on_completion ev_on_completion opt_ev_comment
         ev_alter_on_schedule_completion opt_ev_rename_to opt_ev_sql_stmt
 
+%type <m_yes_no_unk>
+        opt_chain opt_release
+
 %type <m_fk_option>
         delete_option
 
@@ -13546,16 +13550,16 @@ opt_work:
 
 opt_chain:
           /* empty */
-          { $$= (YYTHD->variables.completion_type == 1); }
-        | AND_SYM NO_SYM CHAIN_SYM { $$=0; }
-        | AND_SYM CHAIN_SYM        { $$=1; }
+          { $$= TVL_UNKNOWN; }
+        | AND_SYM NO_SYM CHAIN_SYM { $$= TVL_NO; }
+        | AND_SYM CHAIN_SYM        { $$= TVL_YES; }
         ;
 
 opt_release:
           /* empty */
-          { $$= (YYTHD->variables.completion_type == 2); }
-        | RELEASE_SYM        { $$=1; }
-        | NO_SYM RELEASE_SYM { $$=0; }
+          { $$= TVL_UNKNOWN; }
+        | RELEASE_SYM        { $$= TVL_YES; }
+        | NO_SYM RELEASE_SYM { $$= TVL_NO; }
 ;
 
 opt_savepoint:
@@ -13568,7 +13572,9 @@ commit:
           {
             LEX *lex=Lex;
             lex->sql_command= SQLCOM_COMMIT;
-            lex->tx_chain= $3; 
+            /* Don't allow AND CHAIN RELEASE. */
+            MYSQL_YYABORT_UNLESS($3 != TVL_YES || $4 != TVL_YES);
+            lex->tx_chain= $3;
             lex->tx_release= $4;
           }
         ;
@@ -13578,7 +13584,9 @@ rollback:
           {
             LEX *lex=Lex;
             lex->sql_command= SQLCOM_ROLLBACK;
-            lex->tx_chain= $3; 
+            /* Don't allow AND CHAIN RELEASE. */
+            MYSQL_YYABORT_UNLESS($3 != TVL_YES || $4 != TVL_YES);
+            lex->tx_chain= $3;
             lex->tx_release= $4;
           }
         | ROLLBACK_SYM opt_work