优化的核心思想：Balance is the ONLY key to Optimizer.
前文中我们详细介绍了TX行锁的概念，危害以及应对方案，并通过双11的一个经典案例进行了解读。今天我们分享另外一个跟TX行锁有关的案例。
案例描述
技术层面：
1、2017年某天，从当天大约10:30开始，A库上出现持续不断的严重行锁。
2、最初应用开发方的处理方式是不断杀会话（且并未通知我方），然而锁的问题一直持续到晚上还存在。
业务层面：
1、选号开户业务受到严重影响，成功开户的业务量仅为平常时刻的30%都不到。
2、业务群各地州市均有反馈该业务处理慢。
首先我们选取故障发生前1小时到处理前最近一个时段的AWR报告进行分析。（因为故障一直存在，故可以导出长时段的报告以查看整体情况）
首先我们看到，在采样时间长达10多个小时的时间里，TX行锁占到了68.7%，比例是非常高的。
我方分析问题是从20：00开始的，因此我们再次看了这段时间的等待事件。发现TX行锁的占比更高。说明我们接手处理的时候问题已经越来越严重。
根据前面的思路，我们同样首先确认TX行锁发生在哪些对象上面。
从AWR报告中可以看到，在三个对象上面的行锁比较严重。
选号开户业务的流程
当用户打开一个预选界面之后，系统会显示若干个号码，例如10个，如果对显示出来的号码不满意的话，可以刷新再换。在预选环节（展示10个号码的这个过程叫做预选）中，当前页面显示的号码被正在操作的用户所占用，这个占用时间一般是几分钟（在业务上是有一定的时间控制的），如果在这几分钟之内，你选择好了一个中意的号码，就进入到预占环节，并转向开户环节。如果你在限定的时间内完成开户，那么你选中的中意号码就归属你所有了。
因此，实际流程中，从预占开始到完成开户之间，是需要一定的时间的。
我们看到在TX行锁发生的第一张表上，这张表叫做 RES_NUM_ORIGIN，指的是号码的资源池，可选的号码都可以通过一定的条件从资源池中查询出来，第二个对象是号码预占历史表的主键，当用户预占一个号码之后，会把预占的信息存放在号码临时预占表上，这样当下一个用户来选号的时候，就不会选择到前面用户预占到的号码。存放号码预占信息一方面是为了避免用户选号的冲突，一方面是为了提供业务回查的依据。
第三个对象是在历史表上预占号码的索引。
我们看到第一个对象上的行锁占到50%以上，第二个占到30%左右，都是比较严重的。根据TX行锁的原理，我们知道，当在主键上产生行锁的时候，大部分都是发生在insert的情况下，也就是多个会话插入了相同的值。
那这个时候我们就会想， 这些争用是怎么发生的？
首先我们来看发生争用的SQL语句
在执行比较慢的SQL里面，我们看到有一些update和insert的语句。
具体如下：
三条为update，一条为insert
ACCESS_NUMBER为表RES_NUM_ORIGIN的主键
OCCUPY_ID为表RES_TEMPOCCUPY的主键
OCCUPY_ID亦为表RES_TEMPOCCUPY_HIS的主键
update均是根据主键进行更新，以资源池中的具体号码作为主键，为何出现行锁？
insert到RES_TEMPOCCUPY_HIS表，为何出现行锁？
原理分析
对于update：唯一的可能就是多个会话在更新相同的主键值，并且同一事务中包含执行时间长的SQL 语句。
对于insert：唯一的可能就是多个会话在插入相同的主键值，并且同一事务中包含执行时间长的SQL 语句。
由于TX行锁的成因和事务中单次执行时间长的SQL有关，所以我们要对AWR中的Top SQL，与业务开发方一道进行排查。
我们首先结合逻辑读进行分析
SQL_ID：ap50cvq66uqch是经确认需要优化的SQL，是同一事务中的SQL。
SQL文本如下：
这条SQL并不复杂，就是从一张表去除一条记录，跟另外一张表做了反连接的操作，not exists 是反的半连接操作。
该SQL的执行计划如下：
我们看到出现了两个执行计划，但路径都不是最优。索引跳扫一般效果都不是很好，尤其是当前导列的值比较多的时候。
我们来看条件中涉及到的两个列
OCCUPY_ORG_ID列上distinct的值是7000多，OCCUPY_TYPE_CODE列则是7，两个相乘的话，最多是49000最少也有7000的唯一值，如果我们把这两个列做一个索引，是不是会有一个比较好的效果呢？
在索引上我们发现刚才走索引跳扫的是倒数第二个索引，存在两个列，分别是valid_time和occupy_org_id。
其中valid_time存在23000+的唯一值。在这种情况下走索引跳扫的效率是非常低的。
由于在RES_TEMPOCCUPY上没有最合适该SQL的索引，因此我们先创建了对应的索引。
优化方案：两列上创建复合索引
顺序：OCCUPY_ORG_ID, OCCUPY_TYPE_CODE
优化效果：
耗时：800多毫秒->0.49毫秒
逻辑读： 12万->7
这样处理之后，看起来效果是不错的。但由于当时已经几乎没有业务了，所以不知道具体实施效果。
到了第二天继续来查看：
发现依然存在TX行锁，只是说比第一天的情况有所好转。也就是说，通过创建索引虽然解决了性能上的一些瓶颈，但并不是导致问题发生的根本原因。
根据AWR报告来看，第二天行锁SQL与昨日一样，虽然执行耗时减半，但我们发现DML类SQL执行的时间仍然有超过1秒的，根据主键更新一条记录，更新的字段不多，表本身也不算大，需要花1秒的时间，依旧是不可接受的(>10ms)。哪怕是在存储性能很差的情况下，几百毫秒也是很高的了。
于是继续跟开发商进行确认沟通：
对于第一天找到的SQL语句已经进行了优化，效果也很好。但是为什么还有行锁存在呢？我们希望跟开发商确定，是不是库上面还有来自其他库的SQL。 这里有一个背景信息，电信行业的客户，经过多年演进，CRM系统架构发生变更，将原来是一个库拆成了多个逻辑上独立的库，目的是为了减少热点上的竞争。这样的做法在行业中很常见。因此，我们很关心，这个事务里面会不会执行其他库里面的SQL语句。
开发商很笃定的告诉我们：没有，选号的业务只跟当前的A库有关系。
如果是这样， 那么我们就怀疑选号的SQL可能有问题。因为如果选号逻辑做得不够随机，那么两个用户就有可能选到重复的号。而且因为其是一次选10个号，只要有一个号是重复的，就可能导致TX行锁产生。
所以我们需要审查是不是选号的SQL有问题。下图是选号SQL的关键部分：
在该选号的SQL语句中，从资源池中取号，选择了sample的20%，相当于是说从所有可用的号码中随机抽取20%作为用户可选的范围。 20%的量是比较大的，但跟其他表关联之后，得到的结果会相对减少。但从选号的逻辑上来讲，是不合理的。很可能会导致用户在选号的时候每次挑出的可选项的数量不一致。
因此我们对SQL做了以下改写，使得在逻辑上正确。
改写后的SQL即时发布。
同时，开发商接报障，在B库上有一SQL物理读很高，于是在B库上紧急创建了一个索引，B库问题SQL运行速度提升。
至此，行锁争用问题得到进一步缓解，但是行锁问题依旧存在。
因为从业务的角度来看，虽然预选号业务表现趋向正常，但仍然比往常要慢一些。
故障分析至此，我们提出以下疑问：
1、改写SQL和在B库创建的索引，哪个起到了更关键的作用？
2、为何到现在业务的表现还没有完全恢复往常的状态，到底哪里还有问题？
我们对比了A库和B库的故障前后同时段的AWR报告，发现：在B库的参数对比上，存在一个比较严重的问题。Buffer Cache和Large pool的大小相当于是做了对调。Buffer Cache减少了24G。160M的大小会导致很多数据不能保存在内存里，需要反复从磁盘读取。
到这里我们联想到系统在故障前一日做过整个的系统版本升级打补丁操作。都打上了2017年4月的PSU，并将所有实例都重启。通过对比重启前后的参数变化，只有B库遭遇了关键参数变动的问题。
这样看起来就比较清晰了，但我们还看到新的问题：与B库承担同样作用的C库（只是地州市不同的区别），表现正常，但为何业务部门也说慢？
经核实，实际上只有一部分地州市业务受影响（均是B库负责的地州市），其他地州市并未受影响。
处理措施：
1、后来我们通知代维部门，需要调整B库的参数为重启前的值。
2、对比B库DBTimes：
正常情况在20以内（根据往常的监控评估）
db_cache_size参数为160MB且新建应急索引之前，DBTimes为90左右。
db_cache_size参数为160MB且新建应急索引之后，DBTimes为50左右。
db_cache_size参数恢复24G之后，DBTimes恢复到正常情况，保持在20以内。
我们看到业务在新建索引之后并没有完全恢复正常。当调整完参数之后，DBtime才恢复到正常的状态。因此整个过程中，调整B库参数才是唯一核心的操作。
案例小结
1、同库上同一事务中的慢SQL，影响并不大。
2、改写选号SQL，解决的主要是选号冲突问题和逻辑正确性问题，影响也不大。
3、B库创建索引，进一步缓解了行锁争用问题，但仍未解决根本问题。
4、数据库关键参数调整为原来的值，是解决问题的关键。
平衡三要素中，“资源”出现了问题。
B库关键参数被修改为不合理的值，导致A库产生严重行锁争用。
总结
导致TX行锁争用的典型情况
1、DML较慢，由自身产生TX行锁争用：
DML需要操作大量数据
不合理情况：如案例1，更新了不应更新的数据
DML不够优化，运行慢
2、DML很快，但同一事务中存在其他慢环节，导致出现TX行锁争用
同库慢SQL
其他库慢SQL
调用了其他慢模块或服务
3、由其他等待事件引起的TX行锁争用
log file sync慢
在DML commit之前，有大的事务提交
存储本身不给力
拓展思考
如果会话中存在慢SQL或慢服务，但此时没有行锁争用或其他争用：
A.会导致连接池逐渐被撑满的情况
B.不会导致连接池逐渐被撑满的情况
针对这两种场景，你有什么问题和想法，或者是对文章内容的疑问、感悟都可以留言告诉我们，留言点赞数超30的前3名将获得精美图书一本。截止时间：2017.12.5 12：00。
资源下载
关注公众号：数据和云（OraNews）回复关键字获取
‘2017DTC’，2017DTC大会PPT
‘DBALIFE’，“DBA的一天”海报
‘DBA04’，DBA手记4经典篇章电子书
‘INTERNALS’，Oracle RAC PPT
‘122ARCH’，Oracle
12.2体系结构图
‘2017OOW’，Oracle
OpenWorld资料
‘PRELECTION’，大讲堂讲师课程资料