拨云见日—深入解析Oracle TX 行锁（上）

在刚刚过去不久的第七届数据技术嘉年华上，性能优化专家怀晓明老师进行了Oracle性能优化的主题分享。在他多年的优化生涯中，一直遵守的优化理念是，平衡是唯一的核心。我们整理了怀老师大会的演讲内容，今天一起来学习，如何在实践中应用这一理念并实现有效的性能优化。

cae3f23f59e837b6549703ee1e567f2d1dc1da98

演讲实录

优化的核心思想是平衡。在数据库的运行中，平衡取决于三个方面：

需求：指的是要做什么；

资源：是系统中所能提供的内容；

实现：指的是为了满足需求，应该如何利用提供的资源。

只有三者达到平衡，系统才能够高效地运行。

今天的内容将会通过Oracle 中一个很具体的等待事件 TX行锁来剖析数据库的平衡。

什么是TX行锁？

等待事件： enq: TX - row lock contention

enq代表的意思是enqueue,事实上代表的是入队这一个动作。

contention指的是争用，所以一般意义上的行锁，其实指的是行锁争用。

不管是在Oracle数据库还是其他关系型数据库，在修改一条记录的时候一定会产生行锁。其目的是为了保证数据的一致性，如果行锁长久不能得到释放，当其他进程想要使用的时候，就会产生争用。这种情况一般发生在先行的会话事务没有结束的时候。

TX行锁发生的常见场景：

1、当前会话要更新或删除的记录，已经被其他会话更新或删除。

2、对于表上有唯一索引的情况，多个会话插入或更新为相同的键值。

3、对于表上有位图索引的情况，多个会话即使更新不同记录，只要这些记录在位图索引上的键值相同，也会产生行锁。

一般我们可能认为在发生行锁的时候，几个SQL的语句是一样的，事实上这种理解是错误的。我举一个简单的例子：

首先在表上找到job为manager的记录，有三条：

select empno from emp where job='MANAGER';

--显示7566/7698/ 7782 三条记录。

之后在会话1 将部门ID为10的员工的记录删除掉

sess1：delete from emp where deptno=10;

-- 7782/7839/ 7934 三条记录被删除，但并未提交。其中7782的记录刚好是job为manager的。

接下来在session2做一个delete的操作，此时就会被hang住。

sess2：delete from empwherejob='MANAGER';

那么hang的情况说明时候会结束呢？

--if sess 1 rollback, 7566/ 7698/7782将被删除
--if sess 1 commit,  7566/ 7698将被删除

也就是说只有资源被释放，系统才会解除TX行锁。

TX行锁的危害：会导致其他会话的相关业务操作hang住

1、业务操作长时间无法完成

用户投诉

2、会导致会话积压

数据库连接池逐渐被占满

应用获取不到数据源无法创建新的数据库连接
或操作系统CPU、内存资源逐渐耗尽，无法创建新的数据库连接

3、会导致产生其他争用，如bufferbusy wait, ITL contention等

TX行锁的解决方案：

1、先行会话需要结束事务（transaction）：commit或者rollback

2、强制结束先行会话：kill session。

真实案例深入解析

来自双11的真实案例：双11早08:45，我方接到客户反映，在当天凌晨04:00~08:00，enq:TX - row lock contention等待严重。

当用户在投诉数据库严重的行锁问题的时候，我们首先会想到，在发生TX行锁时，由于资源久久得不到释放，系统中会话积压，导致DBtime会变得很高。

f29d620fe18b871b77e00057ef899ddb605acb80

从图上看出，在问题发生的前两条开始采样，DBtime一直处于相对较低的状态，大概是100。在故障点，DBtime超过了800。

问题初现：从11月11日约00:00开始

高峰时刻：11月11日凌晨04：00达到峰值。

高峰值：该时刻DBTimes峰值为835.86，是该节点平日压力的十几倍，可见问题十分严重。

接下来我们查看了当时的AWR的报告。在AWR报告里面，我们首先要关注的是等待事件。我们看到其中TX行锁占用了大部分的等待时间，因此初步推断行锁就是导致故障的原因。

077b84524b883e1af3126cd1d2d3d23c57d4e3ea

那么具体的行锁在什么地方呢？

通过top SQL查行锁的话，可能会比较困难。推荐大家通过segment部分进行查询。在segment模块，有一个专门针对行锁的统计， segment by row lock waits.从这个统计中我们看到，有一张命名为_manager_tp的表，占用了99%的行锁争用。

94e94e57cee8170b058d5c3048b13fe9b82cd2ae

明确了争用对象以后，我们再来找对应的SQL语句。

6e4c56196734f32b14213377c1ade65c474c318a

在以耗时排名的top SQL 中，有一条SQL占比达到98%，这条SQL语句正在对_manager这张表进行update操作。但是我们之前看到的那张争用的表是_manager_tp, 跟这里查出来的manager不是同一张表。

原因是什么呢？

后经查证，MANAGER是指向表*MANAGER_TP的同义词。我们知道在运营商的环境中，他们很喜欢用同义词指向一张表。

因此，现在确认在top SQL里面涉及的对象和争用的segment的对象是匹配的。

接下来我们看一下从ASH分析出来的00：00 到08:00的趋势：

716df12004e789afd8d2103446277bf375983f79

我们看到在整体的上升的趋势中，有一些点会产生向下的波动，向下的波动在行锁争用中是很常见的一种现象。当有一部分行锁被释放之后，被阻塞的量就会往下降。但是在持续的行锁阻塞中，虽然会有短暂的释放和缓解，但更多的会话会很快拥堵上来。

我们再对峰值期间的的行锁争用进行详细分析：

202a61e0f634765777a764ea6a9a3cc4a0971649

我们看到每一秒行锁争用的个数都达到了800+，只有在少数的时刻降到了几十。

因此到这一步，我们首先明确了分析方向：

该SQL自身导致的行锁，与其他无关

排除了存储不稳定可能导致SQL运行缓慢的可能性

第二条是因为在客户的机房环境下，经常会由于存储的不稳定导致应用SQL变慢。所以在故障发生的时候，也首先查看了操作系统的错误日志。

当我们确定了是某一条SQL导致的行锁，接下来我们对该SQL语句进行深入分析：

SQL全文如下：从SQL文本来看，对应到的应该是比较少的记录。

UPDATE *MANAGE
SET EXPIRE_DATE= SYSDATE
WHERE ACCESS_NUM = :1 
AND IDENT_CODE_LEVEL= :2 
AND IDENT_CODE_TYPE= :3

其执行计划如下：走的是索引范围扫。从TP_AN看到，并不是一个唯一索引。

cb63275a6aa9c3bd84e7d47c507cdfdd0024c5c4

相关文章：