Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the application of non-perceptual design in MySQL, which is very detailed and has a certain reference value. Interested friends must read it!

Session_track_transaction_info parameter

Parameter introduction

In MySQL5.7, you can track the status of a transaction by setting the session_track_transaction_info variable.

This parameter has two levels, global and session, and can be modified dynamically.

This parameter can be set to a value of 0 (default OFF), 1 and 2

/ * * Transaction tracking level*/enum enum_session_track_transaction_info {TX_TRACK_NONE = 0, / < do not send tracker items on transaction info TX_TRACK_STATE = 1, / / < track transaction status TX_TRACK_CHISTICS = 2 / < track status and characteristics}

The value allowed to be set for this parameter is 0 # 1 # 2.

When set to 0, show variables like'% session_track_transaction_info%' is displayed as OFF, indicating that transaction status tracking is not enabled

When set to 1, show variables like'% session_track_transaction_info%' is displayed as STATE, which means that the transaction status is tracked

When set to 2, show variables like'% session_track_transaction_info%' is displayed as CHARACTERISTICS, which means tracking transaction status and statements

Parameter setting influence

When the session_track_transaction_info parameter is turned on, the transaction status record cannot be directly queried in the database.

According to [WL#4797], MySQL records the information tracked by the transaction status to the OK packet returned by each Query request.

You can view transaction status information by grabbing packets.

Native MySQL OK packet format

Packet format definition for OK Packet

Type name description int header uses 0x00 or 0xFE to indicate that the packet is a number of rows affected by OK Packetint, the last inserted id, the last inserted idint status identification, if CLIENT_PROTOCOL_41 is defined, there will be this part of int warning quantity warning number warning number, if CLIENT_PROTOCOL_41 is defined, there will be this part of int status identification if CLIENT_TRANSACTIONS is defined, there will be this part of string information human readable status information If CLIENT_SESSION_TRACK is defined, there will be this part of string session state session state information. If SERVER_SESSION_STATE_CHANGED is defined, there will be this part of string information human readable information.

Where lenenc in int and string represents LengthEcode.

The part of the MySQL-5.7.19 code that encapsulates OK packet is in the net_send_ok () function in the protocol_classic.cc file.

Additional information about session_track_transaction_info

Session_track_transaction_info uses eight character bits to represent the information of the transaction, and the eight character information is stored in the return packet of the COM_QUERY request statement (when the client executes a statement, it is sent to the server as a COM_QUERY request encapsulated in the MySQL protocol, and the server returns the result in the packet after parsing.)

Location representation information specific meaning Place 1TransactionT explicitly starts a transaction

I implicitly open a transaction (@ autocommit=0)

_ No active transaction Place 2unsafe readr the table of the non-transactional storage engine is read in the current transaction

_ tables of non-transactional storage engine are not read in current transaction Place 3transaction readR tables of transactional storage engine are read in current transaction

_ tables of transactional storage engine not read in current transaction Place 4unsafe wirtew tables of non-transactional storage engine are written in current transaction

_ tables not written to the non-transactional storage engine in the current transaction Place 5transaction writeW tables written to the transactional storage engine in the current transaction

_ tables that are not written to the transactional storage engine in the current transaction Place 6unsafe statements use unsafe statements in the current transaction, similar to UUID ()

_ sent a result set to the client without using a similar unsafe statement Place 7result-setS

_ No result set Place 8LOCKed TABLESL table is explicitly locked through the LOCK TABLES statement

_ there is no lock table in the current transaction

Analysis of OK packet format when session_track_transaction_info = 0

Session_track_transaction_info=0 means that transaction information is not recorded, and there is no transaction status tracking information in all packets returned on the server side.

# # session_track_transaction_info = 0 client executes begin Encapsulated packet 06 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY62 65 67 696e # beginserver packet returned: response 07 0000 # playload_length 01 # sequence_id00 # header 0x00 indicates that it is an OK packet 00 # the last inserted id03000000 client performs insert into T1 values (55) encapsulated packet 1a 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY696e7365727420696e746f2074312076616c75657328353529 # insert into T1 values (55) packet returned by server: response07 0000 # playload_length01 # sequence_id00010003000000 client executes commit Encapsulated packet 07 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY636f6d6d6974 # database package returned by commitserver: response07 0000 # playload_length01 # sequence_id00000002000000

Analysis of OK packet format for session_track_transaction_info = 1

# # session_track_transaction_info = 1 client executes begin Encapsulated packet 06 # playload_length00 # sequence_id03 # command_type COM_QUERY626567696e # beginserver: response14 0000 # playload_length01 # sequence_id00 # header 0x00 indicates the number of rows affected by an OK packet 00 # last inserted id03400000000b05090854 5f 5f 5f # transaction status information id03400000000b05090854 # Place 1: 54 / / explicitly open a transaction # Place 2: 5f / / Table # Place 3: 5F / / Table of non-transactional storage engine not read in current transaction # Place 4: 5F / / Table of non-transactional storage engine not written in current transaction # Place 5: 5f / / Table of transactional storage engine not written in current transaction # Place 6: 5F / / No unsafe statements are used in current transaction # Place 7 : 5f / No result set # Place 8: 5F / / No lock table client executes insert into T1 values encapsulated packet 1b 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY696e7365727420696e746f2074312076616c7565732831313129 # insert into T1 values packet returned by server: response14 0000 # playload_length01 # sequence_id00010003400000000b050908 54 5f 5f 57 5f 5f # transaction status information T___W___# Place 1: 54 / explicit open A transaction # Place 2: 5f / / Table of the non-transactional storage engine is not read in the current transaction # Place 3: 5F / / the table of the non-transactional storage engine is not read in the current transaction # Place 4: 5F / / the table of the non-transactional storage engine is not written to the current transaction # Place 5: 57 / / the table of the transactional storage engine is written to the current transaction # Place 6: 5F / / not enabled in the current transaction Execute commit with the unsafe statement # Place 7: 5f / / No result set # Place 8: 5f / / unlocked table client Encapsulated packet 07 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY636f6d6d6974 # commitserver return packet: response1400000100000002400000000b0509085f 5f 5f 5f # transaction status Information _ # Place 1: 5F / / No active transaction # Place 2: 5F / / Table # Place 3: 5F / / No transactional storage reference is read in the current transaction Table # Place 4: 5F / / Table of non-transactional storage engine not written in current transaction # Place 5: 5F / / Table of transactional storage engine not written in current transaction # Place 6: 5f / / No unsafe statements in current transaction # Place 7: 5f / / No result set # Place 8: 5f / / No locking table

Analysis of OK packet format when session_track_transaction_info = 2

When the session_track_transaction_info parameter is set to 2, more detailed transaction status information is displayed.

Client executes begin Encapsulated packet 06 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY626567696e # packet returned by beginserver: response29 0000 # playload_length01 # sequence_id00000003400000002005090854 5f 5f 5f # transaction status Information T_0413125354415254205452414e53414354494f4e3b # START TRANSACTION # Place 1: 54 / / explicitly open a transaction # Place 2: 5F / / the table of the non-transactional storage engine is not read in the current transaction # Place 3: 5f / / the table of the transactional storage engine is not read in the current transaction # Place 4: 5f / / the table of the non-transactional storage engine is not written to the current transaction # Place 5: 5F / the table of the transactional storage engine is not written to the current transaction # Place 6: 5F / / No unsafe statement is used in the current transaction # Place 7: 5f / / No result set # Place 8: 5f / / the client executes the packet encapsulated by insert into T1 values 1b 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY696e7365727420696e746f2074312076616c7565732832323229 # insert into T1 values returned by the server side: response14 0000 # playload_length01 # sequence_id00010003400000000b05090854 5f 5f 57 5f 5f 5f # transaction status Information T___W___# Place 1: 5F / / No active transaction # Place 2: 5F / / Table of the non-transactional storage engine is not read in the current transaction # Place 3: 5f / / the table of the transactional storage engine is not read in the current transaction # Place 4: 5F / / Table of the non-transactional storage engine is not written in the current transaction # Place 5: 5f / / the transactionality is not written in the current transaction Storage engine table # Place 6: 5F / / No unsafe statements are used in the current transaction # Place 7: 5f / / No result set # Place 8: 5f / / No table lock client executes commit Encapsulated packet 07 0000 # playload_length00 # sequence_id03 # command_type COM_QUERY636f6d6d6974 # commitserver: response17 0000 # playload_length01 # sequence_id00000002400000000e050908 5f 5f 5f # transaction status Information _ 04010 transaction Place 1: 5F / / No active transaction # Place 2: 5F / / Table # Place 3: 5F / / current of the non-transactional storage engine is not read in the current transaction Table of transactional storage engine not read in transaction # Place 4: 5F / / Table of non-transactional storage engine not written in current transaction # Place 5: 5f / / Table of transactional storage engine not written in current transaction # Place 6: 5f / / No unsafe statements in current transaction # Place 7: 5F / / No result set # Place 8: 5f / / No locking table is applied in MySQL Sample Analysis of non-perceptual Design "all the content of this article Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.