mysql - 在长时间锁定表期间与 MySQL 的通信链接失败

Question

我遇到了一些奇怪的行为。我有一个 Java 程序，它使用准备好的语句和批量插入将数据永久写入 MySQL 表。如果其他一些进程在同一个表上发出 LOCK TABLES t WRITE，并在不久之后(几分钟后)释放它，Java 程序将按预期继续。但是，当锁定持续时间较长(超过 30 分钟)时，Java 程序会断开连接而不会继续。 2 小时后，它因通信链接故障 而失败。表锁期间挂起的插入语句在释放锁后执行，但随后连接消失。

详情如下:

• 它发生在 MySQL 5.0.51a 和 5.1.66 上
• 我使用的是最新的 JDBC 驱动程序 mysql-connector-5.1.25-bin.jar
• wait_timeout 设置为 28800(8 小时)
• Communications链接失败的堆栈跟踪和Java程序如下所示

有人知道这是怎么回事吗？我需要设置/增加任何超时吗？

---

两小时后抛出的异常:

Exception in thread "main" java.sql.BatchUpdateException: Communications link failure

The last packet successfully received from the server was 7.260.436 milliseconds ago.  The last packet sent successfully to the server was 7.210.431 milliseconds ago.
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1836)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1456)
    at com.mysql.jdbc.CallableStatement.executeBatch(CallableStatement.java:2499)
    at main.LockTest.main(LockTest.java:26)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 7.260.436 milliseconds ago.  The last packet sent successfully to the server was 7.210.431 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3670)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4110)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1792)
    ... 3 more
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:129)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3570)
    ... 13 more

完整的 Java 程序:

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class LockTest {

    private static final String CONNECTION_PATTERN = "jdbc:mysql://%s/?user=%s&password=%s"
            + "&autoReconnect=true&rewriteBatchedStatements=true&characterEncoding=utf8";
    private static final String QUERY = "INSERT INTO test.lock_test (random_text) VALUES (?)";

    public static void main(String[] args) throws SQLException, InterruptedException {
        Connection con = DriverManager.getConnection(String.format(CONNECTION_PATTERN, "host", "user", "pw"));
        PreparedStatement ps = con.prepareCall(QUERY);
        int i = 0;
        while (true) {
            i++;
            ps.setString(1, Long.toString(System.currentTimeMillis()));
            ps.addBatch();
            if (i % 10 == 0) {
                ps.executeBatch();
                System.out.println(new Date() + ": inserting 10 rows");
            }
            Thread.sleep(5000);
        }
    }
}

Answer 1

我终于知道了真正的原因。 MySQL 的 wait_timeout 与问题无关——只要语句正在运行(无论多长时间)，数据库 session 就不会处于 SLEEP 状态，因此 session 永远不会被 MySQL 关闭。

原因好像是Linux操作系统(Debian 6或7)，闲置2小时后关闭了tcp连接(具体机制我不清楚，也许有人可以详细说明一下？)。

为了避免上述超时，需要发送更频繁的 tcp keep alive 数据包。为此，必须减少 /proc/sys/net/ipv4/tcp_keepalive_time(我将它从 7,200 减少到 60)。 cat 60 >/proc/sys/net/ipv4/tcp_keepalive_time 临时执行此操作；要在系统重启后保留它，必须在 /etc/sysctl.conf 中设置 net.ipv4.tcp_keepalive_time=60。