在 OceanBase 中定位上锁 SQL 并分析锁重试的机制

这篇文章希望从实用性的角度为大家提供在 OceanBase 观测锁的方法顺便分析 OceanBase 的锁特性以便用户更好理解锁与锁重试的机制。同时感谢 OceanBase 解决方案同学书水以及产研同学——涧月、逸畅、亨元、龙吟、洛梵对文章的帮助和建议。立即试用 OceanBase 企业版体验国产数据库能力180 天免费试用零门槛开通一个问题如何查询上锁的 SQL这个问题在运维上是个比较难处理的问题具体场景如下DBA 发现一个 SQL 在等锁错误码是 -6005可以通过 GV$OB_LOCKS 视图来查到持有锁的那个 SESSION ID但是通过 PROCESSLIST 视图却看到这个 SESSION 是 SLEEP 状态不知道这个 SESSION 能不能杀也无法确定这个是什么业务的请求无法定位冲突的关联业务。构造案例CREATE TABLE TEST_LOCK( ID INT PRIMARY KEY, VAL VARCHAR(100) ); INSERT INTO TEST_LOCK VALUES(1,K1),(2,K2), (3,K3),(4,K4),(5,K5); COMMIT; -- AUTO COMMIT 是 OFF 的状态 -- 事务1 先执行 SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 事务2 SELECT ID FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 进入锁等待上面事务 2 中的 SQL 被事务 1 的 SQL 上的锁堵塞了。先通过这个 SQL 查出现在所有的锁对“锁的对”指的就是持有锁的会话和申请锁的会话会成对出现往下看就清晰了。SELECT K.R_ID 锁对ID, K3.ID 会话ID, CASE WHEN K.TRANS_ID K1.TRANS_ID THEN 申请锁 WHEN K.ID1 K1.TRANS_ID THEN 持有锁 END AS 操作, K1.TRANS_ID 事务ID, ROUND(K1.CTIME /1000000,2) 持续时间(s), K3.INFO, K3.TOP_INFO, K3.RETRY_INFO, K3.RETRY_CNT, K2.DATABASE_NAME , K2.TABLE_NAME , K.ID3_2 上锁行, NOW() TIME_NOW, DATE_SUB(NOW(), INTERVAL round(K1.CTIME / 1000000,3) SECOND) AS LOCK_TIME, CASE WHEN K1.ID1 K3.TRANS_ID THEN concat( WITH KK AS (SELECT REQUEST_ID FROM OCEANBASE.GV$OB_SQL_AUDIT WHERE TX_ID , K1.ID1 , ) SELECT /* USE_NL(KK K) */K.* FROM OCEANBASE.GV$OB_SQL_AUDIT K WHERE SVR_IP \ , K1.SVR_IP , AND SVR_PORT , K1.SVR_PORT , AND TENANT_ID , K1.TENANT_ID , AND REQUEST_ID IN (SELECT REQUEST_ID FROM KK) AND REQUEST_TIME IS NOT NULL AND UPPER(QUERY_SQL) LIKE \% , UPPER(K2.TABLE_NAME) , %\;) END AS DO_SQL FROM (SELECT ROW_NUMBER() OVER () AS R_ID,K.TRANS_ID,K.ID1,K.ID3, SUBSTR(K.ID3,1,INSTR(K.ID3,-)-1) AS ID3_1, SUBSTR(K.ID3,INSTR(K.ID3,-)1)AS ID3_2 FROM OCEANBASE.GV$OB_LOCKS K WHERE K.TYPE TR AND K.BLOCK 1) K LEFT JOIN OCEANBASE.GV$OB_LOCKS K1 ON K.ID3 K1.ID3 LEFT JOIN OCEANBASE.DBA_OB_TABLE_LOCATIONS K2 ON K.ID3_1 K2.TABLET_ID AND ROLE LEADER LEFT JOIN OCEANBASE.GV$OB_PROCESSLIST K3 ON K1.TRANS_ID K3.TRANS_ID ORDER BY K.R_ID,K1.TRANS_ID;结果如图所示图1图2解释一下从上面这条 SQL 中查询出的内容锁对就是指成对出现的持有者和申请者的编号是我自己创建的编号就是清晰表示他们是一对会话 IDSESSION ID操作持有锁 / 申请锁事务 IDTRANS ID持续时间持有锁 / 申请锁 的持续时间INFO会话正在执行的 SQL可能是包含在个 PL 中的 SQL。TOP INFO会话执行的外层 SQL简单理解就是调用的 PL。RETRY INFO重试的原因等锁重试就是 -6005RETRY CNT重试次数DATABASE NAME锁的那张表的 SCHEMATABLE NAME锁住的表名上锁行指的是表的主键对应的值锁上在主键上TIME NOW当下的查询时间LOCK_TIME锁操作的时间对于持有锁来说是开始持有的时间对于申请锁来说是第一次申请的时间。DO_SQL拷贝出来去 SQL_AUDIT 中查持有者是用什么 SQL 上的锁主要注意的是上面的查锁 SQL 只能查被行锁堵塞的情况。希望直接解开锁可以 KILL 持有者的 会话 ID。如果想要知道持有者用什么 SQL 上的锁拷贝出 DO_SQL 去执行即可。COPY 出来的 SQL 如下WITH KK AS(SELECT REQUEST_ID FROM OCEANBASE.GV$OB_SQL_AUDIT WHERE TX_ID 14983239 ) SELECT /* USE_NL(KK K) */K.* FROM OCEANBASE.GV$OB_SQL_AUDIT K WHERE SVR_IP 11.161.204.62 AND SVR_PORT 2882 AND TENANT_ID 1002 AND REQUEST_ID IN (SELECT REQUEST_ID FROM KK) AND REQUEST_TIME IS NOT NULL AND UPPER(QUERY_SQL) LIKE %TEST_LOCK%;不难理解实际上就是查 GV$OB_SQL_AUDIT 中匹配 TX_ID 和 TEST_LOCK 表相关的 SQL但是为什么要写成上面这样复杂的 SQL 当然是为了性能。首先 OCEANBASE.GV$OB_SQL_AUDIT 这个表是内存里的虚表组装的视图有联合主键 svr_ip , svr_port , tenant_id , request_id )参考下面源码def_table_schema( owner xiaoyi.xy, tablegroup_id OB_INVALID_ID, table_name __all_virtual_sql_audit, rowkey_columns [ (svr_ip, varchar:MAX_IP_ADDR_LENGTH), (svr_port, int), (tenant_id, int), (request_id, int), ], normal_columns [ ... ], partition_columns [svr_ip, svr_port], vtable_route_policy distributed, index {all_virtual_sql_audit_i1 : { index_columns : [tenant_id, request_id], index_using_type : USING_BTREE,index_table_id : 14992,index_table_id_ora : 19999}}, )也就是按照给定主键各列的值作为 WHERE 条件是可以走 TABLE GET 算子的。另外再解释一下为什么要在 WITH 的第一个表达式里单独获取 REQUEST_ID原因是这样获取 REQUEST_ID 的效率是最高的目前虚表不支持 filter pushdown因此都需要把一行完整的数据先投影出来才能进行 filter 计算。然而业务模型中通常虚表的 filter 列较少而 output 列通常是表上所有的列。简单的理解就是 SELECT REQUEST_ID FROM OCEANBASE.GV$OB_SQL_AUDIT WHERE TX_ID 14983239; 虽然走了全表扫描但是 output 除了主键以外就吐了一个列 TX_ID快很多。我自己的测试是通常一个较大的 GV$OB_SQL_AUDIT这样查可以从 4 分钟左右变成 20 秒内。额外再说 2 个点研发同学已经针对虚表不支持 filter pushdown 这个点做出了优化新版本发布之后马上就不需要绕弯子查了。目前没有的情况下可以考虑试试这个方式查询。这样优化是否有效可以查看执行计划如果 SQL_AUDIT 本身很小的话比如小于 10w 行内容那优化器可能还是不会选择 TABLE GET 算子的。只要理解了原理你就可以灵活地运用这个查询了。需要注意的是因为 SQL_AUDIT 会因为容量限制淘汰最早的信息所以如果事务是锁了很久的则无法通过这个 SQL 获取到持有锁的相关信息。一个有效的方法是按照某个周期比如 1 分钟把长时间持锁会话的上锁 SQL 相关 SQL_AUDIT 信息落盘。如果落盘就需要保证落盘 SQL 的性能以免一个周期内完成不了落盘正好可以用上面定制的 SQL。两个锁特性2.1锁是上在主键上的是一行一行按照某个顺序上锁的要理解这个特性执行下面的 SQL 观测即可。-- 事务1 SELECT * FROM TEST_LOCK FOR UPDATE; -- 查看 SQL SELECT * FROM OCEANBASE.GV$OB_LOCKS;看图图3TYPETR 的意思是行锁。ID 3 这列显示了锁对应的主键值前面的“203189”对应的是表的 TABLET_ID后面的 INT1 对应的是锁对应的主键上的主键值。锁是上在主键上CTIME获取到锁以后的持续时间。这里特别需要注意每个 CTIME 的值都是不一样的千万不要认为可以看出是每个行的锁获取的先后时间这里的时间差来自于虚拟表的实现由于每行数据都是临时构造的其中 CTIME current_time() - tx_ctx_create_time() 每行的构造都获取了新的 current_time() tx_ctx_create_time() 相同的情况下就造成了后构造的行CTIME 大的现象。说明 在与研发沟通以后后续版本将得到优化上面红圈内的 CTIME 将保持一致也就是抵消了行先后构造造成的 current_time 误差。那么如何观察行是一行一行按照某个顺序上锁的可以参考第三节的第二个实验我会在实验后给出解释。请注意无法给出一个明确的上行锁顺序规则因为情况复杂比如开启的并行或者是分布式的情况有很多种可能。如果最简单的 SQL 则应该和主键扫描的顺序一致。2.2 锁的重试一般会由锁管理器来管理也可能占着线程直接重试简单来说一个 SESSION 在执行 SQL 的过程中如果遇到锁需要的是等待并按照某个时间周期重试如果直接占着线程去重试等于霸占一个CPU性能上显然是不合适的于是就有了一个叫做锁管理器的东西Lock Wait Mgr事务进入等锁状态就会把自己交给 Lock Wait Mgr 。这样在等锁的时候CPU 就可以用来给其他线程提供服务等有需要重试的时候再唤醒相关的事务线程。详见文末OceanBase 官方文档图4来看一个占用线程重试的案例-- 创建一个存过 DELIMITER $$ CREATE PROCEDURE JINCHUAN_TEST1() BEGIN SELECT 1 ; SELECT ID FROM TEST_LOCK WHERE ID 2 FOR UPDATE; END$$ DELIMITER ; -- AUTO COMMIT 是 ON 的状态【这个很重要】 SET autocommit ON; -- 事务1 先执行 BEGIN; SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 事务2 BEGIN; CALL JINCHUAN_TEST1; -- 进入锁等待这个时候你用查锁 SQL 去看发现没有锁对直接去查 OCEANBASE.GVO B _ L O C K S 发现没有 B L O C K 1 的行。这说明该事务重试没有进入锁管理器本身它这次的锁等待信息在 G V OB\_LOCKS 发现没有 BLOCK 1 的行。这说明该事务重试没有进入锁管理器本身它这次的锁等待信息在 GVOB_LOCKS发现没有BLOCK1的行。这说明该事务重试没有进入锁管理器本身它这次的锁等待信息在GVOB_LOCKS 中是查不到的。补充 2 点MYSQL租户 存储过程 autocommit 开启锁重试就会是占着线程的重试。以后这个规则会不会改我不知道如果你的实验失败也不要紧理解这个实验要表达的含义更重要如果事务 A 锁 BB 锁 C。B 占线程重试C 用的锁管理器那么 GV$OB_LOCKS 也能看到 B 的信息但是这个锁对是 C 为申请者B 为持有者。B 是申请者的锁对信息是看不到的。进入锁管理器重试和占线程重试的几点重要区别如下以下简称管理器和占线程管理器对于 CPU 更友好资源消耗少占线程相反占用 CPU 资源多。管理器管理的锁等待会话申请者信息会出现在 GV$OB_LOCKS 视图中占线程不会。管理器的重试策略更灵活可以更快发起重试策略参加官方文档而占线程只能按照普通的给定周期来重试。管理器重试的 SQL 在 GVO B _ S Q L _ A U D I T 中只会出现一次而占线程每次重试都会新写入 G V OB\_SQL\_AUDIT 中只会出现一次而占线程每次重试都会新写入 GVOB_SQL_AUDIT中只会出现一次而占线程每次重试都会新写入GVOB_SQL_AUDIT使得 GV$OB_SQL_AUDIT 信息激增。简单给个结论进入锁管理的重试是更好的情况不过你也需要知道还有占用线程的重试千万不要一查 GV$OB_LOCKS 里没有 BLOCK 1 的行就慌了。接下来我们来看几种稍复杂的情况更多探索一下锁的机制。三种相关的复杂情况3.1 多事务多重等锁要找到持有锁的源头构造方式-- 按顺序操作事务 -- 事务1 SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 事务2 SELECT * FROM TEST_LOCK WHERE ID 3 FOR UPDATE; SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 被事务1卡 -- 事务3 SELECT * FROM TEST_LOCK WHERE ID 3 FOR UPDATE; -- 被事务2卡查询方式WITH RECURSIVE ANCESTRY_PATH (START_CHILD,CURRENT_PARENT,GENERATIONS)AS( SELECT TRANS_ID, ID1, 1 FROM OCEANBASE.GV$OB_LOCKS WHERE TYPE TR AND BLOCK 1 UNION ALL SELECT AP.START_CHILD, PC.ID1, AP.GENERATIONS 1 FROM ANCESTRY_PATH AP JOIN OCEANBASE.GV$OB_LOCKS PC ON AP.CURRENT_PARENT PC.TRANS_ID AND TYPE TR AND BLOCK 1 ) SELECT AP.START_CHILD AS 等待事务ID, AP.CURRENT_PARENT AS 最终持锁事务ID, AP.GENERATIONS AS 迭代查找次数 FROM ANCESTRY_PATH AP WHERE AP.CURRENT_PARENT NOT IN( SELECT TRANS_ID FROM OCEANBASE.GV$OB_LOCKS WHERE TYPE TR AND BLOCK 1);看图图5不同的等待事务都指向了同一个持锁事务 ID那么只要使用上面的查锁 SQL 查询然后找到对应的事务 ID再使用 DO_SQL 就可以知道对应的上锁 SQL 了。3.2 持有锁的持续时间比申请锁的持续时间短的情况按常理先到先得先申请的没拿到凭什么后来的人先拿到构造方式-- 按顺序操作事务 -- 事务1 SELECT * FROM TEST_LOCK WHERE ID 3 FOR UPDATE; -- 事务2 SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 被事务1卡查锁 SQL图6这个时候事务2被卡在了 id3 上。-- 继续执行 -- 事务3 SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE;查锁 SQL图7现在事务 2 被卡在 id2 上正如标题中写的事务3是后申请锁的事务但是先得到了锁还把先申请锁的事务给卡住了。这个案例说明了2个点1事务 2 需要在行 ID 2、3、4、5 上获取行锁只要其中一个拿不到已经拿到的也会放弃不然事务3是无法获取 ID 2 的锁的。2每次重试获取锁的时候都是按照 2、3、4、5 的顺序一个个去获取的这样才会显示锁冲突在 ID 更小的2这行上。相信你可以体会到锁是按某个顺序一行一行申请一行一行获取的了。3.3 PL 的锁重试机制整块重试写这个例子时候我比较纠结因为 PL 的锁重试比较复杂要写清楚需要给到很多篇幅去铺垫前置知识点。我思考以后决定就留下一个最典型的例子你需要记住的就是 PL 的锁重试很复杂但大概率是 PL 块重头重试之前 PL 中已经执行的 SQL 会被回滚当然需要满足回滚条件。看下面的例子查看下表现在的情况图8构造方式-- 创建一个存过 DELIMITER $$ CREATE PROCEDURE JINCHUAN_TEST2() BEGIN DECLARE KA VARCHAR(200); SELECT VAL INTO KA FROM TEST_LOCK WHERE ID 3 FOR UPDATE; UPDATE TEST_LOCK SET VAL KA WHERE ID 4; SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; COMMIT; END$$ DELIMITER ; -- AUTO COMMIT 是 OFF 的状态【这个很重要】 SET autocommit OFF; -- 按顺序操作事务 -- 事务1 SELECT * FROM TEST_LOCK WHERE ID 2 FOR UPDATE; -- 事务2 SELECT * FROM TEST_LOCK WHERE ID 4 FOR UPDATE; CALL JINCHUAN_TEST2;这个时候事务 2 中存储过程 JINCHUAN_TEST2 里的 SELECT ID INTO KB FROM TEST_LOCK WHERE ID 2 FOR UPDATE; 这句 SQL 会被卡住。-- 继续执行 -- 事务3 UPDATE TEST_LOCK SET VAL kkk WHERE ID 3; COMMIT; -- 事务1 ROLLBACK;等事务全部执行完成后看下表最后的情况图9后面事务 3 可以执行 DML 成功就说明了 PL 里的 SELECT VAL INTO KA FROM TEST_LOCK WHERE ID 3 FOR UPDATE; 这句 SQL 在重试的时候是被回滚的否则就会锁住事务 3 的修改。简单的结论就是 PL 的执行还是需要注意尽量不要里面包含过长的事务长事务不但容易被锁而且还会被回滚很多的 SQLONE MORE THING写文章的过程中为了清晰地说明一些机制请教了好几个研发同学。我也深刻体会到数据库的复杂性因为一个主题可能涉及到好几个功能模块都是不同的研发各自负责的一个问题也需要几人一起才说的清楚。这时候我觉得距离业务一线最近的售后其实是很重要的能更好的把业务主题串起来发现数据库产品可以优化的地方从而帮助产品越做越好。最后把文章里 2 个查锁的工具 SQL 的 ORACLE 租户版本贴出来目的是为了方便更多使用 OceanBase 的人~-- oracle 租户版查锁 SQL SELECT K.R_ID 锁对ID, K3.ID 会话ID, CASE WHEN K.TRANS_ID K1.TRANS_ID THEN 申请锁 WHEN K.ID1 K1.TRANS_ID THEN 持有锁 END AS 操作, K1.TRANS_ID 事务ID, ROUND(K1.CTIME /1000000,2) 持续时间, K3.INFO, K3.TOP_INFO, K3.RETRY_INFO, K3.RETRY_CNT, K2.DATABASE_NAME , K2.TABLE_NAME , K.ID3_2 上锁行, SYSDATE TIME_NOW, (SYSTIMESTAMP - K1.CTIME / 86400000000 ) AS LOCK_TIME, CASE WHEN K1.ID1 K3.TRANS_ID THEN WITH KK AS (SELECT REQUEST_ID FROM GV$OB_SQL_AUDIT WHERE TX_ID || K1.ID1 || ) SELECT /* USE_NL(KK K) */ K.* FROM GV$OB_SQL_AUDIT K WHERE SVR_IP || K1.SVR_IP || AND SVR_PORT || K1.SVR_PORT || AND TENANT_ID || K1.TENANT_ID || AND REQUEST_ID IN (SELECT REQUEST_ID FROM KK) AND REQUEST_TIME IS NOT NULL AND UPPER(QUERY_SQL) LIKE % || K2.TABLE_NAME || %; END AS DO_SQL FROM (SELECT ROWNUM AS R_ID,K.TRANS_ID,K.ID1,K.ID3, SUBSTR(K.ID3,1,INSTR(K.ID3,-)-1) AS ID3_1, SUBSTR(K.ID3,INSTR(K.ID3,-)1)AS ID3_2 FROM GV$OB_LOCKS K WHERE K.TYPE TR AND K.BLOCK 1) K LEFT JOIN GV$OB_LOCKS K1 ON K.ID3 K1.ID3 LEFT JOIN DBA_OB_TABLE_LOCATIONS K2 ON K.ID3_1 K2.TABLET_ID AND ROLE LEADER LEFT JOIN GV$OB_PROCESSLIST K3 ON K1.TRANS_ID K3.TRANS_ID ORDER BY K.R_ID,K1.TRANS_ID; -- 递归找锁源头的 SQL WITH ANCESTRY_PATH (START_CHILD,CURRENT_PARENT,GENERATIONS)AS( SELECT TRANS_ID, ID1, 1 FROM GV$OB_LOCKS WHERE TYPE TR AND BLOCK 1 UNION ALL SELECT AP.START_CHILD, PC.ID1, AP.GENERATIONS 1 FROM ANCESTRY_PATH AP JOIN GV$OB_LOCKS PC ON AP.CURRENT_PARENT PC.TRANS_ID AND TYPE TR AND BLOCK 1 ) SELECT AP.START_CHILD AS 等待事务ID, AP.CURRENT_PARENT AS 最终持锁事务ID, AP.GENERATIONS AS 迭代查找次数 FROM ANCESTRY_PATH AP WHERE AP.CURRENT_PARENT NOT IN( SELECT TRANS_ID FROM GV$OB_LOCKS WHERE TYPE TR AND BLOCK 1);立即试用 OceanBase 企业版体验国产数据库能力180 天免费试用零门槛开通