Why do select @@session.tx_read_only appear in DB in large quantities?

Find the problem

When retrieving the top SQL statements, I found that the DB executed a large number select @@session.tx_read_only statements. This statement appeared before almost every DML statement. However, 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 function of this SQL is to determine whether the transaction is a read-only transaction. MySQL itself will optimize read-only transactions, which only appears after MySQL version 5.6.5. http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tx_read_only

Locate the MySQL driver package

ConnectionImpl.java:

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

By default, our connection string information does not contain the setting of the useLocalSessionState parameter, and this value defaults to false.

This value determines whether the driver uses the internal values ​​of autocommit, read_only and transaction isolation (local values ​​on the JDBC side).

If set to false, the scenarios where these three parameters need to be judged need to send statements to the remote request, such as before updating the statement.

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

If set to true, only the local value needs to be taken. This can explain why some instances have many select @@session.tx_read_only statements.

Generally, the driver can ensure that the local value is consistent with the remote server value. When the application calls the setAutoCommit, setTransactionIsolation and setReadOnly interfaces to set parameter values, it will be synchronized with the remote server.

Specifically,

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

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

However, if the user does not set the parameters through the JDBC interface (such as setAutoCommit), but executes句'set autocommit=xxx' , there will be inconsistencies between the local value and the remote value, which may cause the business logic to change after the parameter useLocalSessionState is modified.

Related settings SQL statements:

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

Setting the default value of useLocalSessionState to true may cause changes in the meaning of business logic. The triggering condition is that the user directly sets the auto-commit parameter, isolation level parameter, or read-only transaction parameter through SQL statements.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.