<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="session-state-tracking">
      </a>
      7.1.18 Server Tracking of Client Session State
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045260091472">
  </a>
  <a class="indexterm" name="idm46045260090016">
  </a>
  <p>
   The MySQL server implements several session state trackers. A
      client can enable these trackers to receive notification of
      changes to its session state.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="session-state-tracking.html#session-state-tracking-uses" title="Uses for Session State Trackers">
       Uses for Session State Trackers
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="session-state-tracking.html#session-state-tracking-notifications" title="Available Session State Trackers">
       Available Session State Trackers
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="session-state-tracking.html#session-state-tracking-capi-support" title="C API Session State Tracker Support">
       C API Session State Tracker Support
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="session-state-tracking.html#session-state-tracking-test-suite-support" title="Test Suite Session State Tracker Support">
       Test Suite Session State Tracker Support
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="session-state-tracking-uses">
       </a>
       Uses for Session State Trackers
      </h4>
     </div>
    </div>
   </div>
   <p>
    Session state trackers have uses such as these:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       To facilitate session migration.
      </p>
     </li>
     <li class="listitem">
      <p>
       To facilitate transaction switching.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The tracker mechanism provides a means for MySQL connectors and
        client applications to determine whether any session context is
        available to permit session migration from one server to
        another. (To change sessions in a load-balanced environment, it
        is necessary to detect whether there is session state to take
        into consideration when deciding whether a switch can be made.)
   </p>
   <p>
    The tracker mechanism permits applications to know when
        transactions can be moved from one session to another.
        Transaction state tracking enables this, which is useful for
        applications that may wish to move transactions from a busy
        server to one that is less loaded. For example, a load-balancing
        connector managing a client connection pool could move
        transactions between available sessions in the pool.
   </p>
   <p>
    However, session switching cannot be done at arbitrary times. If
        a session is in the middle of a transaction for which reads or
        writes have been done, switching to a different session implies
        a transaction rollback on the original session. A session switch
        must be done only when a transaction does not yet have any reads
        or writes performed within it.
   </p>
   <p>
    Examples of when transactions might reasonably be switched:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Immediately after
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         START
            TRANSACTION
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       After
       <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
        <code class="literal">
         COMMIT AND
            CHAIN
        </code>
       </a>
      </p>
     </li>
    </ul>
   </div>
   <p>
    In addition to knowing transaction state, it is useful to know
        transaction characteristics, so as to use the same
        characteristics if the transaction is moved to a different
        session. The following characteristics are relevant for this
        purpose:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa12650051"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-simple">READ ONLY
READ WRITE
ISOLATION LEVEL
WITH CONSISTENT SNAPSHOT</code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="session-state-tracking-notifications">
       </a>
       Available Session State Trackers
      </h4>
     </div>
    </div>
   </div>
   <p>
    To support the session-tracking activities, notification is
        available for these types of client session state information:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Changes to these attributes of client session state:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          The default schema (database).
         </p>
        </li>
        <li class="listitem">
         <p>
          Session-specific values for system variables.
         </p>
        </li>
        <li class="listitem">
         <p>
          User-defined variables.
         </p>
        </li>
        <li class="listitem">
         <p>
          Temporary tables.
         </p>
        </li>
        <li class="listitem">
         <p>
          Prepared statements.
         </p>
        </li>
       </ul>
      </div>
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_session_track_state_change">
        <code class="literal">
         session_track_state_change
        </code>
       </a>
       system variable controls this tracker.
      </p>
      <a class="indexterm" name="idm46045260061072">
      </a>
      <a class="indexterm" name="idm46045260059968">
      </a>
     </li>
     <li class="listitem">
      <p>
       Changes to the default schema name. The
       <a class="link" href="server-system-variables.html#sysvar_session_track_schema">
        <code class="literal">
         session_track_schema
        </code>
       </a>
       system
            variable controls this tracker.
      </p>
      <a class="indexterm" name="idm46045260056256">
      </a>
      <a class="indexterm" name="idm46045260055216">
      </a>
     </li>
     <li class="listitem">
      <p>
       Changes to the session values of system variables. The
       <a class="link" href="server-system-variables.html#sysvar_session_track_system_variables">
        <code class="literal">
         session_track_system_variables
        </code>
       </a>
       system variable controls this tracker. The
       <a class="link" href="privileges-provided.html#priv_sensitive-variables-observer">
        <code class="literal">
         SENSITIVE_VARIABLES_OBSERVER
        </code>
       </a>
       privilege is required to track changes to the values of
            sensitive system variables.
      </p>
      <a class="indexterm" name="idm46045260050160">
      </a>
      <a class="indexterm" name="idm46045260049056">
      </a>
     </li>
     <li class="listitem">
      <p>
       Available GTIDs. The
       <a class="link" href="server-system-variables.html#sysvar_session_track_gtids">
        <code class="literal">
         session_track_gtids
        </code>
       </a>
       system
            variable controls this tracker.
      </p>
      <a class="indexterm" name="idm46045260045360">
      </a>
      <a class="indexterm" name="idm46045260044320">
      </a>
     </li>
     <li class="listitem">
      <p>
       Information about transaction state and characteristics. The
       <a class="link" href="server-system-variables.html#sysvar_session_track_transaction_info">
        <code class="literal">
         session_track_transaction_info
        </code>
       </a>
       system variable controls this tracker.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For descriptions of the tracker-related system variables, see
    <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
     Section 7.1.8, “Server System Variables”
    </a>
    . Those system
        variables permit control over which change notifications occur,
        but do not provide a way to access notification information.
        Notification occurs in the MySQL client/server protocol, which
        includes tracker information in OK packets so that session state
        changes can be detected.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="session-state-tracking-capi-support">
       </a>
       C API Session State Tracker Support
      </h4>
     </div>
    </div>
   </div>
   <p>
    To enable client applications to extract state-change
        information from OK packets returned by the server, the MySQL C
        API provides a pair of functions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-first.html" target="_top">
        <code class="literal">
         mysql_session_track_get_first()
        </code>
       </a>
       fetches the first part of the state-change information
            received from the server. See
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-first.html" target="_top">
        mysql_session_track_get_first()
       </a>
       .
      </p>
      <a class="indexterm" name="idm46045260034112">
      </a>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-next.html" target="_top">
        <code class="literal">
         mysql_session_track_get_next()
        </code>
       </a>
       fetches any remaining state-change information received from
            the server. Following a successful call to
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-first.html" target="_top">
        <code class="literal">
         mysql_session_track_get_first()
        </code>
       </a>
       ,
            call this function repeatedly as long as it returns success.
            See
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-next.html" target="_top">
        mysql_session_track_get_next()
       </a>
       .
      </p>
      <a class="indexterm" name="idm46045260028832">
      </a>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="session-state-tracking-test-suite-support">
       </a>
       Test Suite Session State Tracker Support
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045260026144">
   </a>
   <a class="indexterm" name="idm46045260025040">
   </a>
   <a class="indexterm" name="idm46045260023536">
   </a>
   <a class="indexterm" name="idm46045260022448">
   </a>
   <a class="indexterm" name="idm46045260020960">
   </a>
   <a class="indexterm" name="idm46045260019856">
   </a>
   <a class="indexterm" name="idm46045260018352">
   </a>
   <a class="indexterm" name="idm46045260017264">
   </a>
   <a class="indexterm" name="idm46045260015776">
   </a>
   <a class="indexterm" name="idm46045260014656">
   </a>
   <a class="indexterm" name="idm46045260013136">
   </a>
   <a class="indexterm" name="idm46045260012032">
   </a>
   <p>
    The
    <span class="command">
     <strong>
      mysqltest
     </strong>
    </span>
    program has
    <code class="literal">
     disable_session_track_info
    </code>
    and
    <code class="literal">
     enable_session_track_info
    </code>
    commands that
        control whether session tracker notifications occur. You can use
        these commands to see from the command line what notifications
        SQL statements produce. Suppose that a file
    <code class="filename">
     testscript
    </code>
    contains the following
    <span class="command">
     <strong>
      mysqltest
     </strong>
    </span>
    script:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34325262"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> test<span class="token punctuation">.</span>t1<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> f <span class="token datatype">FLOAT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">--enable_session_track_info</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.session_track_schema</span><span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.session_track_system_variables</span><span class="token operator">=</span><span class="token string">'*'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.session_track_state_change</span><span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span>
<span class="token keyword">USE</span> information_schema<span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">NAMES</span> <span class="token string">'utf8mb4'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.session_track_transaction_info</span><span class="token operator">=</span><span class="token string">'CHARACTERISTICS'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">TRANSACTION</span> <span class="token keyword">ISOLATION</span> <span class="token keyword">LEVEL</span> <span class="token keyword">SERIALIZABLE</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">TRANSACTION</span> <span class="token keyword">READ</span> <span class="token keyword">WRITE</span><span class="token punctuation">;</span>
<span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Run the script as follows to see the information provided by the
        enabled trackers. For a description of the
    <code class="literal">
     Tracker:
    </code>
    information displayed by
    <span class="command">
     <strong>
      mysqltest
     </strong>
    </span>
    for the various trackers, see
    <a class="ulink" href="/doc/c-api/8.4/en/mysql-session-track-get-first.html" target="_top">
     mysql_session_track_get_first()
    </a>
    .
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa45506681"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqltest</span> &lt; testscript
DROP TABLE IF EXISTS test<span class="token punctuation">.</span>t1<span class="token punctuation">;</span>
CREATE TABLE test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span>i INT<span class="token punctuation">,</span> f FLOAT<span class="token punctuation">)</span><span class="token punctuation">;</span>
SET @@SESSION<span class="token punctuation">.</span>session_track_schema<span class="token attr-value"><span class="token punctuation">=</span>ON;</span>
SET @@SESSION<span class="token punctuation">.</span>session_track_system_variables=<span class="token atrule">'*'</span><span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_SYSTEM_VARIABLES
<span class="token property">--</span> session_track_system_variables
<span class="token property">--</span> *

SET @@SESSION<span class="token punctuation">.</span>session_track_state_change<span class="token attr-value"><span class="token punctuation">=</span>ON;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_SYSTEM_VARIABLES
<span class="token property">--</span> session_track_state_change
<span class="token property">--</span> ON

USE information_schema<span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_SCHEMA
<span class="token property">--</span> information_schema

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_STATE_CHANGE
<span class="token property">--</span> 1

SET NAMES <span class="token atrule">'utf8mb4'</span><span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_SYSTEM_VARIABLES
<span class="token property">--</span> character_set_client
<span class="token property">--</span> utf8mb4
<span class="token property">--</span> character_set_connection
<span class="token property">--</span> utf8mb4
<span class="token property">--</span> character_set_results
<span class="token property">--</span> utf8mb4

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_STATE_CHANGE
<span class="token property">--</span> 1

SET @@SESSION<span class="token punctuation">.</span>session_track_transaction_info=<span class="token atrule">'CHARACTERISTICS'</span><span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_SYSTEM_VARIABLES
<span class="token property">--</span> session_track_transaction_info
<span class="token property">--</span> CHARACTERISTICS

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_STATE_CHANGE
<span class="token property">--</span> 1

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_CHARACTERISTICS
<span class="token property">--</span>

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> ________

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_CHARACTERISTICS
<span class="token property">--</span> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<span class="token punctuation">;</span>

SET TRANSACTION READ WRITE<span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_CHARACTERISTICS
<span class="token property">--</span> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<span class="token punctuation">;</span> SET TRANSACTION READ WRITE<span class="token punctuation">;</span>

START TRANSACTION<span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_CHARACTERISTICS
<span class="token property">--</span> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<span class="token punctuation">;</span> START TRANSACTION READ WRITE<span class="token punctuation">;</span>

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> T_______

SELECT 1<span class="token punctuation">;</span>
1
1
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> T_____S_

INSERT INTO test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> VALUES<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> T___W_S_

INSERT INTO test<span class="token punctuation">.</span>t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> VALUES<span class="token punctuation">(</span>1<span class="token punctuation">,</span> RAND<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> T___WsS_

COMMIT<span class="token punctuation">;</span>
<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_CHARACTERISTICS
<span class="token property">--</span>

<span class="token property">--</span> Tracker <span class="token punctuation">:</span> SESSION_TRACK_TRANSACTION_STATE
<span class="token property">--</span> ________

ok</code></pre>
   </div>
   <p>
    Preceding the
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      START
        TRANSACTION
     </code>
    </a>
    statement, two
    <a class="link" href="set-transaction.html" title="15.3.7 SET TRANSACTION Statement">
     <code class="literal">
      SET
        TRANSACTION
     </code>
    </a>
    statements execute that set the isolation
        level and access mode characteristics for the next transaction.
        The
    <code class="literal">
     SESSION_TRACK_TRANSACTION_CHARACTERISTICS
    </code>
    value indicates those next-transaction values that have been
        set.
   </p>
   <p>
    Following the
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    statement
        that ends the transaction, the
    <code class="literal">
     SESSION_TRACK_TRANSACTION_CHARACTERISTICS
    </code>
    value is reported as empty. This indicates that the
        next-transaction characteristics that were set preceding the
        start of the transaction have been reset, and that the session
        defaults apply. To track changes to those session defaults,
        track the session values of the
    <a class="link" href="server-system-variables.html#sysvar_transaction_isolation">
     <code class="literal">
      transaction_isolation
     </code>
    </a>
    and
    <a class="link" href="server-system-variables.html#sysvar_transaction_read_only">
     <code class="literal">
      transaction_read_only
     </code>
    </a>
    system
        variables.
   </p>
   <p>
    To see information about GTIDs, enable the
    <code class="literal">
     SESSION_TRACK_GTIDS
    </code>
    tracker using the
    <a class="link" href="server-system-variables.html#sysvar_session_track_gtids">
     <code class="literal">
      session_track_gtids
     </code>
    </a>
    system
        system variable.
   </p>
  </div>
 </div>
 <br/>
</div>
