MySQL Bugs: #81724: Session Tracker Does Not Report Isolation Level

Bug #81724	Session Tracker Does Not Report Isolation Level
Submitted:	4 Jun 2016 11:01	Modified:	26 Oct 2018 1:48
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S3 (Non-critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/worklog/task/?id=6631

No isolation level information is included in the response packet. According to this worklog, it should be included.

How to repeat:
mysql> set session_track_transaction_info='CHARACTERISTICS';

mysql> set tx_isolation='serializable';

Then, see the client's packet.

Suggested fix:
Cite from 5.7.12 community server source code:

bool Sys_var_tx_isolation::session_update(THD *thd, set_var *var)
{
  if (var->type == OPT_SESSION && Sys_var_enum::session_update(thd, var))
    return TRUE;
  if (var->type == OPT_DEFAULT || !(thd->in_active_multi_stmt_transaction() ||
                                    thd->in_sub_stmt))
  {
    Transaction_state_tracker *tst= NULL;

    if (thd->variables.session_track_transaction_info > TX_TRACK_NONE)
      tst= (Transaction_state_tracker *)
             thd->session_tracker.get_tracker(TRANSACTION_INFO_TRACKER);

    /*
      Update the isolation level of the next transaction.
      I.e. if one did:
      COMMIT;
      SET SESSION ISOLATION LEVEL ...
      BEGIN; <-- this transaction has the new isolation
      Note, that in case of:
      COMMIT;
      SET TRANSACTION ISOLATION LEVEL ...
      SET SESSION ISOLATION LEVEL ...
      BEGIN; <-- the session isolation level is used, not the
      result of SET TRANSACTION statement.

      When we are in a trigger/function the transaction is already
      started. Adhering to above behavior, the SET TRANSACTION would
      fail when run from within trigger/function. And SET SESSION
      TRANSACTION would always succeed making the characteristics
      effective for the next transaction that starts.
     */
    thd->tx_isolation= (enum_tx_isolation) var->save_result.ulonglong_value;

    if (var->type == OPT_DEFAULT)  <=================== this should be OPT_DEFAULT or OPT_SESSION
    {
      enum enum_tx_isol_level l;
      switch (thd->tx_isolation) {
      case ISO_READ_UNCOMMITTED:
        l=  TX_ISOL_UNCOMMITTED;
        break;
      case ISO_READ_COMMITTED:
        l=  TX_ISOL_COMMITTED;
        break;
      case ISO_REPEATABLE_READ:
        l= TX_ISOL_REPEATABLE;
        break;
      case ISO_SERIALIZABLE:
        l= TX_ISOL_SERIALIZABLE;
        break;
      default:
        DBUG_ASSERT(0);
        return TRUE;
      }
      if (tst)
        tst->set_isol_level(thd, l);
    }
    else if (tst)
    {
      tst->set_isol_level(thd, TX_ISOL_INHERIT); <========= why this is required?
    }
  }
  return FALSE;
}

Hello Mikiya-San,

Thank you for the report and feedback!

Thanks,
Umesh

Posted by developer:
 
It is important to keep in mind that the characteristics tracker is
ONLY concerned with the one-shots (SET TRANSACTION tx_isolation ...,
SET TRANSACTION tx_read_only).  To get the global or session
variables, track those explicitly (as recommended in WL#6631).

"SET tx_isolation=..." sets the session variable, not the one-shot.
The one-shot (for the next transaction) is not set,
so the isolation level of the next transaction will 
be whatever the session level is.

If we'd set the one-shot to something else first 
(i.e. made it different from the default, i.e. 
the session value), and THEN changed the session
value (which resets the one-shot), we'd see a tracker 
item for CHARACTERISTICS.

Example:

# turn on chistics tracking
SET SESSION session_track_transaction_info="CHARACTERISTICS";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# setting tx_isolation. This defaults to the session variable,
# not the one-shot, and the one-shot is not explicitly set
# (i.e. its value is TX_ISOL_INHERIT, inherit value from session).
# Therefore (because of "inherit") we don't see an explicit
# characteristics item (because it's "boring").
# Another way to think of it is that the one-shot was "inherit"
# before we changed the session value, and it is still "inherit"
# afterwards, i.e. the was no change/edge, and therefore nothing
# to report.
# We do however see a change notification for the session variable
# (when tracking transaction characteristics, tx_isolation and
# tx_read_only should also be tracked, as per the specification):

SET tx_isolation='serializable';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- tx_isolation
-- SERIALIZABLE
    
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1
    

# now, we'll set up the one-shot (using "TRANSACTION"), making
# it "read committed" rather than "inherit". We'll get a
# characteristics change notice for it:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# Now, we set the session variable (which will reset
# the one-shot). We'll get a change notice for the variable,
# and another one for the one-shot, as both change:    
SET tx_isolation='read-uncommitted';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- tx_isolation
-- READ-UNCOMMITTED
            
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1 

# note that the chistics don't change to "read-unco",
# they change to "none", which would result in a
# "use session settings" behaviour.     
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--

Posted by developer:
 
clarifying "This defaults to the session variable,
not the one-shot, and the one-shot is not explicitly set"
for

  SET tx_isolation='serializable';

The "scope" here is session, i.e. the above statement is
equivalent to

  SET SESSION tx_isolation='serializable';

To make the scope just the next transaction ("one-shot"),
you have to explicitly mark it so:

  SET TRANSACTION ISOLATION LEVEL serializable;

Since "SET TRANSACTION" was not used, the one-shot is
not explicitly set, which means it's at its default,
"inherit", as in, "use the session setting."

As CHARACTERISTICS reports on one-shots only, and
that setting did not change (it is "inherit" before
we touch the session value, and it is "inherit"
after), we report nothing.

Posted by developer:
 
The characteristics tracker tracks changes to the one-shot characteristics that apply only to the next transaction. It does not track changes to the session variables. Therefore, the client additionally must track the transaction_isolation and transaction_read_only system variables to correctly determine the session defaults that apply when next-transaction characteristic values are empty. (To track these variables, list them in the value of the session_track_system_variables system variable.)

For additional information, see discussion of SESSION_TRACK_TRANSACTION_CHARACTERISTICS
at: https://dev.mysql.com/doc/refman/8.0/en/mysql-session-track-get-first.html

And see the discussion at the end of:
https://dev.mysql.com/doc/refman/8.0/en/session-state-tracking.html
following the example showing the output from the test script.