深入内核:Oracle数据库里SELECT操作Hang解析

崔华,网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracle”社区投稿。

我们都知道在 Oracle 数据库里是“读不阻塞写,写不阻塞读”,那么是否可以认为在正常情况下,select 操作是怎样都能执行,始终不会被 hang 住的呢?注意这里提到的是正常情况下,不包括那些由于 latch 被 hold 住、或者 bug 等相关异常导致的 select 操作 hang 住的情况。

答案是:不可以这样认为的。

我们来举一个反例。

首先我们来分析一下在 sql 硬解析时在相关表对象上 library cache lock 的持有情况。这里我用到了10049事件,用10049事件,最重要的就是要知道如何设置它所对应的 level 值。

10049的level值可能会有如下一些组合:

这里因为我要跟踪 sql 硬解析时相关表对象的 library cache lock 的持有情况,所以这里level 值取0x0210=0x0200|0x0010,即这里 level 值取528。

SQL> select to_number(‘210′,’XXXX’)

from dual;

先在11.2.0.1里使用一下10049事件:

C:\Documents and Settings\cuihua>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 27 21:39:37 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> select count(*) from scott.emp; COUNT(*) ———- 14 SQL> oradebug tracefile_name c:\app\cuihua\diag\rdbms\cuihua112\ cuihua112\trace\cuihua112_ora_2292.trc

这个TRACE文件没有任何内容,看起来似乎是10049事件对11gR2无效或者 Oracle 改变了10049事件在11gR2中的 level 的定义(这个我不确定)。

我们换一个10gR2的版本:

SQL> select * from v$version;

SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> select count(*) from scott.emp; COUNT(*) ———- 13 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc

从上述 trace 文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc)中从前到后可以看到如下内容:

即针对上述 cursor 是以 NULL 模式持有 library cache lock,

针对表 scott.emp 是以 share 模式持有 library cache lock。

也就是说,只要我事先以 exclusive 模式在表 scott.emp上持有 library cache lock,那么后续的以硬解析方式执行的针对该表的所有sql(包括 select 语句)都将被 hang 住。

现在我们来测一下对一个表增加一个主键时的 library cache lock 的持有情况。

SQL> create table t2 as select * from emp; Table created SQL> select count(*) from t2; COUNT(*) ———- 13 SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> alter table scott.t2 add constraint PK_T2 primary key (EMPNO); 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc

从这个trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc)中我们可以看出对表t2的 library cache lock 的先后持有模式为:

即大部分时间 library cache lock 的持有模式都是N,只有在一头一尾的时候才是X。

但请注意这种情况下 select 操作是会被hang住的。

因为一开头的X是 kglget,结尾才 kgllkdl(kgllkdl大致是 kgl lock delete 的意思,表示释放相应的 library cache lock),并且它们的 KGL Lock addr 相同:

这也就意味着在添加主键的整个过程中,Oracle始终会以 exclusive 模式在表 scott.t2 上持有 library cache lock,直到最后主键添加完毕了才释放。

所以在 win32上的10.2.0.1中,在添加主键的过程中会一直阻塞查询(select)操作。

我们来测一下,同时开3个session。

Session 1:

SQL> create table t3(id number); Table created SQL> declare 2 i number; 3 begin 4 for i in 1..3000000 loop 5 insert into t3 values (i); 6 end loop i; 7 commit; 8 end; 9 / PL/SQL procedure successfully completed

Session 2:

SQL> select * from v$mystat where rownum<2;

在 session 1中开始执行添加主键操作:

Session 1: SQL> alter table scott.t3 add constraint PK_T3 primary key (id); ……开始执行

转到 session 2执行查询操作:

Session 2:

SQL> select * from t3 where rownum<10; ……这里 hang 住了

转到 session 3并执行对 session2的等待事件的查询:

Session 3:

SQL> select t.event,t.state,t.seconds_in_wait from v$session t where sid=138;

从中可以看到 session 2在等待 library cache lock,同时它的STATE为waiting,SECONDS_IN_WAIT的值在递增。

这就验证了我们的结论:在 win32上的10.2.0.1中,在对表增加主键的过程中会一直阻塞对这个表的查询(select)操作。

现在我们再问一个问题:是不是所有对表的DDL操作,在DDL操作的执行过程中都会阻塞对这个表的select操作?

答案是:不是这样的。

我们来举一个反例。

现在我们来测一下对表 drop一个column 时 library cache lock 的持有情况:

SQL> desc t1;

SQL> select count(*) from t1;

同时开两个session。

在session 1中打开10049事件后drop表t1的列object_type:

Session 1:

SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever,level 528 已处理的语句 SQL> alter table scott.t1 drop column OBJECT_TYPE; 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\ cuihua_ora_5020.trc

session 2在 session 1执行 drop column 操作的同时查询表t1,结果是 select 操作并没有被 hang 住,且能看到正在被 drop 的列 object_type:

Session 2:

SQL> select owner,object_name,object_type

from t1

where rownum<10;

从 session 1所产生的 trace 文件

(d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc)中我们可以看出对表t1的 library cache lock 的先后持有模式为:

即大部分时间对表 scott.t1 的 library cache lock 的持有模式都是S,最后才是X,所以这就可以解释为什么在对表 scott.t1 执行 drop column 操作的时候对它的select语句能够同时执行。

从 trace 文件来看,drop column 并不是不会阻塞 select 操作,只是阻塞的时间点要恰好是Oracle以X模式持有library cache lock时。

最后我们来测一下对一个表增加一个 unique constraint时library cache lock的持有情况

SQL> conn / as sysdba; 已连接。 SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10049 trace name context forever, level 528 已处理的语句 SQL> alter table scott.t2 add constraint UK_T2_EMPNO unique (EMPNO, ENAME); 表已更改。 SQL> oradebug tracefile_name d:\oracle\admin\cuihua\udump\cuihua_ora_5240.trc

从这个trace文件中我们可以看出对表 scott.t2 的 library cache lock 的先后持有模式为:

即大部分时间都是N,一头一尾才是X,这个和添加主键操作一样,在此不再赘述。

结论:不要随便在生产环境对大表执行DDL操作(如添加唯一性约束等),可能会导致针对这个表的所有 sql(包括select操作)在执行DDL操作的时间段都 hang 住。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-03-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

使用pt-table-checksum校验MySQL主从复制

pt-table-checksum是一个基于MySQL数据库主从架构在线数据一致性校验工具。其工作原理在主库上运行, 通过对同步的表在主从段执行checksum...

812
来自专栏idba

死锁案例之五

死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友...

774
来自专栏数据之美

MySQL 死锁与日志二三事

最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现。甚至有些异常还比较诡异,最后 root...

2766
来自专栏MYSQL轻松学

MySQL InnoDB Lock(二)

MySQL InnoDB Lock主要从5个部分介绍,这篇文章承接 上一篇 ,会详细介绍后3部分。 ---- 数据库数据一致性 InnoDB事物一致级别 Inn...

3157
来自专栏java达人

关于mysql锁的两个例子

版本:mysql5.5.52 存储引擎:InnoDB 隔离级别:READ-COMMITTED 示例一: 事务1:左图 事务2:右图 1、 ? 事务...

1938
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

3469
来自专栏北京马哥教育

MySQL数据库事务隔离级别

数据库隔离级别有四种,应用《高性能mysql》一书中的说明: ? ? 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上...

3216
来自专栏乐沙弥的世界

MySQL 慢查询日志(Slow Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

422
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1082
来自专栏恰同学骚年

Microsoft SQL Server中的事务与并发详解

  事务是数据库并发控制的基本单位,一条或者一组语句要么全部成功,对数据库中的某些数据成功修改; 要么全部不成功,数据库中的数据还原到这些语句执行之前的样子。

803

扫码关注云+社区