WEBKT

排查 PostgreSQL 长事务：如何利用 Linux 动态追踪工具找到锁不释放的客户端连接

2 0 0 0

在 PostgreSQL 的日常运维中，最让人头疼的场景之一莫过于长事务引发的锁阻塞。

当收到数据库告警，提示大量写入请求被阻塞时，你迅速连上数据库，执行了如下 SQL：

SELECT pid, age(backend_xmin), query, state, usename, client_addr 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
ORDER BY age(backend_xmin) DESC;

你找到了罪魁祸首：一个处于 idle in transaction（事务中空闲）状态的进程 PID 12345。它已经持锁 20 分钟了，而 query 字段只显示了它执行的上一个查询（比如一个无害的 SELECT 1），并不是真正持有锁的那个写入/更新语句。

更糟糕的是，由于应用架构引入了连接池（如 PgBouncer）、微服务网关，或者客户端处于 K8s Pod 内部，client_addr 显示的只是一个内部网关的 IP，你根本无法定位到到底是哪台机器、哪个进程、哪段代码触发了这个长事务且迟迟不释放连接。

此时，传统的 SQL 诊断手段已经无能为力。本文将带你跳出数据库层面，利用 Linux 动态追踪技术（eBPF & bpftrace），直接穿透到内核与网络协议栈，把这个持锁不释放的客户端连接揪出来。

为什么常规方法失效了？

在深入动态追踪之前，我们需要明白为什么常规手段会失灵：

  1. 应用层死锁/假死：客户端代码在开启事务（BEGIN）并执行完更新（UPDATE）后，由于业务逻辑缺陷（如调用了响应极慢的外部第三方 API，或者内部线程发生死锁），导致应用进程“假死”，迟迟没有发送 COMMIT 或 ROLLBACK。
  2. 网络隐蔽中断（Half-Open）：客户端和数据库之间的物理链路可能已经中断（如防火墙静默丢弃了空闲连接，或者客户端所在容器异常重启，未能发送 TCP FIN 包）。此时 PostgreSQL 后端进程（Backend）依然认为连接存活，傻傻地等待客户端的下一个指令，从而将事务和锁无限期挂起。
  3. 连接池混淆：应用通过连接池复用连接，pg_stat_activity 中的客户端 IP 和端口只能对应到连接池代理，无法对应到真实的下游业务服务器。

第一步：定位内核层面的 Socket 文件描述符 (FD)

既然 PostgreSQL 的 Backend 进程（PID 12345）正在等待客户端数据，那么在操作系统层面，它必然阻塞在某个网络 read 或 recv 系统调用上。

我们首先通过 /proc 文件系统，获取该进程拥有的 Socket 信息：

ls -lh /proc/12345/fd | grep socket

输出可能类似于：

lr-x------ 1 postgres postgres 64 Oct 24 10:00 0 -> /dev/null
l-wx------ 1 postgres postgres 64 Oct 24 10:00 1 -> /dev/null
lrwx------ 1 postgres postgres 64 Oct 24 10:00 9 -> socket:[12345678]

这里的 9 就是该 TCP 连接的文件描述符（FD），12345678 是该 Socket 在 Linux 内核中的 inode 编号。

接着，利用 ss 工具获取该 Socket 对应的对端 IP 和端口：

ss -tni -p | grep "pid=12345,"

输出结果会展现出底层的 TCP 状态及网络参数：

ESTAB  0      0      10.0.1.100:5432    10.0.2.50:45678   users:(("postgres",pid=12345,fd=9))
     cubic wscale:7,7 rto:200 rtt:0.12/0.04 ato:40 mss:1460 rcvspace:14600 ssthresh:10 ssthresh:10

这里我们拿到了关键线索：对端客户端 IP 是 10.0.2.50，端口是 45678。

第二步：利用 eBPF / bpftrace 动态分析连接状态

仅仅拿到 IP 和端口还不够。如果这个连接处于“僵死”状态（Half-Open），我们该如何向开发团队证明“连接已死，是客户端没有发送数据，而不是数据库卡死”？

我们可以使用 bpftrace（基于 eBPF 的高阶追踪工具）来实时监控该进程的系统调用行为。

1. 追踪该进程是否仍在接收/发送数据

我们编写一个简单的 bpftrace 单行脚本，来捕获 PID 12345 上的 sys_enter_recvfrom 和 sys_enter_read 动作，看看它是否处于无限等待状态：

bpftrace -e '
tracepoint:syscalls:sys_enter_recvfrom /pid == 12345/ {
    @start[tid] = nsecs;
}
tracepoint:syscalls:sys_exit_recvfrom /pid == 12345 && @start[tid]/ {
    $duration_ms = (nsecs - @start[tid]) / 1000000;
    printf("Backend PID %d waiting on socket, blocked for %d ms\n", pid, $duration_ms);
    delete(@start[tid]);
}'

如果运行该脚本后，屏幕上没有任何输出，或者只输出了一条长达数分钟未返回的记录，这铁证如山地表明：数据库完全健康，它正在死等客户端发送数据（客户端已经停滞，或正在处理极其耗时的本地业务）。

2. 捕捉网络层面的保活（Keepalive）心跳

如果怀疑是网络链路中断导致的“隐蔽半开连接”，我们可以利用 eBPF 动态追踪内核的 TCP 定时器（特别是 Keepalive 探测包）：

bpftrace -e '
kprobe:tcp_keepalive_timer {
    $sk = (struct sock *)arg0;
    $inet = (struct inet_connection_sock *)$sk;
    $daddr = $sk->__sk_common.skc_daddr;
    $dport = $sk->__sk_common.skc_dport;
    
    // 只过滤我们关心的客户端 IP (例如 10.0.2.50)
    // 10.0.2.50 的十六进制大端表示为 0x32020a0a (需要根据实际IP转换)
    if ($daddr == 0x32020a0a) {
        printf("TCP Keepalive timer expired for client %s:%d, state: %d\n", 
               ntop($daddr), bswap($dport), $sk->__sk_common.skc_state);
    }
}'

如果在物理网络上确实有丢包或防火墙拦截，你会观察到内核在不断尝试发送 TCP Keepalive 探测包，却始终没有收到对端的 ACK 回包。

第三步：更进一步，获取持锁事务的历史 SQL

有些时候，客户端连接并没有断，它只是在执行完一个长事务中的更新语句后，因为应用层业务代码逻辑（如：DB.Begin() -> DB.Exec(Update) -> HTTP_Request_To_Third_Party(Timeout 30s) -> DB.Commit()）被阻塞。

当发生阻塞时，pg_stat_activity 只能看到 idle in transaction。我们如何知道它在阻塞前，在这个事务里执行了哪些 SQL 语句？

如果未开启 PostgreSQL 的 log_min_duration_statement（或者该参数设置得比较大），我们同样可以通过动态追踪工具，在操作系统层去拦截 PostgreSQL 后端进程的用户态符号。

PostgreSQL 的查询解析入口函数通常是 exec_simple_query。我们可以利用 bpftrace 挂载到 postgres 二进制文件的 USDT（用户态静态定义跟踪）探针或直接挂载到函数符号上：

# 查找 postgres 二进制路径，通常在 /usr/usr/lib/postgresql/XX/bin/postgres
# 监控指定 PID 正在执行的 SQL 语句
bpftrace -e '
uprobe:/usr/lib/postgresql/14/bin/postgres:exec_simple_query /pid == 12345/ {
    printf("PID %d executing SQL: %s\n", pid, str(arg0));
}'

当长事务发生前，只要你开启此脚本，就能在内核态无损地捕获到该连接执行的所有历史 SQL，彻底告别“管中窥豹”的窘境。

彻底根治长事务的黄金法则

定位到具体的客户端和问题根源后，我们需要在数据库和系统层面设置兜底机制，防止单点故障引发全库瘫痪：

1. 数据库层面的主动防御

不要信任客户端能够百分之百正确释放连接。在 PostgreSQL 中，必须配置以下两个关键参数：

  • idle_in_transaction_session_timeout：
    限制事务处于 idle in transaction 状态的最大时长（推荐设置为 10s 到 30s）。一旦超时，PostgreSQL 会主动中断该连接并释放其持有的所有锁，保障大盘稳定。
    ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();
  • lock_timeout：
    限制获取锁的等待超时时间，避免因某一个长事务持锁，导致其他后续请求无限期排队，最终撑爆数据库连接池。

2. 操作系统与 TCP 协议栈层面优化

缩短 TCP Keepalive 的探测间隔，让内核更快地识别并清理由于网络原因导致的“僵死连接”：

编辑 /etc/sysctl.conf：

# 减少 TCP 空闲探测时间（默认 7200 秒，太慢了）
net.ipv4.tcp_keepalive_time = 600
# 探测包发送间隔
net.ipv4.tcp_keepalive_intvl = 10
# 探测失败重试次数
net.ipv4.tcp_keepalive_probes = 6

保存后执行 sysctl -p 生效。

同时，确保 PostgreSQL 的 postgresql.conf 中开启了 Keepalives 支持：

keepalives_idle = 600
keepalives_interval = 10
keepalives_count = 6

总结

当传统的 SQL 层监控失效时，Linux 的**动态追踪技术（eBPF）**为我们打开了一扇观测系统本质的窗户。通过 /proc 确认文件描述符，利用 ss 和 bpftrace 穿透至 TCP 内核状态与用户态函数，我们不仅能精准定位到顽固长事务背后的客户端元凶，更能从根本上厘清是网络故障、应用逻辑缺陷还是配置失当。

把这些诊断工具加入你的武器库，下次面对死锁和长事务告警时，你将不再盲目猜测，直击痛点。

DBA内核杂谈 PostgreSQLeBPF性能调优

评论点评