行锁争用问题的分析方法

今天遇到两起“锁表”问题的技术支持，都是“药品库存”相关的，业务层面表现为发药操作卡死，或要等很久才完成，严重影响窗口业务的使用。

数据库层面表现为，等待事件'enq: TX - row lock contention'。

这类问题，如果只是从发生等待事件的会话当时执行的SQL去分析，很难查到问题原因，可能完全想不通为什么这么简单的一条SQL会卡死。

之前写过一篇文章，介绍了两种常见的“锁表”问题，下面分析今天遇到的两个案例中的一个，再次对行锁争用的问题进一步的说明，分享解决这类问题的方法。

当发生“锁表”问题后，可以通过查AWR或ASH来分析问题，一般来说，查ASH更方便一些，因为可以获取一个较短时间内（常用15分钟）的信息，快速定位问题。

如下图所示：

从中我们看到，在更新“病人余额”和“药品库存”时发生了行锁争用。

进一步，可以看到是如下两个存储过程的调用：

如果仅仅看这几条Update操作相关的SQL，该SQL很简单，难以理解为什么这么一条SQL会被阻塞。

遇到这类问题时，我们需要从原理来理解，从业务层面去分析。

1.更新一行数据后，如果不提交事务会发生什么问题？

其他并发会话，要更新相同的一行数据，就只有等待这个事务提交后才能执行完成。

2.有哪些情况会引起事务没有立即提交？

常见的有，更新一行数据后，存储过程中查询或处理别的数据，如果耗时过长，例如：大表全表扫描，耗时一分钟。那么之前在同一个过程中更新的那一行数据，在此期间就是一直锁住的。

还有比较常见的情况就是，调用存储过程的界面程序，没有及时提交事务，在事务中进行一些耗时的操作，例如：弹出提示等待操作人员处理，调用远程接口（常见的，例如：医保接接），执行打印操作等。

简单的说，茅坑有限，如果有人占倒茅坑不拉屎，其他人就只有等待。

所以，如果只是看“Update药品库存”这一条语句，很难理解到这个案例中，为什么“Select* From病人费用查询”这条语句的全表扫描会是导致这个问题的根本原因。

当出现这类问题时，首先从ASH或AWR中查，如果有耗时的SQL,特别是全表扫描的，先解决了再说，不要觉得跟当前行锁的语句完全无关就忽略了。

如果没有这类SQL，那就要想想应用程序方面的原因了，特别是一些第三方程序，或相关接口程序，往往会缺乏性能方面的考虑。

这个案例中的问题解决，最终定位到下面这条SQL:

这是一个第三方应用程序“zl_卫材自动打印.exe”中调用的SQL。

SQL中的问题就是对视图进行外连接，导致没有使用到预期的索引。

解决办法是，改写SQL中的视图“病人费用记录”,这是一个由“门诊费用记录”和“住院费用记录”进行Union查询而来的，改为直接用“基表”来与其他表时行连接，或者简单的加一个rule提示字来固定执行计划。

这条语句快了，更新“药品库存”的事务就会很快完成，就能够避免行锁争用。

当然，可能还有其他的原因，以上只是列举最常见的原因。

产生等待事件'enq: TX - row lock contention'，其他原因还包括：

1.并发会话，插入相同主键值的记录；

2.位图索引的并发更新。

3.Oracle的BUG等。

更多信息，可查看Oracle官网的一篇文章：