开发者社区> 技术小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

拨云见日—深入解析Oracle TX 行锁(上)

简介:
+关注继续查看

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

de3a84b24c7f47ed8844eb9e338dc17a92b36cf0


演讲实录

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

需求:指的是要做什么;

资源:是系统中所能提供的内容;

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

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

今天的内容将会通过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会变得很高。

228795cc6c6919f20c103cd715e7740536fcdc93

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

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

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

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

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

e22e175912b319a37300f9013290f2123c50bddf

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

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

da438245159035e734b9522cf7dcfe9e6ad5df63

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

bf2450eb60f850cbdbd5ee8361d95a90fb127ad1

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

原因是什么呢?

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

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

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

ae0f1992fd51eb87d43f2b965b698e9c6ea3cd91

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

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

5cd1ca28d9a9fe7d9e10dbc9fa70be795d2ac9a9

我们看到每一秒行锁争用的个数都达到了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看到,并不是一个唯一索引。

40a02311ed9b6c2d8d0efd088bdbd10427ac1c1b

相关的表和索引信息如下:

我们看到表有200w的记录,access_num为40w左右。因此平均每行的访问为6。

9a5c55759dc0c0619df4ad3d3befea9bb8e2f186

然后我们把SQL的AWR报告导出来一看,我们可以看到平均每次要处理7w多条记录,和6相比差别很大。这说明数据存在严重的倾斜。

cdc5a74ee30dbe5c71f8e7b4a1e34e5d4e11dc79

因此我们做了一个查询,结果如下:

4ae854ca8d58b99759bcbb932e6435f0b00bebb6

有些号码对应到十几万条记录,数据倾斜严重。

因此我们推测,发生故障是因为多会话在更新相同的access_number

深度分析:数据为什么会分布不均?

经过跟业务部沟通,发现:

每次用户申请凭证,表内就会记录一条凭证信息。

如果用户反复申请,表内对同一手机号就会记录多条信息。

问题为何产生?

一次就将表内一个手机号对应的所有记录的过期日期都更新为当前日期,是不合理的做法。

后来跟开发商进行沟通,得到以下结论:不是代码问题,就是设计问题

方案1:允许存在相同ACCESS_NUM对应多条记录的情况:正确的做法应该是只更新最新的记录,而早之前的记录不应该更新,因为其早已过期(过期日期比当前日期小)。
并将单表改为主子表关系,主表存最新的记录,子表存历史记录。

方案2:1个ACCESS_NUM在该表只应有一行记录的情况:应根据判断新进入该表的数据是否已经存在在表内,若是,则更新数据,若否,则插入数据。

因此开发商给出的方案:

880b08aaf8bad063ed3ca3194944192105c7f816

后续得知,该问题不是第一次出现,曾经*MANAGER就是指向表*MANAGER的同义词!!!

我们根据前面的观点判断,在本案例当中,平衡三要素中的“实现”出现了问题。是由于开发设计不合理导致的行锁竞争。


原文发布时间为:2017-12-1

本文作者:怀晓明

本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”微信公众号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle 数据库性能优化3日实战(企业培训)
课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述: 本课程讲述Oracle数据库物理层规划,系统性能的监控,数据库性能参数调整,统计信息的收集,使用自动化调试工具优化数据库,I/O子系统的配置与设计以及性能优化方法论等。
1890 0
oracle数据库 如何查询某个表的约束条件
使用下面语句从all_constraints视图中查看某表上的约束: SELECT constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints WHERE table_name = 'table_name' and owner = 'owner_name'; 另一个视图ALL_CONS_COLUMNS也包括组成表上约束列的信息。
736 0
配置ORACLE 客户端连接到数据库
       --================================= -- 配置ORACLE 客户端连接到数据库 --=================================       Oracle 客户端连接到Oracle 数据库服务器貌似不同于SQL serve中的网络配置,其实不然,只不过所有的SQL server 运行于Windows平台,故很多配置直接集成到了操作系统之中。
1040 0
系统关闭时候,oracle数据库执行动作
系统关闭时候,oracle数据库执行动作 先尝试shutdown immediate如果超时,则 shutdown abort
599 0
Oracle 数据库实例启动关闭过程
--================================ -->Oracle 数据库实例启动关闭过程 --================================ /*     Oracle数据库实例的启动,严格来说应该是实例的启动,数据库仅仅是在实例启动后进行装载。
993 0
在 Linux x86 上安装 Oracle 数据库 10g_1
验证您的安装 如果完成了以上步骤,您应该具备 Oracle 10g 所需的所有程序包和更新。您可以执行以下步骤验证您的安装。 所需内核版本:2.4.21-4.EL(这是 RHEL 3 的基本版本附带的内核版本。
994 0
通过JDBC操纵Oracle数据库LOB字段的分析
在Oracle中,LOB(Large Object,大型对象)类型的字段现在用得越来越多了。因为这种类型的字段,容量大(最多能容纳4GB的数据),且一个表中可以有多个这种类型的字段,很灵活,适用于数据量非常大的业务领域(如图象、档案等)。而LONG、LONG RAW等类型的字段,虽然存储容量也不小(可达2GB),但由于一个表中只能有一个这样类型的字段的限制,现在已很少使用了。  LOB类型分为
1083 0
+关注
技术小能手
云栖运营小编~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
ORACLE 10g 数据库体系结构图
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载