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

Editor to share with you why there is a large number of DB select @ @ session.tx_read_only, I hope you will learn something after reading this article, let's discuss it together!

Find a problem

In a Top SQL grab, it is found that DB executes a large number of select @ @ session.tx_read_only, and there is such a sql before almost every DML statement. But the application layer does not do any special processing, so what is the purpose of this SQL statement? Who executed it?

Detailed introduction

The main purpose of this sql is to determine whether the transaction is read-only. MySQL itself optimizes read-only transactions, which is not available until after the MySQL5.6.5 version. Http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tx_read_only

Navigate to the driver package of MySQL

ConnectionImpl.java:

You can see that in the if condition, the version of MySQL is judged, and there is also a condition of! getUseLocalSessionState (), which corresponds to the JDBC parameter useLocalSessionState. When this value is false, the sql of select @ @ session.tx_read_only; will be issued.

By default, our connection string information does not include the setting of the useLocalSessionState parameter, which defaults to false.

The purpose of this value is whether the driver uses the internal values of autocommit,read_only and transaction isolation (the local value on the JDBC side).

If set to false, the scenario that determines these three parameters needs to send a statement to the remote request, such as before updating the statement.

You need to issue the statement select @ @ session.tx_read_only to confirm that the session is read-only.

If set to true, you only need to take the local value. This explains why some instances have so many select @ @ session.tx_read_only statements.

In general, the driver ensures that the local value is consistent with the remote server value. When the application calls the three interfaces setAutoCommit, setTransactionIsolation and setReadOnly to set parameter values, it will synchronize with the remote server.

Specifically,

When useLocalSessionState is true, if the value is inconsistent with the local value, it will be sent to remote update.

When useLocalSessionState is false, it is sent to a remote update every time, regardless of whether the set value is consistent with the local value. This explains why some instances have more set autocommit statements.

However, if the user does not set the parameter through the JDBC interface (such as setAutoCommit), but executes the statement 'set autocommit=xxx' setting, then the local value is inconsistent with the remote, which may cause the business logic to change after the parameter useLocalSessionState is modified.

SQL statement for related settings:

Set autocommit=0 / * set session auto-commit mode * / corresponding JDBC interface: setAutoCommit (false) set tx_isolation='read-committed' / * set isolation level of transaction * / corresponding JDBC interface: setTransactionIsolation ('read-committed') set tx_read_only=0; / * set read-only transaction * / corresponding JDBC interface: setReadOnly (false)

Setting the useLocalSessionState default to ture may result in a change in the meaning of the business logic. The trigger condition is that the user sets autocommit parameters, isolation level parameters, or read-only transaction parameters directly through the SQL statement.

After reading this article, I believe you have a certain understanding of "Why there is a large number of DB select @ @ session.tx_read_only". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.