人大金仓 KingbaseES 死锁排查方案：从日志监控到 SQL 代码优化的完整流程

第一章：理解死锁的形成原理

清晨的阳光照在DBA小王的工位上，他正对着屏幕上的事务阻塞报警发愁。KingbaseES的死锁就像突然爆发的交通拥堵，多个事务"车辆"相互卡在数据库的十字路口，谁也不肯退让半步。

死锁形成的经典四要素：

互斥条件：线程独占资源使用权限
请求保持：持有锁的同时请求新锁
不可剥夺：已获得的锁不能被强行释放
环路等待：形成事务间的环形等待链

"上周生产环境的订单扣减就遇到过这种情况"，小王边在测试环境复现问题边自语。两条更新语句形成了环路：

-- 事务A执行序列
BEGIN;
UPDATE account SET balance = balance - 100 WHERE user_id = 1; -- 持有用户1的X锁
UPDATE account SET balance = balance + 100 WHERE user_id = 2; -- 请求用户2的X锁

-- 事务B执行序列
BEGIN;
UPDATE account SET balance = balance - 50 WHERE user_id = 2;  -- 持有用户2的X锁
UPDATE account SET balance = balance + 50 WHERE user_id = 1;  -- 请求用户1的X锁

此时若两个事务的第二个更新语句同时执行，就会形成典型的相互等待环。

第二章：日志监控与初步诊断

通过KingbaseES的日志管理系统，我们首先定位异常时间点：

# 查看锁等待超时配置（默认50秒）
show deadlock_timeout;

# 设置长事务记录阈值（超过2分钟的事务）
ALTER SYSTEM SET log_min_duration_statement = '2min';
SELECT sys_reload_conf();

# 检索最近1小时的死锁日志
SELECT * FROM sys_stat_activity 
WHERE backend_start > NOW() - INTERVAL '1 hour'
AND wait_event_type = 'Lock';

异常日志中常见的关键信息模式：

2023-08-20 10:23:18 CST ERROR:  deadlock detected
2023-08-20 10:23:18 CST DETAIL:  
Process 89256 waits for ShareLock on transaction 1955; blocked by process 89331.
Process 89331 waits for ShareLock on transaction 1956; blocked by process 89256.

这显示进程89256和89331形成了相互等待，对应的需要追溯具体事务中的SQL操作。

第三章：深度排查

3.1 锁等待视图分析

使用系统视图诊断当前锁状态：

-- 查询当前所有锁的状态
SELECT 
    locktype, 
    relation::regclass,
    mode,
    transactionid,
    virtualtransaction,
    pid,
    granted
FROM sys_locks 
WHERE NOT granted;

-- 关联会话信息
SELECT 
    a.pid,
    a.query,
    l.relation::regclass,
    l.mode
FROM sys_locks l
JOIN sys_stat_activity a ON l.pid = a.pid
WHERE l.granted = false;

3.2 等待链可视化

生成锁等待链拓扑图：

WITH RECURSIVE lock_chain AS (
    SELECT 
        w1.pid as holding_pid,
        w2.pid as waiting_pid,
        w1.query as holding_query,
        w2.query as waiting_query
    FROM sys_stat_activity w1
    JOIN sys_locks l1 ON w1.pid = l1.pid AND l1.granted
    JOIN sys_locks l2 ON l1.locktype = l2.locktype 
        AND l1.lockid = l2.lockid 
        AND NOT l2.granted
    JOIN sys_stat_activity w2 ON l2.pid = w2.pid
)
SELECT * FROM lock_chain;

3.3 历史死锁溯源

分析系统表记录的近期死锁事件：

SELECT 
    deadlocks,
    stats_reset
FROM sys_stat_database 
WHERE datname = current_database();

-- 解析详细的死锁报告（需要开启参数）
SELECT pg_blocking_pids(pid),* 
FROM sys_stat_activity 
WHERE state = 'active';

第四章：SQL优化化解死锁

4.1 访问顺序统一原则

优化前的风险代码：

-- 转账业务原始实现
CREATE OR REPLACE FUNCTION transfer_funds(
    from_user INT,
    to_user INT,
    amount NUMERIC
) RETURNS VOID AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
    UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
END;
$$ LANGUAGE plpgsql;

调整为固定顺序：

-- 改进后的处理逻辑
CREATE OR REPLACE FUNCTION safe_transfer(
    from_user INT,
    to_user INT,
    amount NUMERIC
) RETURNS VOID AS $$
DECLARE
    first_user INT := LEAST(from_user, to_user);
    second_user INT := GREATEST(from_user, to_user);
BEGIN
    UPDATE accounts SET balance = balance - amount 
    WHERE user_id = first_user;

    UPDATE accounts SET balance = balance + amount 
    WHERE user_id = second_user;
    
    -- 反向操作需要同步调整
    IF from_user > to_user THEN
        UPDATE accounts SET balance = balance + amount 
        WHERE user_id = second_user;
        
        UPDATE accounts SET balance = balance - amount 
        WHERE user_id = first_user;
    END IF;
END;
$$ LANGUAGE plpgsql;

4.2 锁粒度的把控技巧

对比不同隔离级别的影响：

-- 查看当前事务隔离级别
SHOW default_transaction_isolation;

-- 调整会话级隔离级别
SET SESSION CHARACTERISTICS AS TRANSACTION 
    ISOLATION LEVEL REPEATABLE READ;

批量更新操作优化示例：

-- 原始批量处理（容易引发范围锁）
UPDATE orders 
SET status = 'processed'
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 分片处理优化
DO $$
DECLARE
    day_count INT := 31;
BEGIN
    FOR i IN 0..day_count-1 LOOP
        UPDATE orders 
        SET status = 'processed'
        WHERE order_date = '2023-01-01'::DATE + i;
        COMMIT;
    END LOOP;
END $$;

第五章：综合解决方案实践

5.1 库存扣减场景的优化案例

创建测试表结构：

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    stock INT CHECK(stock >= 0),
    version INT DEFAULT 0
);

INSERT INTO inventory VALUES 
(1, 100, 0),
(2, 200, 0);

悲观锁实现方案：

-- 使用SELECT FOR UPDATE锁机制
BEGIN;
SELECT stock FROM inventory WHERE item_id = 1 FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 1;
COMMIT;

乐观锁优化方案：

-- 基于版本号的更新重试
CREATE OR REPLACE FUNCTION deduct_stock(
    p_item_id INT,
    deduct_count INT
) RETURNS BOOLEAN AS $$
DECLARE
    current_version INT;
BEGIN
    SELECT version INTO current_version 
    FROM inventory WHERE item_id = p_item_id;
    
    UPDATE inventory 
    SET stock = stock - deduct_count,
        version = version + 1
    WHERE item_id = p_item_id 
      AND version = current_version;
    
    RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

-- 调用示例（最多重试3次）
DO $$
DECLARE
    retry_count INT := 0;
    success BOOLEAN;
BEGIN
    LOOP
        success := deduct_stock(1, 1);
        EXIT WHEN success OR retry_count >= 3;
        retry_count := retry_count + 1;
        PERFORM pg_sleep(0.5 * retry_count);
    END LOOP;
END $$;

第六章：关联技术的深度集成

6.1 连接池的参数调优

配置Druid连接池参数示例：

# 最大等待毫秒数
maxWait=2000
# 最小空闲连接
minIdle=5
# 最大活跃连接
maxActive=50
# 获取连接测试
testOnBorrow=true
# 验证查询
validationQuery=SELECT 1

6.2 执行计划分析技巧

解析复杂查询的锁开销：

EXPLAIN (ANALYZE, BUFFERS) 
UPDATE products 
SET price = price * 1.1 
WHERE category_id = 5;

关注执行计划中的关键指标：

Rows Removed by Filter: 低效过滤操作
Shared Hit Blocks: 缓存命中率
Exclusive Lock Time: 排他锁耗时

第七章：应用场景与技术选型

7.1 典型应用场景

金融交易系统：高频账户变更操作
电商库存系统：促销期间并发扣减
工单处理系统：状态变更的并行处理

7.2 方案对比分析

解决方案	适用场景	优势	缺点
锁顺序调整	简单事务逻辑	实现简单	复杂场景难以覆盖
乐观锁机制	高并发写场景	避免长事务阻塞	需要业务重试机制
连接池控制	突发流量场景	快速见效	需配合其他方案使用
执行计划优化	复杂查询引发的死锁	从根源解决问题	需要DBA深度介入

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。