数据库锁表现场排查与处理手册

一、现场快速排查步骤（5 分钟定位问题）

1. 确认是否为锁表

• 现象判断：接口超时、数据库操作卡住、多个请求排队无响应

• 快速验证：在数据库客户端执行简单查询（如SELECT 1），若正常返回则排除数据库宕机，大概率是锁表

2. 查看当前锁状态（MySQL 为例）

-- 查看InnoDB引擎锁信息（核心命令）
SHOW ENGINE INNODB STATUS

-- 查看所有活跃事务
SELECT * FROM information_schema.INNODB_TRX

-- 查看长事务
SELECT 
  trx_id AS 事务ID,
  trx_mysql_thread_id AS 线程ID,
  trx_query AS 执行SQL,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 运行秒数
FROM information_schema.INNODB_TRX 
WHERE trx_state = 'RUNNING'  -- 只查活跃事务
ORDER BY 运行秒数 DESC;      -- 按运行时间排序，优先排查长事务

3. 定位关键信息

从上述结果中提取：

• 阻塞线程 ID（trx_mysql_thread_id）：导致锁表的源头

• 阻塞 SQL（trx_query）：具体执行的 SQL 语句

• 事务开始时间（trx_started）：若超过 30 分钟，基本可判定为异常事务

二、临时处理方案（快速恢复服务）

1. 终止阻塞事务（紧急操作）

-- 替换12345为实际阻塞线程ID

KILL 12345;

注意事项：

• 执行前确认该事务未涉及核心业务数据（如支付、订单提交）

• 若事务已执行部分更新，终止后会自动回滚，可能导致数据暂时不一致（需后续校验）

• 生产环境建议先记录阻塞 SQL 和事务 ID，再执行 KILL

2. 临时调整锁等待超时

-- 临时设置锁等待超时为10秒（默认50秒），避免长时间阻塞
SET GLOBAL innodb_lock_wait_timeout = 10;

适用场景：无法立即终止阻塞事务时，让后续请求快速失败，避免大量请求堆积

3. 手动提交 / 回滚事务（若有权限）

-- 若阻塞事务是未提交的手动事务，可尝试提交
COMMIT;

-- 或回滚
ROLLBACK;

三、根本解决方法（避免再次发生）

1. 优化引发锁表的 SQL

• 添加索引：针对阻塞 SQL 的WHERE条件字段建索引

-- 示例：为last_login字段建索引
ALTER TABLE users ADD INDEX idx_last_login (last_login);

• 减少扫描行数：用LIMIT限制批量操作数量，避免一次性更新 / 删除过多数据

2. 优化 Java 代码中的事务逻辑

// 错误示例：事务包含耗时操作
@Transactional
public void processData() {
    // 远程调用（耗时5秒）
    remoteService.call();
    // 数据库更新（实际只需0.1秒，但事务已持有锁5秒）
    userMapper.updateStatus();
}

// 正确示例：缩短事务范围
public void processData() {
    // 事务外执行耗时操作
    remoteService.call();
    // 单独开启事务处理数据库操作
    transactionTemplate.execute(status -> {
        userMapper.updateStatus();
        return null;
    });
}

3. 规范事务使用

• 禁止在事务中执行：远程调用、文件 IO、循环处理大量数据

• 控制事务时长：核心业务事务建议不超过 3 秒

• 统一加锁顺序：多表操作时，所有事务按固定表顺序访问（如先操作 users 表，再操作 orders 表）

4. 数据库配置优化

# SpringBoot项目连接池配置（application.yml）
spring:
  datasource:
    hikari:
      maximum-pool-size: 20  # 根据服务器CPU核数配置（建议核数*2）
      connection-timeout: 30000  # 连接超时30秒
      idle-timeout: 600000  # 空闲连接10分钟回收

四、事后校验与复盘

1. 数据一致性校验：检查被终止事务涉及的表，确认数据无异常（如订单状态、库存数量）

2. 日志分析：从应用日志中查找阻塞 SQL 的调用链路，确认是否有代码逻辑漏洞

3. 制定预案：针对核心业务表，提前编写锁表应急处理脚本（包含查询、KILL 命令）