Innodb引擎的innodb_lock_wait_timeout参数说明

说明

首先Innodb引擎在执行update语句时候会对对应的行进行锁定，直到事务提交完毕或回滚才会释放。在持有行锁的过程中如果有其他事务b也想修改锁定行的数据，则要等待锁的释放。如果锁一直不释放，那么事务b不可能一直等待下去，那么这个最大的等待时长就是由innodb_lock_wait_timeout来指定，单位是秒。如果等待锁的事件超过了 innodb_lock_wait_timeout 指定的秒数，则会抛出异常并回滚事务。异常信息为：

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

验证

验证手段为先启动一个不自动提交事务的链接，然后在execute和commit之间sleep10秒，期间把网络断开，这样在Innodb引擎层就持有了对应行的锁，但是因为没有提交，所以暂时不会释放。然后再执行相同的程序去修改同一行代码就会出现上述异常。

执行下面的代码，然后再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); // 第一次执行的时候在这期间断开网络
            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();
            }
        }
    }

此时查看引擎状态和innodb参数

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    |

可以看到有一行数据时被锁了的，innodb_lock_wait_timeout的值是60s，同时然后再次执行上面的java代码段，等待执行结果

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

看两次logger的时间间隔（60秒）和上面innodb_lock_wait_timeout指定的值一致。

结论

1. innodb_lock_wait_timeout是用来指定行锁等待超时时间的
2. OLTP型业务不应该将这个值设置的太大，也不应该出现行锁占用很长的情况发生
3. OLAP型业务本身可以适当将这个值放宽一点
4. 业务场景中有非自动提交事务的场景要留心会不会有执行语句但是没有提交的情况。

衍生问题（未解决，请指教）

在第一遍执行上面的java代码段时，我们再sleep那里直接将网络断开了，导致1，事务没有提交；2，connection没有正常关闭。由此发现两个情况并衍生出两个问题：

1. 虽然本地已经断网了，但是在mysql那里通过show processlist 查看session还是存在的，通过show status like ‘Threads_connected’;也能证明这一点。即便之后重新链接，正常断开，那之前的session在mysql那里都是没有释放的。
2. 在上面的session没有释放的期间，那个session持有的行锁始终是不释放的，直到手动kill掉这个session。

衍生的问题

1. 和mysql建立的不是TCP链接吗，那为什么mysql那里的session一直不能释放呢？难道mysql没有检查断线的机制吗？
2. 那应该如何判断这种情况并及时释放掉它持有的资源呢？通过interactive_timeout吗？