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, 47 insertions, 5 deletions

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c8faac7c91d..2443b71dbc9 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4033,33 +4033,65 @@ end_with_restore_list:
     my_ok(thd);
     break;
   case SQLCOM_COMMIT:
+  {
     DBUG_ASSERT(thd->lock == NULL ||
                 thd->locked_tables_mode == LTM_LOCK_TABLES);
+    bool tx_chain= (lex->tx_chain == TVL_YES ||
+                    (thd->variables.completion_type == 1 &&
+                     lex->tx_chain != TVL_NO));
+    bool tx_release= (lex->tx_release == TVL_YES ||
+                      (thd->variables.completion_type == 2 &&
+                       lex->tx_release != TVL_NO));
     if (trans_commit(thd))
       goto error;
     thd->mdl_context.release_transactional_locks();
     /* Begin transaction with the same isolation level. */
-    if (lex->tx_chain && trans_begin(thd))
+    if (tx_chain)
+    {
+      if (trans_begin(thd))
       goto error;
+    }
+    else
+    {
+      /* Reset the isolation level if no chaining transaction. */
+      thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+    }
     /* Disconnect the current client connection. */
-    if (lex->tx_release)
+    if (tx_release)
       thd->killed= THD::KILL_CONNECTION;
     my_ok(thd);
     break;
+  }
   case SQLCOM_ROLLBACK:
+  {
     DBUG_ASSERT(thd->lock == NULL ||
                 thd->locked_tables_mode == LTM_LOCK_TABLES);
+    bool tx_chain= (lex->tx_chain == TVL_YES ||
+                    (thd->variables.completion_type == 1 &&
+                     lex->tx_chain != TVL_NO));
+    bool tx_release= (lex->tx_release == TVL_YES ||
+                      (thd->variables.completion_type == 2 &&
+                       lex->tx_release != TVL_NO));
     if (trans_rollback(thd))
       goto error;
     thd->mdl_context.release_transactional_locks();
     /* Begin transaction with the same isolation level. */
-    if (lex->tx_chain && trans_begin(thd))
-      goto error;
+    if (tx_chain)
+    {
+      if (trans_begin(thd))
+        goto error;
+    }
+    else
+    {
+      /* Reset the isolation level if no chaining transaction. */
+      thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
+    }
     /* Disconnect the current client connection. */
-    if (lex->tx_release)
+    if (tx_release)
       thd->killed= THD::KILL_CONNECTION;
     my_ok(thd);
     break;
+  }
   case SQLCOM_RELEASE_SAVEPOINT:
     if (trans_release_savepoint(thd, lex->ident))
       goto error;
@@ -4561,12 +4593,22 @@ create_sp_error:
     if (trans_xa_commit(thd))
       goto error;
     thd->mdl_context.release_transactional_locks();
+    /*
+      We've just done a commit, reset transaction
+      isolation level to the session default.
+    */
+    thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
     my_ok(thd);
     break;
   case SQLCOM_XA_ROLLBACK:
     if (trans_xa_rollback(thd))
       goto error;
     thd->mdl_context.release_transactional_locks();
+    /*
+      We've just done a rollback, reset transaction
+      isolation level to the session default.
+    */
+    thd->tx_isolation= (enum_tx_isolation) thd->variables.tx_isolation;
     my_ok(thd);
     break;
   case SQLCOM_XA_RECOVER: