MySQL锁等待与连接池排队排查实战

排查接口性能问题时，我们很容易形成一种惯性：

接口慢，先查慢 SQL；发现 SQL 没走索引，就补索引；数据库连接不够，就把连接池调大。

但如果遇到下面这种情况，该怎么办？

页面访问明显变慢，部分请求甚至超时，可数据库 CPU 并不高，慢查询日志里也没有多少值得关注的 SQL。把 SQL 单独拿出来执行，又很快返回。

这时，真正需要回答的不是“哪条 SQL 最慢”，而是：

一个请求从进入应用到返回结果，究竟把时间花在了哪里？

慢查询日志记录的是符合特定条件的数据库语句，不是整个 HTTP 请求的时间线。它还受到 long_query_time、min_examined_row_limit 等条件影响，不能独自证明整个数据库访问链路没有问题。(MySQL开发者专区)

本文用一个 Java、HikariCP 和 MySQL 的最小实验，拆开三类容易混淆的问题：连接池排队、连接持有时间过长，以及事务锁等待。

文中的实验用于本地复现，时间估算是机制推导，不代表生产实测结果。

一、先把“数据库访问时间”拆开

一次数据库访问，至少有三个不同的时间边界。

这里最容易忽略的是：

连接持有时间，不等于 SQL 执行时间。

应用拿到连接后，即使暂时没有执行 SQL，这条连接也不能被其他请求借用。HikariCP 在连接池达到上限且没有空闲连接时，会让获取连接的调用等待，直到拿到连接或达到 connectionTimeout。(GitHub)

例如，一段业务可能这样执行：

graph LR
    A["收到请求"] --> B["等待连接"]
    B --> C["执行查询"]
    C --> D["等待外部接口"]
    D --> E["归还连接"]
    E --> F["返回结果"]

假设查询很快，但外部接口需要等待 500 毫秒，而且等待期间连接一直没有归还。

那么，数据库可能早已完成查询，连接池却仍然认为这条连接正在使用。

后续请求甚至还没机会把 SQL 发给 MySQL，就已经在应用内部排队。

还有一个统计上的细节：连接持有耗时已经包含 JDBC 调用耗时，不能把两者再次相加。否则，监控图表里的总耗时会被重复计算。

二、搭建一个最小复现实验

实验只做一件事：

让 12 个并发任务共用 2 条数据库连接，每个任务查询一行数据，然后执行一段持续 500 毫秒的模拟工作。

我们比较两种处理方式：

查询内容不变，连接池大小不变，任务数量不变，只调整连接的持有范围。

2.1 项目文件

创建 pool-lab 目录，并按下面的路径放置文件：

示例使用 JDK 21，固定 HikariCP 为 7.0.2、MySQL Connector/J 为 9.4.0。这些是实验依赖，不表示它们是当前最新版本。

2.2 启动 MySQL

compose.yaml：

services:
  mysql:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: local_root_only
      MYSQL_DATABASE: perf_lab
      MYSQL_USER: lab
      MYSQL_PASSWORD: local_lab_only
    ports:
      - "127.0.0.1:3307:3306"
    command:
      - "--slow-query-log=ON"
      - "--log-output=TABLE"
      - "--long-query-time=1"
    volumes:
      - mysql-data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/01-init.sql:ro
    healthcheck:
      test:
        - CMD-SHELL
        - "MYSQL_PWD=$$MYSQL_ROOT_PASSWORD mysql -h 127.0.0.1 -uroot -e 'SELECT 1'"
      interval: 5s
      timeout: 3s
      retries: 30

volumes:
  mysql-data:

这里把数据库端口绑定到本机回环地址，示例密码也只供本地实验使用。

init.sql：

USE perf_lab;

CREATE TABLE comment_demo (
    id BIGINT NOT NULL,
    content VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO comment_demo (id, content)
VALUES (1, 'connection pool demo');

MySQL 官方镜像会在首次初始化空数据目录时执行初始化目录里的脚本。已经存在数据的卷不会因为修改了 init.sql 就重新初始化。(Docker Hub)

在项目根目录执行：

docker compose up -d --wait

mysql:8.4 固定的是版本系列。记录实验结果时，还应执行 SELECT VERSION()，保留实际补丁版本信息。

2.3 Maven 配置

pom.xml：

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="
           http://maven.apache.org/POM/4.0.0
           https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>demo</groupId>
    <artifactId>pool-lab</artifactId>
    <version>1.0.0</version>

    <properties>
        <maven.compiler.release>21</maven.compiler.release>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>7.0.2</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>9.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.17</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.13.0</version>
            </plugin>

            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>3.5.0</version>
                <configuration>
                    <mainClass>demo.PoolLab</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.4 完整实验代码

src/main/java/demo/PoolLab.java：

package demo;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public final class PoolLab {
    private static final int CLIENTS = 12;
    private static final long WORK_MS = 500;

    public static void main(String[] args) throws Exception {
        String mode = args.length == 0 ? "hold" : args[0];

        if (args.length > 1
                || (!mode.equals("hold") && !mode.equals("release"))) {
            throw new IllegalArgumentException(
                    "Usage: PoolLab [hold|release]");
        }

        boolean holdDuringWork = mode.equals("hold");

        HikariConfig config = new HikariConfig();

        // 关闭 TLS 和示例密码仅用于本机隔离实验。
        config.setJdbcUrl("jdbc:mysql://127.0.0.1:3307/perf_lab"
                + "?sslMode=DISABLED&allowPublicKeyRetrieval=true");
        config.setUsername("lab");
        config.setPassword("local_lab_only");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");

        config.setPoolName("pool-lab");
        config.setMaximumPoolSize(2);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(10_000);
        config.setValidationTimeout(1_000);

        config.addDataSourceProperty("connectTimeout", "2000");
        config.addDataSourceProperty("socketTimeout", "5000");

        try (HikariDataSource dataSource =
                     new HikariDataSource(config)) {

            // 同时借出两条连接，排除连接池尚未填充的影响。
            try (Connection first = dataSource.getConnection();
                 Connection second = dataSource.getConnection()) {
                if (!first.isValid(2) || !second.isValid(2)) {
                    throw new SQLException("Database warm-up failed");
                }
            }

            ScheduledExecutorService monitor =
                    Executors.newSingleThreadScheduledExecutor();

            monitor.scheduleAtFixedRate(() -> {
                HikariPoolMXBean pool =
                        dataSource.getHikariPoolMXBean();

                System.out.printf(
                        "active=%d idle=%d waiting=%d%n",
                        pool.getActiveConnections(),
                        pool.getIdleConnections(),
                        pool.getThreadsAwaitingConnection());
            }, 0, 200, TimeUnit.MILLISECONDS);

            try (ExecutorService workers =
                         Executors.newFixedThreadPool(CLIENTS)) {

                CountDownLatch start = new CountDownLatch(1);
                List<Future<Void>> tasks = new ArrayList<>();

                for (int i = 0; i < CLIENTS; i++) {
                    tasks.add(workers.submit(() -> {
                        start.await();
                        runOne(dataSource, holdDuringWork);
                        return null;
                    }));
                }

                start.countDown();

                // 不吞掉工作线程中的异常。
                for (Future<Void> task : tasks) {
                    task.get();
                }
            } finally {
                monitor.shutdownNow();
                monitor.awaitTermination(5, TimeUnit.SECONDS);
            }
        }
    }

    private static void runOne(
            HikariDataSource dataSource,
            boolean holdDuringWork)
            throws SQLException, InterruptedException {

        long begin = System.nanoTime();
        long acquired;
        long jdbcNanos;

        try (Connection connection = dataSource.getConnection()) {
            acquired = System.nanoTime();

            try (PreparedStatement statement =
                         connection.prepareStatement(
                                 "SELECT content "
                                         + "FROM comment_demo "
                                         + "WHERE id = ?")) {

                statement.setLong(1, 1L);
                statement.setQueryTimeout(3);

                long sqlBegin = System.nanoTime();

                try (ResultSet result = statement.executeQuery()) {
                    if (!result.next() || result.getString(1) == null) {
                        throw new SQLException("Missing demo row");
                    }
                }

                jdbcNanos = System.nanoTime() - sqlBegin;
            }

            if (holdDuringWork) {
                Thread.sleep(WORK_MS);
            }
        }

        long released = System.nanoTime();

        if (!holdDuringWork) {
            Thread.sleep(WORK_MS);
        }

        long end = System.nanoTime();

        System.out.printf(Locale.ROOT,
                "%s acquire=%.2fms jdbc=%.2fms "
                        + "hold=%.2fms task=%.2fms%n",
                holdDuringWork ? "hold" : "release",
                millis(acquired - begin),
                millis(jdbcNanos),
                millis(released - acquired),
                millis(end - begin));
    }

    private static double millis(long nanos) {
        return nanos / 1_000_000.0;
    }
}

代码使用有限数量的任务制造竞争，并不是生产压测工具。

这里的 Thread.sleep() 代表一段不需要数据库连接的等待，例如某些外部调用。实验没有真实调用第三方服务，也没有用数据库的 SLEEP() 函数代替应用等待。

这个区别非常重要：我们要模拟的是应用占着连接却没有执行 SQL，而不是让数据库执行一条慢 SQL。

三、真正拖慢请求的，可能是连接归还得太晚

先运行持有连接的模式：

mvn -q compile exec:java -Dexec.args=hold

再运行及时归还连接的模式：

mvn -q compile exec:java -Dexec.args=release

程序会输出四个时间指标：

忽略查询、线程调度和日志输出开销，只看理论模型：

在 hold 模式下，每条连接至少被占用约 500 毫秒。两条连接一次只能承载两个任务，12 个任务需要分批通过，最后一批可能先等待约 2.5 秒，才能借到连接。

在 release 模式下，500 毫秒的等待发生在归还连接之后。数据库连接可以继续服务其他任务，而不是陪着已经查完数据的任务一起等待。

两种模式没有改变任何索引，也没有改变查询语句，区别只在于稀缺资源被占用了多久。

监控输出里的 active、idle 和 waiting 可以辅助观察这个过程，但它们是分别采集的瞬时快照，不应根据某一次采样做严格的数量推断。HikariCP 官方也明确提醒，这些指标可能在短时间内无法完全“对上”。(GitHub-Monitoring-and-Management))

为什么慢查询日志可能没有明显变化？

打开管理员会话：

docker compose exec mysql mysql -uroot -p

输入实验密码 local_root_only，查询：

SELECT
    start_time,
    query_time,
    lock_time,
    sql_text
FROM mysql.slow_log
WHERE db = 'perf_lab'
ORDER BY start_time DESC
LIMIT 20;

本实验把慢查询阈值设为 1 秒。如果那条查询本身没有达到记录条件，应用里的 500 毫秒等待不会自动变成一条慢 SQL；获取连接之前的排队，更不在 MySQL 的语句执行范围内。(MySQL开发者专区)

所以，“慢查询日志没有异常”和“数据库访问链路没有异常”，不是同一个结论。

四、比占着连接等待更麻烦的，是占着锁等待

上一节没有显式开启跨语句事务。

如果把场景改成“更新一行数据，然后一直不提交”，影响就不只是占用一条连接了。

其他请求可能已经借到了自己的连接，却因为要更新同一行数据而等待锁。InnoDB 的更新操作需要取得相应锁，即使查询条件命中了主键，也不能绕过冲突锁的等待。(MySQL开发者专区)

4.1 会话 A：更新数据，但不结束事务

打开一个新的终端：

docker compose exec mysql mysql -ulab -p perf_lab

输入实验密码 local_lab_only，执行：

START TRANSACTION;

UPDATE comment_demo
SET content = 'updated by session A'
WHERE id = 1;

暂时不要提交，也不要回滚。

这条更新语句可以很快执行完成，但事务仍然没有结束。

4.2 会话 B：尝试更新同一行

再打开一个终端，用同样方式登录：

docker compose exec mysql mysql -ulab -p perf_lab

执行：

SET SESSION innodb_lock_wait_timeout = 30;

START TRANSACTION;

UPDATE comment_demo
SET content = 'updated by session B'
WHERE id = 1;

这时，会话 B 会等待会话 A 释放冲突锁。

请在这 30 秒的观察窗口内，执行下一步诊断。

4.3 会话 C：找到“谁在等谁”

在管理员会话中执行：

SELECT
    wait_age_secs,
    waiting_pid,
    blocking_pid,
    locked_table_schema,
    locked_table_name,
    locked_index,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

sys.innodb_lock_waits 展示的是正在发生的 InnoDB 锁等待关系，包括等待者、阻塞者、相关表和索引，以及当前语句。(MySQL开发者专区)

这里有一个很容易误判的字段：

blocking_query 为 NULL，不代表这个会话没有阻塞别人。

MySQL 官方说明，当发起阻塞操作的会话随后变为空闲状态时，这个字段可能返回 NULL。(MySQL开发者专区)

会话 A 就可能处于这种状态：更新已经执行完，客户端停在命令提示符，等待下一条指令，但事务尚未结束。

因此，不能仅凭“当前没有执行 SQL”就排除它。

4.4 把阻塞会话和事务年龄关联起来

继续执行：

SELECT
    trx.trx_mysql_thread_id AS connection_id,
    trx.trx_state,
    TIMESTAMPDIFF(
        SECOND,
        trx.trx_started,
        NOW()
    ) AS trx_age_seconds,
    trx.trx_rows_locked,
    trx.trx_rows_modified,
    trx.trx_query,
    th.PROCESSLIST_COMMAND AS session_command,
    th.PROCESSLIST_TIME AS state_age_seconds
FROM information_schema.innodb_trx AS trx
LEFT JOIN performance_schema.threads AS th
    ON th.PROCESSLIST_ID = trx.trx_mysql_thread_id
ORDER BY trx.trx_started;

重点看事务存在了多久、锁住多少行、修改多少行，以及对应连接当前在做什么。INNODB_TRX 提供事务开始时间、状态、MySQL 线程标识和锁定行数等诊断字段。(MySQL开发者专区)

需要区分两个概念：

连接当前空闲，不代表连接没有未结束的事务；事务存在时间长，也不代表它始终在执行 SQL。

这也是为什么只盯着“当前正在跑什么 SQL”，可能找不到真正的阻塞源。

4.5 正确结束实验

先在会话 A 执行：

ROLLBACK;

会话 B 的更新恢复执行后，再在会话 B 执行：

ROLLBACK;

如果会话 B 已经发生锁等待超时，也应显式结束这个实验事务。按照 MySQL 默认行为，锁等待超时通常只回滚当前语句，而不是自动回滚整个事务。(MySQL开发者专区)

五、一条长事务，如何拖慢整个聚合服务？

把前面的实验放到共享连接池的应用里，可以推导出这样的故障链：

graph LR
    A["事务迟迟未结束"] --> B["后续更新等待锁"]
    B --> C["等待者占用连接"]
    C --> D["连接池被占满"]
    D --> E["新请求等待连接"]
    E --> F["更多接口变慢"]

这条链路里存在两层不同的等待。

第一层发生在数据库内部：请求已经拿到连接，但 SQL 正在等待锁。

第二层发生在应用内部：连接池里的连接被前面的请求占用，新请求连数据库连接都拿不到。

在共享连接池的聚合项目中，这意味着一个模块的阻塞可能影响其他模块。后来的请求即使查询另一张表，也可能先卡在连接池入口。

因此：

连接池等待人数上涨，既可能是连接池容量问题，也可能是下游阻塞的结果。

这不是看到 waiting 增加就立即扩容连接池的理由，而是继续检查连接持有时间与数据库等待关系的信号。

别漏掉元数据锁

行锁之外，还要检查元数据锁，尤其是问题出现在执行表结构变更前后时。

SELECT
    object_schema,
    object_name,
    waiting_pid,
    blocking_pid,
    waiting_query_secs,
    waiting_query
FROM sys.schema_table_lock_waits
WHERE object_schema = 'perf_lab'
ORDER BY waiting_query_secs DESC;

这个视图用于展示元数据锁的等待和阻塞关系。(MySQL开发者专区)

某个尚未结束的事务即使只读取过一张表，也可能持有该表的元数据锁，从而阻止另一个会话修改表结构。MySQL 会把事务所需的相关元数据锁保留到事务结束。(MySQL开发者专区)

所以，排查时应分别确认：

行锁有没有阻塞关系，元数据锁有没有阻塞关系，以及连接池有没有排队。它们不能相互替代。

六、优化重点不是“连接越多越好”

6.1 先缩短不必要的连接持有时间

第一个实验中，最直接的修复不是把连接池从 2 调到 20，而是让不需要连接的工作离开连接持有范围。

不过，这个实验能安全移动等待，是因为它没有改变业务数据，也没有外部副作用。

真实业务不能机械照搬。

例如，“更新订单状态”和“扣减库存”原本需要保持原子性，不能为了缩短耗时，就随意拆成两个独立提交的事务。外部调用移到提交之后，也会引入“数据库已提交，但外部调用失败”的新状态。

正确的优化问题应该是：

哪些操作必须处于同一个一致性边界内，哪些操作只是因为代码写在一起，才被放进同一个事务？

MySQL 对降低锁冲突的建议同样强调：尽量减少第一次数据修改到事务提交之间的工作量，让锁以更短的时间覆盖更少的数据。(MySQL开发者专区)

6.2 用连接占用预算理解容量

假设一个稳定运行的系统，每秒完成 200 次连接借还，每次平均持有连接 100 毫秒。

那么，每秒消耗的连接时间是：

200 × 0.1 = 20 个连接秒。

也就是说，平均大约需要 20 条连接同时被占用。

如果平均持有时间上升到 500 毫秒，而仍希望维持同样的完成速率，平均占用需求就变成：

200 × 0.5 = 100 条连接。

这只是理想化的平均需求推算，不是连接池配置公式，更没有保证数据库能承受 100 条连接同时工作。

它说明的是：请求量不变，连接持有时间变长，也足以把原本够用的连接池耗尽。

6.3 连接池扩容必须看数据库总预算

扩大连接池可能有帮助，前提是原来的容量确实限制了吞吐，而数据库仍有承载余量。

它也可能把更多请求放进数据库内部，让竞争进一步加剧。HikariCP 的连接池容量指南明确讨论了连接过多对性能的负面影响，不能把连接数量直接当成处理能力。(GitHub)

对多个服务共同访问一个数据库的系统，应按整体预算考虑：

所有实例、所有连接池的潜在连接上限之和，还要加上运维、监控、迁移工具和其他客户端的连接需求。

给某个后台任务单独分配连接池，可以建立应用侧的并发边界，但它仍然会与在线业务竞争同一个数据库的资源。

隔离连接池，不等于隔离数据库。

七、超时配置要分层，异常之后还要收尾

“已经配置了超时”是一句信息量很低的话。

至少需要区分下面几种设置：

这些超时对应不同阶段，不能用一个参数替代其他参数，也不能把网络操作超时直接当成整个请求的总时限。(GitHub)

例如，把 connectionTimeout 设置为 1 秒，不能据此推断已经执行中的 SQL 也会在 1 秒后停止。

JDBC 对语句超时的规定涉及驱动检测到超时并尝试取消当前语句，也不应该被理解为一个绝对精准、覆盖所有后续处理的业务截止时间。(Oracle 文档)

工程上还需要考虑失败后的行为：事务是否结束，连接是否归还，重试是否可能重复执行业务，以及上游请求超时之后，下游任务是否仍在继续消耗资源。

不要把自动杀连接当成默认修复

诊断视图可能提供终止阻塞语句或连接的 SQL，但不应该把它们直接接入一个“不加判断的自动执行脚本”。

KILL QUERY 终止当前语句，但保留连接；KILL CONNECTION 则终止连接。两者不是同一种操作。(MySQL开发者专区)

对于已经空闲、但事务尚未结束的阻塞会话，仅仅终止“当前语句”未必解决问题。

更稳妥的处理顺序是先确认会话归属和业务影响，尽可能让业务主动结束事务，再决定是否终止连接。止血之后，仍要修复导致事务长期不结束的代码路径。

八、怎样证明优化真的有效？

不要只比较优化前后的平均接口耗时。

可以围绕以下几组数据验证：

比较时应尽量保持请求类型、并发量、数据规模、缓存状态和外部依赖延迟一致。

尤其要防止一种“假优化”：

把等待超时从 30 秒改成 1 秒，接口平均耗时下降了，但失败请求增加了。这可能是一项有价值的过载保护，却不能被报告成业务处理能力提升。

同样，把连接池调大后，如果连接获取更快，但锁等待、数据库负载和尾延迟更严重，也不能只挑改善的指标展示。

优化成功，不是某一个数字变好，而是在正确性不退步的前提下，系统用更少的等待完成了更多有效工作。

九、总结：先找到等待，再决定改哪里

这篇文章最重要的不是几条诊断 SQL，而是把几个容易混淆的概念分开：

连接获取慢，不一定是 SQL 慢；连接正在使用，不代表数据库正在计算；会话当前空闲，不代表事务已经结束。

慢查询日志依然有价值，但它只是诊断链路的一部分。应用侧的分段计时、连接池状态，以及数据库实时锁关系，需要放在一起解释。

当接口变慢时，先确定请求卡在获取连接之前、语句执行期间，还是数据库操作之后，再决定应该优化查询、缩短事务、调整连接池，还是处理外部依赖。

不要在不知道时间去了哪里的情况下，急着加索引、加连接或者重启服务。