Innodb of Innodb engine_ lock_ wait_ Timeout parameter description

explain

First, the Innodb engine will lock the corresponding row when executing the update statement and will not release it until the transaction is committed or rolled back. In the process of holding a row lock, if other transactions b also want to modify the data of the locked row, they must wait for the lock to be released. If the lock is not released all the time, transaction b cannot wait all the time. The maximum waiting time is determined by innodb_lock_wait_timeout, in seconds. If the event waiting for lock exceeds innodb_lock_wait_timeout, an exception is thrown and the transaction is rolled back. The exception information is:

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

verification

The verification method is to start a link that does not automatically commit transactions, and then disconnect the network between execute and commit for 10 seconds. In this way, the lock of the corresponding row is held at the Innodb engine layer, but it will not be released temporarily because it is not committed. Then execute the same program to modify the same line of code, and the above exception will appear.

Execute the following code, and then disconnect the network from sleep.

public void testCommitOffline() throws SQLException {
        Connection connection = null;
        Statement statement = null;

        try {
            Driver driver = new com.mysql.jdbc.Driver();
            Properties props = new Properties();
            props.setProperty("user", USER);
            props.setProperty("password", PASS);

            connection = driver.connect(DB_URL, props);
            connection.setAutoCommit(false);

            statement = connection.createStatement();
            try {
                logger.info("------------- before updating but doesn't commit.");
                statement.executeUpdate(SQL_UPDATE_PLAYER_SEX);
                logger.info("------------- executed updateSql but doesn't commit.");
            } catch (CommunicationsException ex) {
                ex.printStackTrace();
            }

            Thread.sleep(10000); // During the first execution, disconnect the network
            connection.commit();
            logger.info("----------- commit");
        } catch (Exception e) {
            logger.error("------------- An error occurred when execute update-sql.");
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

View the engine status and innodb parameters

root:~# mysql -e 'show engine innodb status\G'| grep lock
0 lock struct(s), heap size 1136, 0 row lock(s)
0 lock struct(s), heap size 1136, 0 row lock(s)
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

mysql> show variables like 'innodb%lock%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_api_disable_rowlock     | OFF   |
| innodb_autoinc_lock_mode       | 1     |
| innodb_deadlock_detect         | ON    |
| innodb_lock_wait_timeout       | 60    |

You can see that a row of data is locked, InnoDB_ lock_ wait_ The value of timeout is 60s. At the same time, execute the above java code segment again and wait for the execution result

2021-12-04 11:21:59.512|INFO |main|c.p.d.MySqlConnTester| ------------- before updating but doesn't commit.
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2021-12-04 11:23:00.555|ERROR|main|c.p.d.MySqlConnTester| ------------- An error occurred when execute update-sql.

BUILD SUCCESSFUL in 1m 1s
3 actionable tasks: 2 executed, 1 up-to-date
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

Look at the time interval between two logger s (60 seconds) and InnoDB above_ lock_ wait_ The value specified by timeout is consistent.

conclusion

1. innodb_lock_wait_timeout is used to specify the timeout time of row lock waiting
2. OLTP services should not set this value too large, nor should row locks occupy a long time
3. The OLAP business itself can appropriately relax this value
4. In the business scenario with non automatic transaction submission, you should pay attention to whether there is execution statement but no submission.

Derivative problems (unsolved, please advise)

When executing the above java code segment for the first time, we directly disconnected the network from sleep, resulting in 1. The transaction was not committed; 2. The connection is not closed normally. Two situations are found and two problems arise:

1. Although the local network has been disconnected, the session still exists through show processlist in mysql. Use show status like 'threads'_ connected’; Can also prove this. Even if the link is re linked and disconnected normally, the previous sessions are not released in mysql.
2. During the period when the above session is not released, the row lock held by that session will not be released until the session is manually kill ed.

Derived problems

1. Isn't the TCP link established with mysql? Why can't the session in mysql be released all the time? Doesn't mysql have a mechanism to check for disconnection?
2. How should we judge this situation and release its resources in time? Through interactive_timeout?