说明
当发生行锁阻塞，我们可能要做两件事情：
- 应急解开锁阻塞
- 如果锁阻塞频繁发生，导致业务响应时间变长，需要帮助业务找到相关逻辑，优化业务，减少锁阻塞的发生
本文档主要介绍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. 通过日志分析锁阻塞
当没有抓到锁阻塞的现场，但是又想分析业务为什么发生锁等待，可以从 observer.log 来找到锁阻塞的信息。搜索关键字：on_wlock_retry
日志中可能会有很多相关日志，每一次重试加锁都会打印一行日志。可以根据日志中的事务 ID 去gv$sql_audit 中查看事务内容，和业务一起分析原因。
网友评论