Oracle锁概念与常见锁分析

1. 概述

本篇只注重相关原理研究,不涉及诊断案例。
Oracle的锁(lock)机制用于管理对共享资源的并发访问,注意,是“共享资源”而不是“数据库行”。当然Oracle会在行级对表数据锁定,这固然不错,不过Oracle也会在其他多个级别上使用锁,从而对多种不同的资源提供并发访问。例如,执行一个存储过程时,过程本身会以某种模式锁定,以允许其他用户执行这个过程,但是不允许另外的用户以任何方式修改这个过程。数据库中使用锁是为了支持对共享资源进行并发访问,与此同时还能提供数据完整性和一致性。

2. 锁的相关知识

经常把锁分为3大类:
1、 DML锁(Data Manipulation Locks) – 用来保护数据的完整性和一致性
2、 DDL锁(Data Dictionary Locks) – 用来保护数据对象的结构,如table,index的定义
3、 系统级锁(Latch、mutex、library cache pin/lock)- 用来保护数据库内部结构,如sga内存结构

2.1. Oracle锁视图

Oracle中ddl锁和dml锁的视图有:dba_ddl_locks、dba_dml_locks
其中dba_ddl_locks的视图定义如下:

create or replace view dba_ddl_locks as
select  ……
   from v$session s, x$kglob ob, x$kgllk lk
   where lk.kgllkhdl = ob.kglhdadr
   and   lk.kgllkuse = s.saddr
   and   ob.kglhdnsp != 0;

而dba_dml_locks的视图定义如下:

create or replace view dba_dml_locks as
select ……
from (select l.laddr addr, l.kaddr kaddr,  /* 1040651: Defn for v$lock */
                   s.ksusenum sid, r.ksqrsidt type, r.ksqrsid1 id1,
                   r.ksqrsid2 id2, l.lmode lmode, l.request request,
                   l.ctime ctime, l.block block
              from v$_lock l, x$ksuse s, x$ksqrs r
              where l.saddr = s.addr and l.raddr = r.addr and
                    s.inst_id = USERENV('Instance')) l, obj$ o, user$ u
      where l.id1 = o.obj#
      and   o.owner# = u.user#
      and   l.type = 'TM';

所以,在Oracle中,DDL锁其实应该是与x$kgllk有关的library cache方面的锁(library cache lock),而DML锁是指enqueue上的TM锁,$ksqrs是指kernel service enqueue resource。而enqueue中的其它类型,既不属于DDL锁与不属于DML锁,我们就称之为系统级enqueue。所以我们讨论DML锁,其实就是讨论TM enqueue。而讨论DDL锁,实际应该是讨论library cache方面的内容。

2.2. Enqueue概念

enqueue是共享内存结构,用于串行化访问数据库资源。enqueue又分为用户级enqueue和系统级enqueue。
用户类型的enqueue有:

  • TM – DML (Table Manipulation)
  • TX – Transaction Enqueue used to protect transaction information.
  • UL – User Lock Enqueue used when an application makes use of the DBMS_LOCK package.

系统类型的enqueue有:

  • BF enqueue
  • CF Controlfile Enqueue
  • DX Distributed Transaction Enqueue
  • FB Block Format Enqueue
  • HW High Water Enqueue
  • JS Job Scheduler Enqueue
  • RO Fast Object Reuse Enqueue
  • SQ Sequence Cache Enqueue
  • ST Space Transaction Enqueue
  • TO Temporary Table Object Enqueue
  • TT Tablespace Operations Enqueue
  • US Undo Segment Enqueue

2.3. Library Cache概念

Library cache是Shared pool的一部分,它几乎是Oracle内存结构中最复杂的一部分,主要存放shared curosr(SQL)和PLSQL对象(function,procedure,trigger)的信息,以及这些对象所依赖的table,index,view等对象的信息。
Library Cache的结构如下所示:

1

Library Cache中保存的对象,可以查看v$db_object_cache视图。从v$db_object_cache视图的定义可以看出,在该视图上有library cache object对象上存在多少个pin和lock,但是具体是哪个会话加了pin或lock,在该视图上没有表现。

SQL> desc V$DB_OBJECT_CACHE
Name               Type           Nullable Default Comments
------------------ -------------- -------- ------- --------
OWNER              VARCHAR2(64)   Y
NAME               VARCHAR2(1000) Y
……
LOCKS              NUMBER         Y
PINS               NUMBER         Y
……
LOCK_MODE          VARCHAR2(9)    Y
PIN_MODE           VARCHAR2(9)    Y
……
LOCKED_TOTAL       NUMBER         Y
PINNED_TOTAL       NUMBER         Y
……

如果需要查询对library cache object加pin或lock的会话,需要通过x$kglob,x$kgllk或x$kglpn来确认。当然,也可以通过dba_kgllock或dba_ddllock,这两个视图有一些区别,需要注意,具体请自行查看。

2.4. 锁等级

在enqueue、library cache和latch中都有锁的等级概念,但我们一般只讨论enqueue中的锁等级概念,latch和library cache中锁等级涉及的方面较少。
x$kgllk中锁等级的定义:在kgllkmod和kgllkreq字段上定义了持有和请求x$kgllk资源的锁级别,其中锁级别如下: 0, ‘None’, 1, ‘Null’, 2, ‘Share’, 3, ‘Exclusive’,这个可以通过dba_ddl_locks视图的定义可以观察到。
而enqueue中的锁等级可以参考:VIEW: “V$LOCK” Reference Note [ID 29787.1]

  • 0 – none
  • 1 – null (NULL)
  • 2 – row-S (SS)
  • 3 – row-X (SX)
  • 4 – share (S)
  • 5 – S/Row-X (SSX)
  • 6 – exclusive (X)

2.5. 锁冲突

如果会话请求相关锁的锁级别,和其它会话持有该锁的锁级别有冲突,那申请锁的会话会被挂住。总体原则如下:如果申请的锁级别小于持有的锁级别,那申请会话会被挂住等待。
如:某个会话持有4级TM锁(v$lock中LMODE=4),而某个会话想以3级(v$lock中REQUEST=3)来请求TM锁,那请求会话是得不到TM锁的,这就是锁冲突,除非持有4级TM锁的会话释放TM锁。
关于锁冲突的情况,需要关注和研究的一般是enqueue锁,具体可以参考:VIEW: “V$LOCK” Reference Note [ID 29787.1]

2

而其实Library Cache中也是存在锁冲突,这从v$db_object_cache的定义中可以看出来,只是Library Cache锁的级别较少,只有3级。

select inst_id,
       kglnaown,
       ......
       decode(kglhdlmd,
              0,              'NONE',
	      	  1,              'NULL',
	      	  2,              'SHARED',
	      	  3,              'EXCLUSIVE',
              'UNKOWN'),
       decode(kglhdpmd,
              0,              'NONE',
              1,              'NULL',
              2,              'SHARED',
              3,              'EXCLUSIVE',
              'UNKOWN'),
       decode(kglobsta,
              1,              'VALID',
              2,              'VALID_AUTH_ERROR',
              3,              'VALID_COMPILE_ERROR',
              4,              'VALID_UNAUTH',
              5,              'INVALID_UNAUTH',
              6,              'INVALID',
              'UNKOWN'),
......
  from x$kglob
 where kglnaobj is not null。

enqueue锁冲突的表现为enq类型的等待事件,而实际上锁的详细情况(如持有者,阻塞者或者其它情况)是在v$lock表中。而很多enq类型的等待事件,其中p1、p2、p3列的信息就是v$lock中的id1和id2。当然其它的enq类型的等待事件的p1、p2、p3,其实也是v$lock的id1和id2的反映。
具体检查等待事件p1、p2、p3字段的含义,可以查询v$event_name。而查看v$lock的id1、id2的含义,可以查询v$lock_type。
而在libarary cache上的锁冲突的表现为:library cache %类型、cursor:%类型的等待事件,其中library cache类型的等待事件是library cache lock、library cache pin相关的锁,而cursor:%类型的等待事件对应的是相关的mutex。

3. Enqueue锁

3.1. 知识点

enqueue是共享内存结构(锁),用于串行化访问数据库资源。参考如下:
WAITEVENT: “enqueue” Reference Note (文档 ID 34566.1)
VIEW: “V$LOCK” Reference Note (文档 ID 29787.1)
在很多trc中,我们会看到enqueue的表现格式为:”TYPE-ID1-ID2″,如:

*** 2015-10-15 11:15:28.960
ksqgtl <strong>* TM-0001598e-00000000 mode=4 flags=0x401 timeout=0 *</strong>
ksqgtl: xcb=0x14b7203f8, ktcdix=2147483647, topxcb=0x14b7203f8

在Oracle 9i的等待事件中,没有把enqueue进行细分,所以如果需要在9i中具体判断是什么enqueue锁,需要通过v$session_wait中enqueue等待事件的P1进行计算。转换方法可以参考:34566.1

  SELECT sid, chr(to_char(bitand(p1,-16777216))/16777215)||
         chr(to_char(bitand(p1, 16711680))/65535) "Lock",
         to_char( bitand(p1, 65535) )    "Mode", p1, p2, p3
    FROM v$session_wait
   WHERE event = 'enqueue';

其实转换的原理很简单,通过查询v$event_name可以得到解释:

SQL> select name,PARAMETER1, PARAMETER2, PARAMETER3 from v$event_name e where e.NAME = 'enqueue';
NAME       PARAMETER1  PARAMETER2  PARAMETER3
---------- ----------- ----------- -----------
enqueue    name|mode   id1         id2

转换的方法是v$session_wait查到的行,把P1RAW进行拆分,如64位系统中,高8位是锁名称,而低8位是请求的锁模式。具体示例如下:

select sid,seq#,event,p1raw from v$session_wait
where event in ('enqueue')
order by p1;
       SID       SEQ# EVENT                  P1RAW
---------- ---------- ---------------- -----------
       675       7450 enqueue             48570006

P1RAW是十六进制数,其中4857是锁名称,转成ASCII码为:

SQL> SELECT utl_raw.cast_to_varchar2(replace('4857',' ')) value
     FROM dual;
VALUE
------------
HW

所以会话请求HW的排它锁(REQUEST=6,一般发生锁等待事件,都是请求者,所以这里是REQUEST)发生等待。具体什么情况会发生HW的排它锁等待,本篇内容暂不展开讨论。
在Oracle 10g里对enqueue进行了细分,而10g里已经没有enqueue这个等待事件,而是拆分成各个详细的“enq: ”类型的等待事件。在10g中’enq: %’类型的等待事件有220多个。

SQL> select * from v$event_name e where e.NAME like 'enq: TX%';
EVENT#   EVENT_ID NAME                          PARAMETER1 PARAMETER2     PARAMETER3 WAIT_CLASS
------ ---------- ----------------------------- ---------- -------------- ---------- --------------
   188  310662678 enq: TX - row lock contention name|mode  usn<<16 | slot sequence   Application
   189  281768874 enq: TX - allocate ITL entry  name|mode  usn<<16 | slot sequence   Configuration
   190 1035026728 enq: TX - index contention    name|mode  usn<<16 | slot sequence   Concurrency
   592 1629782133 enq: TX - contention          name|mode  usn<<16 | slot sequence   Other

3.2. Event

对于想要诊断一个会话在执行过程中需要获取什么锁,可能通过设置10704 Event进行跟踪。

$ oerr ora 10704
10704, 00000, "Print out information about what enqueues are being obtained"
// *Cause:  When enabled, prints out arguments to calls to ksqcmi and
//          ksqlrl and the return values.
// *Action: Level indicates details:
//   Level: 1-4: print out basic info for ksqlrl, ksqcmi
//          5-9: also print out stuff in callbacks:  ksqlac, ksqlop
//          10+: also print out time for each line

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.
SQL> lock  table t1 in share mode;
Table(s) Locked.
SQL> oradebug tracefile_name;
/oracle/diag/rdbms/test3/test3/trace/test3_ora_37916.trc
lock table获取的enqueue锁如下:
*** 2015-10-19 14:55:41.182
ksqgtl <strong>* CU-4e845ff8-00000001 mode=6 flags=0x10000 timeout=300 *</strong>
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-001E-000030CF

*** 2015-10-19 14:55:41.182
*** ksudidTrace: ksqgtl
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-001E-000030CF
ksqgtl: RETURNS 0

*** 2015-10-19 14:55:41.192
ksqrcl: CU,4e845ff8,1
ksqrcl: returns 0

*** 2015-10-19 14:55:41.193
ksqgtl <strong>* TM-000159cc-00000000 mode=4 flags=0x401 timeout=21474836 *</strong>
ksqgtl: xcb=0x14b6f8c20, ktcdix=2147483647, topxcb=0x14b6f8c20
        ktcipt(topxcb)=0x0

*** 2015-10-19 14:55:41.193
ksucti: init txn DID from session DID
ksqgtl:
        ksqlkdid: 0001-001E-000030CF

*** 2015-10-19 14:55:41.193
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-001E-000030CF
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-001E-000030CF
ksqgtl: RETURNS 0

3.3. TX

TX锁的具体概念,可以参考:TX Lock “Transaction Enqueue” (文档 ID 197057.1)。
TX锁是事务enqueue锁,会话持有了TX锁,就表明会话做了事务(如:INSERT, UPDATE, DELETE……),TX锁一般在COMMIT和ROLLBACK后释放。TX锁主要作为排队机制,其它会话修改相同数据时,必须要等到本事务结束。当某个事务的开始时间小于某个查询的开始时间时,TX锁也用于构建数据的一致性读的前景象。
TX锁的ID1和ID2字段说明:

SQL> select type, name, id1_tag, id2_tag from v$lock_type l where l.TYPE = 'TX';
TYPE  NAME          ID1_TAG        ID2_TAG
----- ------------- -------------- ---------
TX    Transaction   usn<<16 | slot sequence 

TX锁的ID1的高4位就是USN,ID2的低4位是slot,验证如下:

 SQL> update t1 set name = 'a' where id = 1517;
1 row updated.
SQL> select s.sid,
       t.XIDUSN,
       t.XIDSLOT,
       t.XIDSQN,
       l.TYPE,
       trunc(l.ID1/power(2,16)) as id1_usn,
       bitand(l.ID1,to_number('ffff','xxxx')) as id1_slot,
       l.ID2,
       l.LMODE,
       l.REQUEST
  from v$lock l, v$transaction t, v$session s
 where s.SADDR = t.SES_ADDR
   and s.SID = l.SID
   and l.type = 'TX';

SID XIDUSN XIDSLOT XIDSQN TYPE ID1_USN ID1_SLOT  ID2 LMODE REQUEST
---- ------ ------- ------ ---- ------- -------- ---- ----- -------
 228     10       3   8393 TX        10        3 8393     6       0

TX锁的ID1与ID2与v$transaction上的信息一致,其中USN、SLOT、SQN三个字段组成XID(Transaction ID)。

SQL> select dbms_rowid.rowid_relative_fno(rowid) as fno,
            dbms_rowid.rowid_block_number(rowid) as bno
       from t1 where id = 1517;
       FNO        BNO
---------- ----------
         1      94716

转储block后,ITL信息如下:

Object id on Block? Y
 seg/obj: 0x159cc  csc: 0x00.3863ba  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.000020c9  0x00c00120.0d7f.07  ----    1  fsc 0x0003.00000000
0x02   0x0003.017.00000580  0x00c03d49.03d0.09  C---    0  scn 0x0000.00365455
  • USN为回滚段号
  • SLOT为事务槽号
  • SQN为序号(该值暂时不解释,请参考相关文档)
SQL> select s.segment_name from dba_rollback_segs s where s.segment_id = 10;
SEGMENT_NAME
------------------------------
_SYSSMU10_1197734989$
SQL> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
System altered.

转储回滚段头后,检查相应的槽位信息如下,state=10表明该槽位上有个活跃事务:

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x20c3  0x0011  0x0000.0038613a  0x00c000da  0x0000.000.00000000  0x00000001   0x00000000  1445238136
   0x01    9    0x00  0x20be  0x0009  0x0000.00386131  0x00c000da  0x0000.000.00000000  0x00000001   0x00000000  1445238136
   0x02    9    0x00  0x20c0  0x000d  0x0000.00386280  0x00c000df  0x0000.000.00000000  0x00000001   0x00000000  1445238982
   0x03   10    0x80  0x20c9  0x0003  0x0000.00000000  0x00c00120  0x0000.000.00000000  0x00000001   0x00000000  0
   0x04    9    0x00  0x20c0  0x000f  0x0000.00386129  0x00c000da  0x0000.000.00000000  0x00000001   0x00000000  1445238136

所以TX锁的ID1和ID2就指到了回滚段的事务槽。每次开始新事务,都需要先分配事务槽,被修改数据块上的ITL的XID信息也指向了相应的事务槽。如果另一个事务修改相同数据块的相同行,发现ITL指向了同一个事务槽,那后来进行的会话就会挂起,等待TX锁。

3.4. TM

TM锁比较简单,事务执行INSERT,UPDATE,DELETE,MERGE,SELECT FOR UPDATE,或LOCK TABLE等SQL语句修改一个表时,需要获取’TM’(Table Manipulation)DML队列。持有TM锁,表明事务对相关表有DML操作的权力,并且防止DDL操作与本事务的冲突。假如已经有会话在对表进行DDL操作,如果有其它会话要修改这张表的数据,就首先要申请TM锁,而这时申请TM锁会被挂住,等待事件就是enq: TM – contention。

3.5. 特殊的锁

3.5.1. DFS lock handle等待

一个会话的等待事件为DFS lock handle,表明这个会话在等待一个全局锁请求的锁句柄。在Oracle RAC中,一些对象级别的State Object会演化为全局锁,如Drop Table,Truncate Table等引起的Table对象变化,另外RAC中sequence创建为ordered类型,那当访问这个sequence时,需要给节点分配全局锁,当访问sequence并发较高时,也会导致DFS lock handle。

SQL> select * from v$event_name e where e.NAME = 'DFS lock handle';
    EVENT#   EVENT_ID NAME            PARAMETER1 PARAMETER2 PARAMETER3
---------- ---------- --------------- ---------- ---------- ----------
       360 3595075359 DFS lock handle type|mode  id1        id2

观察DFS lock handle等待事件的定义,其中p1是锁的类型和模式,p2、p3是id1和id2,其实这个等待事件也是与v$lock相关。
例如:以下是DFS lock handle等待信息

SID  SEQ# EVENT                   P1     P2 P3
--- ----- --------------- ---------- ------ --
767 24410 DFS lock handle 1398145029 696629  0

查看锁的信息如下:

SELECT chr(to_char(bitand(1398145029,-16777216))/16777215)||
       chr(to_char(bitand(1398145029, 16711680))/65535) "Lock",
       to_char( bitand(1398145029, 65535) )  "Mode"
  FROM dual;

Lock Mode
---- -----
SV   5

这是SV锁是与sequence相关的锁,而p2=696629是这个sequence的object_id。

4. Library cache锁

4.1. 概述

library cache lock和library cache pin是与library cache相关的等待事件,library cache锁相关的信息的在x$kgllk和x$kglpn表中。library cache锁既不是enqueue(library cache机制与enqueue很相似,可以称之为KGL enqueue),也不是latch,只是为了保护library cache object对象而存在这种类型的锁,从Oracle 10gR2开始,Oracle使用mutex来替换掉一部分library cache锁。习惯上把library cache lock和library cache pin叫做Library cache锁,而实际上这是等待事件,产生等待事件的原因就是因为library cache的lock锁和pin锁。
所有在Library cache中的对象,都由两部分组成,一个句柄、至少一个子堆。句柄中记录的有对象的名字、命名空间、Lock的持有者和等待者、Pin的持有者和等待者、一些标志信息,最重要的,句柄中有堆的地址。在Library cache中寻找对象时,先计算HASH值,在HASH表中找到句柄,再经由句柄,找到对象实际的内存地址。在这个过程中,有两个重要的数据项需要被锁保护起来。一个是对象句柄、另一个就是对象的内存堆。请求对象句柄上的锁就是Library cache lock,请求内存堆上的锁,就是Library cache pin。

4.2. library cache lock

library cache lock锁用于在library cache中定位一个library cache object(定位一个LCO只需要扫描Library cache handle就可以了,所以申请library cache lock锁只是为了扫描LCH,而如果要修改LCH指向的LCO就需要申请library cache pin了)。当解析或编译一个SQL或PL/SQL语句时,需要获取相关联的数据库对象(如table,view,procedure ,function,package,package body,trigger,index,cluster,synonym等)的library cache lock。当解析或编译完成后释放锁。
Cursors(SQL和PL/SQL),pipe和其它临时对象不使用这种锁(使用mutex)。
Library cache lock不进行死锁检测(死锁检测开销太大),所有操作都是同步的。(这个原因可能是:enqueue只有在rollback或commit后释放,而library cache 锁一般是执行完就释放,不需要rollback或commit)
library cache lock等待事件的参数:
P1:句柄地址
要装载的library cache object 的句柄,对应到x$kglob.kglhdadr
P2:锁地址
使用的锁地址。这与latch或enqueue不同,这是一个State Object,对应到x$kgllk.kgllkadr
P3:Encoded Mode & Namespace

4.3. library cache pin

“library cache pin”用于管理library cache的并发。由于需要将一个堆装载到内存,所以需要pin对象。如果客户端(这里的客户端是指shadow server进程)想要修改和检测对象,在请求完library cache lock锁后,需要请求library cache pin锁。PIN锁可以以NULL,SHARE或EXCLUSIVE模式请求,与enqueue锁级别类似。等待事件“library cache pin”表明其它会话以不兼容模式持有PIN锁。
获取library cache pin锁后,就可以获取数据库对象(table,view,procedure,function…)当前在library cache中的缓存。在library cache中,一个对象缓存有2个部分,“handle”和“object”。只有需要处理“object”部分缓存时,才需要library cache pin锁。
同样,library cache pin也没有死锁检测。

4.4. 为什么需要这两种锁

library cache lock锁管理着进程的并发性,而library cache pin锁管理cache的一致性。为了访问一个对象,进程首先要lock句柄(library cache handle),然后pin对象堆(library cache object)。
当请求这library cache锁时,进程会一直请求直到获取。这一般是library cache锁的争用源头,因为没有NOWAIT这种请求模式。
当在object handle上获得lock锁后,可以防止其它进程访问该对象以及检查该对象是什么类型。因为handle和object这两个部分有关联关系,所以就不需要预防其它进程访问object部分了(因为一定是先访问handle再访问object)。在cache中定位一个对象,请求lock是唯一的方法。进程定位和锁对象是在一个操作里。
如果进程想要测试或修改对象,那就需要获取在该对象上的pin锁(在获取handle的lock锁后)。如果对象不在library cache中,pin对象就会使对象信息和它的堆装载到内存中。在释放pin锁前,这些信息一直在内存中。
编译或解析包、过程、函数或视图时,需要Library cache lock和library cache pin。用于保证在修改过程中没有其它会话使用这些对象。
当SQL语句解析时,会话需要请求library cache lock防止其它会话访问或修改相同的对象。如果这个事件占很多时间,那可能表明共享池太小,或需要定期刷新。否则就表明数据库对象被定期修改。
除了硬解析之外,如果对象想修改SQL中对象的定义或做任意其它修改,都需要请求library cache lock然后是library cache pin。请求pin锁是需要装载相应的数据字典信息到内存,并访问相关代码。

4.5. library cache load lock

当对象不在library cache中时,就无法对library cache handle加锁。这时侯会话需要请求load lock,来把对象装载到内存。load lock总是以排它模式获取,所以没有其它会话可以在同时装载相同的对象。如果load lock争用,那会话会一直处理等待状态直到load lock可用。
library cache pin和load lock可能发生在PL/SQL,视图,类型等的编译和重编译时,因为失效对象可能自动重编译。

4.6. 诊断

如果发生了library cache lock和library cache pin等待事件,那就表明有会话想要请求library cache的lock锁或pin锁被阻塞了。如果是短时间出现这种事件,一般可能是共享池太小、SQL硬件太高等原因。但如果是长时间或者是大量会话出现这个问题,那可能是在生产时间在编译存储过程或者是异常操作导致,这时侯需要把阻塞者会话找出来杀掉来释放相关的lock锁或pin锁。Oracle在11gR2上还存在一个登录Bug,由于密码错误,并且登录频繁,导致严重的library cache lock等待,具体参考:LIBRARY CACHE LOCKS DUE TO INVALID LOGIN ATTEMPTS [ID 1309738.1]。
找出library cache锁的持有者,可以使用如下SQL:

set linesize 200 pagesize 999
column username format a10
column program format a18
column machine format a15
column sql_id format a14
column event format a30
column owner format a12
column object_name format a25
select s.sid, s.username, s.program, s.machine, s.sql_id, s.event,
       s.status, l.kgllktype as type, l.kgllkmod as lmode,
       o.kglnaown as owner, o.kglnaobj as object_name
 from dba_kgllock l, x$kglob o, v$session s
where l.kgllkhdl = '&p1raw'
  and l.kgllkreq = 0
  and l.kgllkmod > 1
  and l.kgllkhdl = o.kglhdadr
  and s.SADDR = l.kgllkuse;

这里的p1raw,指v$session中的p1raw。

4.7. Event 10049

event 10049,这个事件在10gR2以后,专门被用来trace librarycache lock和library cache pin,用10049的难点在于如何确定level。
首先,10049的level可能会有如下一些值:

#define KGLTRCLCK  0x0010                       /* trace lock operations */
#define KGLTRCPIN  0x0020                       /* trace pin operations  */
#define KGLTRCOBF  0x0040                       /* trace object freeing  */
#define KGLTRCINV  0x0080                       /* trace invalidations   */
#define KGLDMPSTK  0x0100                  /* DUMP CALL STACK WITH TRACE */
#define KGLDMPOBJ  0x0200                  /* DUMP KGL OBJECT WITH TRACE */
#define KGLDMPENQ  0x0400                 /* DUMP KGL ENQUEUE WITH TRACE */
#define KGLTRCHSH  0x2000                          /* DUMP BY HASH VALUE */

其次,我们是要针对单个sql,所以需要用到这个sql的hash value,以便将10049和这个sql联系起来,即我们一定要用到KGLTRCHSH,也就是0x2000;另外我们是要trace library cache lock和library cache pin,所以我们一定要用到KGLTRCLCK和KGLTRCPIN,即0x0010和0x0020;
最后就是我们需要把这个sql的hash value的16进制的后两个byte拿出来,作为10049的level的前缀。

SQL> select hash_value,to_char(hash_value,'XXXXXXXX') as hex_hash_value, sql_text
       from v$sqlarea
      where sql_text like 'select * from scott.emp%';

HASH_VALUE HEX_HASH_ SQL_TEXT
---------- --------- ------------------------------
  52404428   31FA0CC select * from scott.emp
KGLTRCHSH | KGLTRCLCK | KGLTRCPIN = 0x2000 | 0x0010 | 0x0020 = 0x2030

select * from scott.emp的hash value的16进制的后两个byte是0xA0CC。
所以select * from scott.emp的10049 Event的最终level值就是0xA0CC2030,也就是2697732144。转储过程如下:
硬解析

SQL> alter system flush shared_pool;
System altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10049 trace name context forever, level 2697732144
Statement processed.
SQL> select * from scott.emp;

......

SQL> oradebug tracefile_name
/opt/oracle/app/oracle/admin/test10/udump/test10_ora_47330.trc
SQL> oradebug event 10049 trace name context off
Statement processed.

*** 2016-01-05 10:25:29.113
Processing Oradebug command 'event 10049 trace name context forever, level 2697732144'
KGLTRCLCK kgllkal    hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb997d890 mode = N
KGLTRCLCK kglget     hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb997d890 mode = N
KGLTRCPIN kglpin     hd = 0x0xb4cb1940  KGL Pin  addr = 0x0xb9763c70 mode = X
KGLTRCPIN kglpndl    hd = 0x0xb4cb1940  KGL Pin  addr = 0x0xb9763c70 mode = X
KGLTRCLCK kgllkal    hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb997d160 mode = N
KGLTRCLCK kglget     hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb997d160 mode = N
KGLTRCPIN kglpin     hd = 0x0xbe9e2340  KGL Pin  addr = 0x0xb9979c38 mode = X
KGLTRCPIN kglpndl    hd = 0x0xbe9e2340  KGL Pin  addr = 0x0xb9979c38 mode = X
*** 2016-01-05 10:25:43.537
KGLTRCLCK kgllkdl    hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb997d160 mode = N
KGLTRCLCK kgllkdl2   hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb997d160 mode = 0
KGLTRCLCK kgllkdl    hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb997d890 mode = N
KGLTRCLCK kgllkdl2   hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb997d890 mode = 0

SQL> select kglhdadr,kglhdpar,kglnaown,kglnaobj from x$kglob
  2   where kglhdadr like '%B4CB1940%';
KGLHDADR         KGLHDPAR         KGLNAOWN  KGLNAOBJ
---------------- ---------------- --------- --------------------------
00000000B4CB1940 00000000B4CB1940           select * from scott.emp

SQL> select kglhdadr,kglhdpar,kglnaown,kglnaobj from x$kglob
  2   where kglhdadr like '%BE9E2340%';
KGLHDADR         KGLHDPAR         KGLNAOWN   KGLNAOBJ
---------------- ---------------- ---------- -------------------------
00000000BE9E2340 00000000B4CB1940            select * from scott.emp

SQL> select sql_id, address, child_address, hash_value, last_active_time
  2         from v$sql
  3        where sql_text like 'select * from scott.emp%';
SQL_ID        ADDRESS          CHILD_ADDRESS    HASH_VALUE  LAST_ACTIVE_TIME
------------- ---------------- ---------------- ----------  ----------------
ggqns3c1jz86c 00000000B4CB1940 00000000BE9E2340   52404428  2016-01-05 10:25

软解析

*** 2016-01-05 10:42:49.101
Processing Oradebug command 'event 10049 trace name context forever, level 2697732144'
KGLTRCLCK kgllkal    hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb977d4e0 mode = N
KGLTRCLCK kglget     hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb977d4e0 mode = N
KGLTRCLCK kgllkal    hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb998e080 mode = N
*** 2016-01-05 10:43:00.864
KGLTRCLCK kgllkdl    hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb998e080 mode = N
KGLTRCLCK kgllkdl2   hd = 0x0xbe9e2340  KGL Lock addr = 0x0xb998e080 mode = 0
KGLTRCLCK kgllkdl    hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb977d4e0 mode = N
KGLTRCLCK kgllkdl2   hd = 0x0xb4cb1940  KGL Lock addr = 0x0xb977d4e0 mode = 0

5. Mutex

在Libaray cache锁中提到,Oracle 从10g引入的新的串行机制-互斥锁机制MUTEX,用于替换Cursors(SQL和PL/SQL),pipe和其它临时对象使用的library cache锁。从10.2.0.2开始,_kks_use_mutex_pin默认为true,在11gR2中已经取消了该隐含参数。以往使用LIBRARY CACHE PIN的时候,一个锁要维护一组对象(比如一组HASH BUCKET),而MUTEX是嵌入到对象内部的,因此一MUTEX仅仅保护一个特定的对象。这也大大提高了MUTEX并发使用的效率。
从v$mutex_sleep_history视图中查看mutex_type,在10gR2中是:Cursor Parent、Cursor Pin、hash table类型,而11gR2中的类型是:Library Cache、Cursor Pin
相关诊断事件:

  • cursor: mutex X
  • cursor: mutex S
  • cursor: pin S
  • cursor: pin X
  • cursor: pin S wait on X

以下两个等待事件是11gR2中新增的

  • library cache: mutex X
  • library cache: mutex S

查看Mutex等待事件参数:

SQL> select name, parameter1, parameter2, parameter3
       from v$event_name n
      where n.NAME like '%mutex%';

NAME                     PARAMETER1  PARAMETER2   PARAMETER3
------------------------ ----------- ------------ ----------
SecureFile mutex
cursor: mutex X          idn         value        where
cursor: mutex S          idn         value        where
library cache: mutex X   idn         value        where
library cache: mutex S   idn         value        where

其中p1就是mutex的标识符,p2的高8位是持有mutex会话的sid,低8位是一个计数,记录了当前正在以share方式访问该mutex的数量。

SQL> select p2raw from v$session where event = 'cursor: pin S wait on X';
P2RAW
----------------
0000001F00000000
 <SID>  <RefCnt>

在systemstate dump的trc文件中,mutex的表现如下:

PROCESS 595:
  ----------------------------------------
  SO: 0x700000600c64ad8, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
……
    Current Wait Stack:
     0: waiting for 'cursor: pin S wait on X'
        idn=0x77288c8e, value=0x7cb00000000, where=0x500000000
        wait_id=20 seq_num=21 snap_id=1
        wait times: snap=22.961360 sec, exc=22.961360 sec, total=22.961360 sec
        wait times: max=infinite, heur=22.961360 sec
……

所以只需要通过mutex等待事件就可以找到该mutex的持有者会话,但是mutex的持有一般非常短暂,并且mutex争用一般是由其它问题造成,当前最新版本的Oracle这块的bug较少,所以只去诊断mutex没有太大意义。

6. Latch

6.1. 概述

Oracle数据库使用闩锁(latch)来管理SGA内存的分配和释放。Latch是用于保护SGA区中共享数据结构的一种串行化锁定机制。Latch的实现是与操作系统相关的,比如一个进程是否需要等待一个latch,需要等待多长时间。
Latch是一种能够极快地被获取和释放的系统级锁,它通常用于保护描述buffer cache中block的数据结构、共享池内存结构等。与每个latch相联系的还有一个清除过程,当持有latch的进程成为死进程时,该清除过程就会被调用。Latch还具有相关级别,用于防止死锁,一旦一个进程在某个级别上得到一个latch,它就不可能再获得等同或低于该级别的latch。

6.2. SPIN与休眠

spin 就是一个进程独占cpu time,直到运行的结束。这个期间其他进程不能获得这个cpu的运行时间。对于单CPU来说没有spin概念。
比如数据缓存中的某个块要被读取,我们会获得这个块的 latch,这个过程叫做spin,另外一个进程恰好要修改这个块,他也要spin这个块,此时他必须等待,当前一个进程释放latch后才能spin住,然后修改,如果多个进程同时请求的话,他们之间将出现竞争,没有一个入队机制,一旦前面进程释放所定,后面的进程就蜂拥而上,没有先来后到的概念,并且这一切都发生的非常快,因为Latch的特点是快而短暂。
休眠意味着暂时的放弃CPU,进行上下文切换(context switch),这样CPU要保存当前进程运行时的一些状态信息,比如堆栈,信号量等数据结构,然后引入后续进程的状态信息,处理完后再切换回原来的进程状态,这个过程如果频繁的发生在一个高事务,高并发进程的处理系统里面,将是个很昂贵的资源消耗,所以Oracle选择了spin,让进程继续占有CPU,运行一些空指令,之后继续请求,继续spin,直到达到_spin_count值,这时会放弃CPU,进行短暂的休眠,再继续刚才的动作。初始状态下,一个进程会睡眠0.01秒。然后醒过来,并再次尝试获得latch。 进程一旦进入睡眠状态,则会抛出一个对应的等待事件,并记录在视图v$session_wait里,说明当前该进程正在等待的latch的类型等信息。

6.3. Latch的种类

愿意等待(Willing-To-Wait)
大部分的latch都属于这种类型(Willing-To-Wait)。这种类型的latch都是通过Test-And-Set的方式来实现的。
任何时候,只有一个进程可以访问内存中的某一个数据块,如果进程因为别的进程正占用块而无法获得Latch时,他会对CPU进行一次spin(旋转),时间非常的短暂,spin过后继续获取,不成功仍然spin,直到spin次数到达阀值限制(这个由隐含参数_spin_count指定),此时进程会停止spin,进行短期的休眠,休眠过后会继续刚才的动作,直到获取块上的Latch为止。进程休眠的时间也是存在算法的,他会随着spin次数而递增,以厘秒为单位,如1,1,2,2,4,4,8,8,。。。休眠的阀值限制由隐含参数_max_exponential_sleep控制,默认是2秒,如果当前进程已经占用了别的Latch,则他的休眠时间不会太长(过长会引起别的进程的Latch等待),此时的休眠最大时间有隐含参数_max_sleep_holding_latch决定,默认是4厘秒。这种时间限制的休眠又称为短期等待。
另外一种情况是长期等待锁存器(Latch Wait Posting),此时等待进程请求Latch不成功,进入休眠,他会向锁存器等待链表(Latch Wait List)压入一条信号,表示获取Latch的请求,当占用进程释放Latch时会检查Latch Wait List,向请求的进程传递一个信号,激活休眠的进程。Latch Wait List是在SGA区维护的一个进程列表,他也需要Latch来保证其正常运行,默认情况下share pool latch和library cache latch是采用这个机制。
如果将隐含参数_latch_wait_posting设置为2,则所有Latch都采用这种等待方式,使用这种方式能够比较精确的唤醒某个等待的进程,但维护Latch Wait List需要系统资源,并且对Latch Wait List上Latch的竞争也可能出现瓶颈。
如果一个进程请求,旋转,休眠Latch用了很长时间,他会通知PMON进程,查看Latch的占用进程是否已经意外终止或死亡,如果是,则PMON会清除释放占用的Latch资源。
不等待(No-Wait)
这种类型的latch比较少,对于这种类型的latch来说,都会有很多个可用的latch。当一个进程请求其中的一个latch时,会以no-wait模式开始请求。如果所请求的latch不可用,则进程不会等待,而是立刻请求另外一个latch。只有当所有的latch都不能获得时,才会进入等待。

6.4. Latch和Lock的区别

Latch是对内存数据结构提供互斥访问的一种机制,而Lock是以不同的模式来获取共享资源对象,各个模式间存在着兼容或排斥,从这点看出,Latch 的访问,包括查询也是互斥的,任何时候,只能有一个进程能pin住内存的某一块,幸好这个过程是相当的短暂,否则系统性能将没的保障
Latch只作用于内存中,他只能被当前实例访问,而Lock作用于数据库对象,在RAC体系中实例间允许Lock检测与访问
Latch是瞬间的占用,释放,Lock的释放需要等到事务正确的结束,他占用的时间长短由事务大小决定
Latch是非入队的,而Lock是入队的
Latch不存在死锁,而Lock中存在。

6.5. Latch的cleanup

在latch的使用过程中,可能会出现一些异常,而导致有些latch被异常占有得不到释放,这样就会有问题了,别的进程过来请求不到。出现这样的异常pmon进程会跟进处理,对于其处理的流程来说,最重要的莫过于将没有提交的事务回滚,那么就需要latch支持恢复,那么latch在开始操作前会先写一些信息去latch的恢复区。Pmon 3秒钟会自动运行一下,但是这也是很长的一段时间了,所以在进程在请求一个latch失败多次之后,会post pmon进程去check一下占有这个latch的process是不是正常。

6.6. Latch的level

Latch的级别分为0~14,共15个级别,0级最低,14最高。如果两个latch之间有联系,只能请求level更高的latch。原因如下:
如果a进程占有一个level 为5的latch,它去请求一个level为3的latch,而进程b,占有这个level为3的latch,又去请求那个level 为5的latch,这样会有什么问题呢?因为它是可以去spin的,又是可以去sleep的,sleep之后还是继续重复,那就永远没有完没有了了。所以呢,level的request是有level顺序的,不能随便的请求,只能由低级的latch去请求高级的latch。
如果进程a一定要申请进程b的latch的话,只能放弃原有latch level5为的latch重新对b进程的latch进行申请。

6.7. Latch资源争用

如果latch资源被争用,通常都会表现为CPU资源使用过高,其主要原因有以下几点:
1. SQL语句 如果没有使用绑定变量,很容易造成频繁读写shared pool里的内存块,如果存在大量的SQL被反复分析,就会造成很大的Latch争用和长时间的等待, 从而导致与解析SQL相关的共享池中的Latch争用 。与 shared pool共享池相关的latch有Library Cache Latch 和Shared Pool Latch。
2. 访问频率非常高的数据块被称为热快(Hot Block),当很多用户一起去访问某几个数据块时,就会导致 数据缓冲池Latch 争用,最常见的latch争用有 :buffer busy waits和cache buffer chain。
Cache buffer chian:
当一个会话需要去访问一个内存块时,它首先要去一个像链表一样的结构中去搜索这个数据块是否在内存中,当会话访问这个链表的时候需要获得一个Latch, 如果获取失败,将会产生Latch cache buffer chain 等待,导致这个等待的原因是访问相同的数据块的会话太多或者这个列表太长(如果读到内存中的数据太多,需要管理数据块的hash列表就会很长,这样会话扫 描列表的时间就会增加,持有chache buffer chain latch的时间就会变长,其他会话获得这个Latch的机会就会降低,等待就会增加)。
Buffer busy waits:
当一个会话需要访问一个数据块,而这个数据块正在被另一个用户从磁盘读取到内存中或者这个数据块正在被另一个会话修改时,当前的会话就需要等待,就会产生一个buffer busy waits等待。
产生这些Latch争用的直接原因是太多的会话去访问相同的数据块导致热快问题,造成热快的原因可能是数据库设置导致或者重复执行的SQL 频繁访问一些相同的数据块导致。热块产生的原因不尽相同,按照数据块的类型,可以分成以下几种热块类型,不同热块类型处理的方式都是不同的:表数据块、索 引数据块、索引根数据块和文件头数据块。
3. 另外也有一些latch等待与bug有关,应当关注Metalink相关bug的公布及补丁的发布。
为何latch的争用会引起CPU使用率较高呢?
其实很容易理解,比如进程A持有latch,此时进程B也需要持有相关latch,但是没有获得,这时候进程B就需要进行spin,如果类似进程B的进程较多的话,对CPU进行spin的进程就会较多,表现也就是CPU利用率非常高,但是吞吐量却很低,典型的“出工不出活”。

本篇文章已于2016年12月28日发于OCM之家
Oracle锁概念与常见锁分析详谈(起始篇)
Oracle锁概念与常见锁分析详谈(传承篇)
Oracle锁概念与常见锁分析详谈(完结篇)

欢迎关注:
ocm_home

关于紫砂壶

感悟技术人生
此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。

1 则回应给 Oracle锁概念与常见锁分析

  1. 匿名说:

    不错!

评论已关闭。