说明

当发生行锁阻塞，我们可能要做两件事情：

• 应急解开锁阻塞
• 如果锁阻塞频繁发生，导致业务响应时间变长，需要帮助业务找到相关逻辑，优化业务，减少锁阻塞的发生

本文档主要介绍V3版本中找出阻塞源、分析业务 SQL 的方法。

1. 制造锁阻塞场景

--session1
--为方便观察，把超时参数调大
set global ob_query_timeout = 3600000000;
set global ob_trx_timeout = 3600000000;
set global ob_trx_idle_timeout = 3600000000;
ob_trx_lock_timeout 默认为-1，锁等待没有超时时间

--准备数据
create table t1 (id number,name varchar2(12),primary key(id));
insert into t1 values(1,'A1');
insert into t1 values(2,'B1');
commit;
select * from t1;

--开启事务加锁
start transaction;
select * from t1 where id = 1 for update;

--session2
--开启事务加锁
start transaction;
select * from t1 where id = 2 for update;
select * from t1 where id = 1 for update;

--session1 制造死锁场景（默认不开启死锁检测）
select * from t1 where id = 2 for update;

2. 通过视图分析锁阻塞

如果有现场，我们可以通过系统视图分析锁阻塞。

2.1 查找锁的阻塞关系

先给出一个直接查出锁阻塞关系的方法，然后再介绍 __all_virtual_lock_wait_stat、__all_virtual_trans_lock_stat 两个视图的限制。

SELECT 
wait.TENANT_ID,
wait.SVR_IP,
wait.table_name,
wait.table_id_extract waiting_table_id,
wait.need_wait,
wait.SESSION_ID waiting_session_id,                     -- 被阻塞事务的session_id（后端连接ID）
tx.trans_id waiting_trans_id,                           -- 被阻塞事务的ID
tx.ctx_create_time waiting_trx_started,                 -- 被阻塞事务的开启时间
wait.waiting_rowkey,                                    -- 正在尝试加锁的行的主键值
usec_to_time(wait.RECV_TS) waiting_query_started,       -- 被阻塞的SQL开始执行的时间
usec_to_time(wait.LOCK_TS) waiting_last_lock_started,   -- 被阻塞的SQL上一次尝试加锁的时间（10秒重试一次）
wait.TRY_LOCK_TIMES waiting_lock_retry_cnt,             -- 加锁重试次数
wait.TIME_AFTER_RECV/1000 waiting_query_age_ms,         -- 被阻塞的SQL等待锁的时长，单位毫秒
trans.trans_id blocking_trans_id,                       -- 持有锁的事务ID
trans.session_id blocking_session_id,                   -- 持有锁的session_id，即阻塞源
trans.rowkey blocking_rowkey,                           -- 持有锁的行的主键值（防止异常join结果有误，可人工比对）
trans.ctx_create_time blocking_trx_started              -- 持有锁的事务开启时间
FROM __all_virtual_trans_lock_stat trans
JOIN (
    SELECT 
        *,
        SUBSTRING_INDEX(SUBSTRING_INDEX(rowkey, 'table_id=', -1), ' ', 1) AS table_id_extract,
        CONCAT(
            'rowkey_object=[{"%',
            '":"',
            SUBSTRING_INDEX(SUBSTRING_INDEX(rowkey, '":"', -1), '"}]', 1),
            '"}]'
        ) AS waiting_rowkey
FROM gv$lock_wait_stat
) wait
ON trans.table_id = wait.table_id_extract 
AND trans.rowkey LIKE CONCAT('%', wait.waiting_rowkey, '%')
join __all_virtual_trans_stat tx 
on wait.SESSION_ID=tx.session_id\G

2.2 __all_virtual_lock_wait_stat 查看锁等待情况

只有当发生行锁阻塞 __all_virtual_lock_wait_stat 才会有信息：

• 每行信息表示被阻塞的连接当前正在尝试对哪一行加锁，以及它的后端连接 ID
• 无法直观看出是被哪个连接阻塞

以如下示例来看：

• session_id: 3222011615 正在获取 table_id=1100611139453783，rowkey=1 的锁，被阻塞了，阻塞源未知
• session_id: 3221980123 正在获取table_id=1100611139453783，rowkey=2 的锁，被阻塞了，阻塞源未知

2.3 __all_virtual_trans_lock_stat 查看持有锁的源头

上一步通过 __all_virtual_lock_wait_stat 查看被阻塞的事务正在获取 rowkey=1 这一行的锁，因此接下来通过 __all_virtual_trans_lock_stat 查看持有 rowkey=1 这一行锁的是哪个事务、哪个连接：

• session_id: 3221980123 持有了 rowkey=1 这一行锁，根据上一步得到的信息，可推测它阻塞了 session_id: 3222011615
• session_id: 3222011615 持有 rowkey=2 这一行锁，根据上一步得到的信息，可推测它阻塞了 session_id: 3221980123

2.4 kill 锁源头

此处其实是死锁场景，如果要应急解开锁阻塞，需要 kill 阻塞源，也就是持有锁的 session。这里我们选择把 session2 执行的 query 杀掉：

--连2883端口登录业务租户
kill query 3222011615;

2.5 分析业务锁阻塞的原因

通过 gv$sql_audit 可以看到锁阻塞的SQL执行情况：

• 被锁阻塞的SQL执行时 retry_cnt 很大
• 被锁阻塞的SQL执行时，elapsed_time 很大，但是 execute_time 很小

要分析业务为什么会有锁阻塞，需要根据事务ID查询 gv$sql_audit 记录的每个事务的所有SQL：

select usec_to_time(request_time), svr_ip, sid, transaction_hash,
user_name, query_sql, ret_code, plan_type, elapsed_time,execute_time, retry_cnt from 
gv$sql_audit where transaction_hash=14355367465281276788 order by 
request_time;

select usec_to_time(request_time), svr_ip, sid, transaction_hash,
user_name, query_sql, ret_code, plan_type, elapsed_time ,execute_time, retry_cnt from 
gv$sql_audit where transaction_hash=12615662878457240999 order by 
request_time;

结果如下：

• 第一个事务被 kill 回滚了，因此我们只看到一个 SQL
• 第二个事务与第一个事务都对 id=2 这一行数据加锁，所以发生阻塞，把这个信息给到业务开发，优化业务逻辑

3. 通过日志分析锁阻塞

日志中可能会有很多相关日志，每一次重试加锁都会打印一行日志。可以根据日志中的事务 ID 去gv$sql_audit 中查看事务内容，和业务一起分析原因。