Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

MySQL Server implements several session state trackers. Clients can enable these trackers to receive notifications of session state changes.

1 Purpose of Session State Tracker

The session state tracker has the following uses:

• To facilitate session migration.
• Facilitates transaction switching.

The tracker mechanism provides a way for MySQL Connectors and client applications to determine whether any session context is available to allow 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 that needs to be taken into account when deciding whether the switch can be made.)

The tracker mechanism allows applications to know when a transaction can be moved from one session to another. Transaction state tracking makes this possible, which is useful for applications that want to move transactions from a busy server to a less loaded server. For example, a load-balancing connector that manages a pool of client connections can move transactions between available sessions in the pool.

However, session switching cannot be done at arbitrary times. If a session is in the middle of a transaction that has completed reads or writes, switching to a different session means that the transaction on the original session is rolled back. Session switching is possible only when no reads or writes have been performed in the transaction.

Examples of where a transaction might reasonably switch:

•  START TRANSACTIONImmediately after
• COMMIT AND CHAINafter

In addition to understanding transaction state, it is also possible to understand transaction characteristics so that the same characteristics are used when the transaction moves to a different session. The following characteristics are relevant in this regard:

READ ONLY
READ WRITE
ISOLATION LEVEL
WITH CONSISTENT SNAPSHOT

2 Available session state trackers

To support session tracking activities, notifications can be provided for the following types of client session state information:

(1) Changes to these attributes of the client session state:

• The default schema (database).
• Session-specific value of a system variable.
• User-defined variables.
• Temporary table.
• Prepared remarks.

The session_track_state_change system variable controls this tracker.

(2) Change to the default schema name. The session_track_schema system variable controls this tracker.

(3) Change the session value of a system variable. The session_track_system_variables system variable controls this tracker. The SENSITIVE_VARIABLES_OBSERVER privilege is required to track changes to sensitive system variable values.

(4) Available GTIDs. The session_track_gtids system variable controls this tracker.

(5) Information about transaction status and characteristics. The session_track_transaction_info system variable controls this tracker.

These system variables allow control over which change notifications occur, but do not provide a way to access the notification information. Notification occurs in the MySQL client/server protocol, which includes tracker information in the OK packets in order to detect changes in session state.

3 C API session state tracker support

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:

• mysql_session_track_get_first() gets the first portion of state change information received from the server.
• mysql_session_track_get_next() gets any remaining state change information received from the server. After a successful call to mysql_session_track_get_first(), call this function repeatedly as long as it returns success.

4 Test suite session state tracker support

The mysqltest program has the disable_session_track_info and enable_sessionutrack_ininfo commands that control whether session tracker notifications occur. You can use these commands to view the notifications produced by SQL statements from the command line. Suppose a file testscript contains the following mysqltest script:

DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (i INT, f FLOAT);
--enable_session_track_info
SET @@SESSION.session_track_schema=ON;
SET @@SESSION.session_track_system_variables='*';
SET @@SESSION.session_track_state_change=ON;
USE information_schema;
SET NAMES 'utf8mb4';
SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ WRITE;
START TRANSACTION;
SELECT 1;
INSERT INTO test.t1 () VALUES();
INSERT INTO test.t1 () VALUES(1, RAND());
COMMIT;

Run the script as follows to see the information provided by the enabled tracers.

$> mysqltest < testscript
DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (i INT, f FLOAT);
SET @@SESSION.session_track_schema=ON;
SET @@SESSION.session_track_system_variables='*';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_system_variables
-- *
 
SET @@SESSION.session_track_state_change=ON;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_state_change
-- ON
 
USE information_schema;
-- Tracker : SESSION_TRACK_SCHEMA
-- information_schema
 
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1
 
SET NAMES 'utf8mb4';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- character_set_client
-- utf8mb4
-- character_set_connection
-- utf8mb4
-- character_set_results
-- utf8mb4
 
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1
 
SET @@SESSION.session_track_transaction_info='CHARACTERISTICS';
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- session_track_transaction_info
-- CHARACTERISTICS
 
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1
 
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--
 
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
SET TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ WRITE;
 
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ WRITE;
 
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______
 
SELECT 1;
1
1
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_____S_
 
INSERT INTO test.t1 () VALUES();
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W_S_
 
INSERT INTO test.t1 () VALUES(1, RAND());
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___WsS_
 
COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
--
 
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________
 
ok

Before the START TRANSACTION statement, execute two SET TRANSACTION statements to set the isolation level and access mode characteristics for the next transaction. The SESSION_TRACK_TRANSACTION_CHARACTERISTICS value indicates the next transaction values ​​that have been set.

After a COMMIT statement that ends a transaction, the SESSION_TRACK_transaction_CHARACTERISTICS value is reported as NULL. This indicates that the next transaction characteristics that were set before the transaction began have been reset, and the session defaults are applied. To track changes to these session defaults, track the session values ​​of the transaction_inisolation and transaction_read_only system variables.

To view information about GTIDs, enable the SESSION_TRACK_GTIDs tracker using the SESSION_TRACK_GTIDs system variable.