Oracle中的锁和10704对高级队列锁的跟踪实验

锁是数据库用来控制共享资源并发访问的机制，是用来保护和用户相关的资源，如：表、用户、会话等。
本文以Oracle中锁的种类和模式讲起，后用10704做实验观察不同sql语句下锁持有情况的不同，主要以TM锁为例。
小弟初学Oracle，如果错误还望多多包涵和留言指出

一、Oracle锁的种类
Oracle里锁分为三大类：
高级队列锁：TM，TX锁等等  视图V$LOCK
中级内存锁：Library cache lock
               Library cache pin（堆）
               Row cache lock；
               Buffer pin lock;
低级内存锁：Latch
               Mutex
高级队列锁Lock与低级内存锁的区别：
1.Lock的种类多，如TM，TX，TT等等；
2.Lock作用于数据库Object，Lacth只作用于SGA内存中；
3.Lock的释放需要等到事务结束，Latch是瞬间的占用和释放；
4.Lock存在死锁，Latch不存在死锁（因free lock机制）

低级内存锁与中级内存锁的区别，主要在于低级内存锁有lock free机制（无锁式编程）：即请求latch时，如果该latch不可用，进程会在CPU中等待小段时间（spin）然后重新请求latch。如果依然不可用，该进程会重复以上步骤去请求latch。重复的次数默认值为2000（mutex为500）。如果在重复次数内还是请求不到latch，进程会在v$session_wait中记录latch free等待事件，然后释放cpu，转入睡眠状态。当睡眠一定时间后，进程被唤醒并重复上面的过程，一直到成功获取latch。因此低级内存锁避免了死锁的存在。此外，中级内存锁往往执行多条记录才会释放，而低级内存锁执行几条就可能会释放。

二、Oracle锁的模式
锁模式可粗略的分为空锁，共享和独占：
空  锁：1号锁，空锁，通常只起标志性作用，保证内存不会被释放。
共享锁：2-5号锁，包括RS（行共享）、RX（行专用）、S（共享锁）、SRX（共享行专用），允许部分DML操作，数字越大影响的操作越大。
独占锁：6号锁，exclusive独立访问使用，是限制最高级别的锁，禁止所有DML操作。

说明：Oracle中多数锁都是有着多种模式的，除高级队列锁TM外，中级内存锁Library Cache Lock/pin、buffer pin也是有多种模式的。

本文主要以跟踪高级队列锁为主：
TM锁定的是对象，所以叫对象锁，或者表锁，用来阻塞DML（或DDL）的运行，通常是持有2-6号锁，1号较少。
TX即事务独占锁，是所有加在行上的锁的总称。模式只有两种，要么无锁，要么独占锁（6号）；

三、跟踪高级队列锁Lock示例
10704事件可以帮助我们跟踪锁和队列的使用情况，下面先以update为例，用10704事件来观察锁的状态：
sqlplus cheng/oracle
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE       11.2.0.4.0       Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select * from test_t1;
ID NAME
---------- --------------------
1 aaaaaa
2 aaaaaa
3 aaaaaa
4 aaaaaa
5 aaaaaa
6 aaaaaa
7 aaaaaa
8 aaaaaa
9 aaaaaa
10 aaaaaa

10 rows selected.

SQL> alter session set events '10704 trace name context forever, level 12'; --开启10704跟踪
Session altered.
SQL> update test_t1 set NAME='bbbbbb' where id<5;  --更新几条数据
4 rows updated.
SQL> commit;  --提交数据
Commit complete.
SQL> alter session set events '10704 trace name context off'; --关闭10704跟踪
Session altered.

[oracle@localhost ~]$ cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/
[oracle@localhost trace]$ more orcl_ora_4992.trc  --提取日志分析

*** 2017-07-05 15:58:46.636
ksqgtl *** CU-bd59a970-00000000 mode=6 flags=0x10000 timeout=300 *** --申请持有游标锁
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
ksqlkdid: 0001-0015-00000017

*** 2017-07-05 15:58:46.636
*** ksudidTrace: ksqgtl
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0015-00000017
ksqgtl: RETURNS 0    --获得游标锁

*** 2017-07-05 15:58:46.639
ksqrcl: CU,bd59a970,0
ksqrcl: returns 0    --释放游标锁

*** 2017-07-05 15:58:46.639
ksqgtl *** TM-00015b7d-00000000 mode=3 flags=0x401 timeout=21474836 ***  --申请持有TM锁(3号）
ksqgtl: xcb=0xbbeb52b8, ktcdix=2147483647, topxcb=0xbbeb52b8
ktcipt(topxcb)=0x0

*** 2017-07-05 15:58:46.639
ksucti: init txn DID from session DID
ksqgtl:
ksqlkdid: 0001-0015-00000017

*** 2017-07-05 15:58:46.639
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0015-00000017
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0015-00000017
ksqgtl: RETURNS 0 --获得TM锁

*** 2017-07-05 15:58:46.639
ksqgtl *** TX-00090011-0000058b mode=6 flags=0x401 timeout=0 ***  --申请持有TX锁（6号独占）
ksqgtl: xcb=0xbbeb52b8, ktcdix=2147483647, topxcb=0xbbeb52b8
ktcipt(topxcb)=0x0

*** 2017-07-05 15:58:46.639
ksucti: init session DID from txn DID:
ksqgtl:
ksqlkdid: 0001-0015-00000017

*** 2017-07-05 15:58:46.639
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0015-00000017
ksusesdi: 0000-0000-00000000
ksusetxn: 0001-0015-00000017
ksqgtl: RETURNS 0  --获得TX锁

--此时COMMIT提交

*** 2017-07-05 15:58:57.519
ksqrcl: TX,90011,58b
ksqrcl: returns 0 --释放TX锁

*** 2017-07-05 15:58:57.519
ksqrcl: TM,15b7d,0
ksqrcl: returns 0 --释放TM锁

以上即是update test_t1 set NAME='bbbbbb' where id<5语句的锁的状态过程

分析：
TM-00015b7d-00000000 mode=3
TM锁ID1对应的是Object_ID，00015b7d（十六进制）>88957（十进制）
SQL> select object_id,object_name from dba_objects where object_id=88957;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
   88957 TEST_T1
可知在update执行过程中，TEST_T1持有的是3号锁，不会堵塞DML操作。

TX-00090011-0000058b mode=6
即对应update test_t1 set NAME='bbbbbb' where id<5语句，可见Update四行，只产生一个TX锁。

通过10704观察oracle高级队列锁的持有释放过程以及锁模式，从而知道操作DML或DDL语句过程中Oracle究竟锁定了哪些表，以及对表操作的影响，分析出执行语句可能产生的性能问题，从而去避免。
高级队列锁比较经典的问题就是创建索引的online和offiline持有什么模式，下面的实验用10704来跟踪各种语句持有的锁信息，以观察TM和TX锁为主

四、Offline和Online创建索引的锁的跟踪对比
这次实验观察不同sql语句下锁持有情况的不同，主要以TM锁为例
实验准备：
sqlplus / as sysdba
SQL> create table text as select * from dba_objects;
SQL> insert into text select * from text;
.....
SQL> select count(*) from text;
  COUNT(*)
----------
  22267136
SQL> create table text_t1 as select * from text;
SQL> select OBJECT_ID,OBJECT_NAME from dba_objects where OBJECT_NAME='TEXT_T1';
OBJECT_ID OBJECT_NAME
---------- ------------------------------------------------------------------------------------------------
   89012 TEXT_T1

1.Create index
Session 1
conn cyh/oracle
SQL> select sid from v$mystat where rownum=1 确认当前会话号SID
----------
  45
SQL> alter session set events '10704 trace name context forever, level 12'; 启动10704观察
SQL> create index idx_text01 on text_t1(OBJECT_ID);

Session 2
sqlplus / as sysdba
在索引创建的同时，新开个窗口观察45会话持有锁的动态
SQL> select * from v$lock where sid=45;
ADDR KADDR SID TY       ID1  ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BED5CE58 00000000BED5CEB0  45 AE       100 0    4  0 132 0
00000000BED5D1D8 00000000BED5D230  45 DL    89012 0    3  0 47 0
00000000BED5D478 00000000BED5D4D0  45 DL    89012 0    3  0 47 0
00007F2A281DC028 00007F2A281DC088  45 TM    89012 0    4  0 47 0
00007F2A281DC028 00007F2A281DC088  45 TM 18 0    3  0 47 0
00000000BBE04B08 00000000BBE04BB8  45 TS 14 29360258    6  0 21 0
00000000BBF03CC8 00000000BBF03D40  45 TX    262165 1930    6  0 47 0

Session 1
SQL> create index idx_text01 on text_t1(OBJECT_ID);
Index created.
SQL> alter session set events '10704 trace name context off';

Linux
[oracle@localhost trace]$ more orcl_ora_24114.trc 从OS从提取日志分析

TM-00015bb4-00000000 mode=4  指向TEXT_T1 开头持有结束释放 4号锁，阻塞其他事务DML操作

TM-00000012-00000000 mode=3  指向OBJ$ 多次持有多次释放
TM-0000000e-00000000 mode=3  指向SEG$ 多次持有多次释放
TM-00000014-00000000 mode=3  指向ICOL$  仅在末尾持有和释放一次
TM-00000013-00000000 mode=3  指向IND$ 仅在末尾持有和释放一次

Create index时，持有4号TM锁在TEXT_T1表，在此期间堵塞其他事务DML操作；另以TM锁3号模式锁定OBJ$，SEG$，ICOL$，IND$数据字典表，对其进行DML（Insert）操作，以更新维护数据字典表信息。

TX锁的记录以及加到哪个对象上（按时间先后）：
OBJ$：
TX-00040015-0000078a mode=6
TX-00040004-0000078a mode=6
SEG$：
TX-0004001d-00000789 mode=6
TX-00040011-00000789 mode=6
TX-00040001-0000078a mode=6
TX-00040010-0000078b mode=6
TX-00040000-0000078b mode=6
TX-00040012-0000078b mode=6
TX-00040020-0000078a mode=6
TX-00040003-0000078b mode=6
TX-0004001c-0000078b mode=6
TX-0004000a-0000078d mode=6
TX-0004001f-0000078b mode=6
TX-00040019-0000078d mode=6
TX-0004000e-0000078d mode=6
TX-0004001f-0000078d mode=6
TX-00040005-0000078e mode=6
TX-0004000b-00000788 mode=6
TX-00040014-0000078d mode=6
TX-00040021-00000788 mode=6
TX-00040002-00000781 mode=6
TX-0004001a-00000777 mode=6
TX-00040013-0000078d mode=6
TX-00040009-0000078d mode=6
TX-0004001b-0000078d mode=6
TX-00040006-0000078b mode=6
TX-0004000d-0000078e mode=6
TX-00040017-0000078e mode=6
TX-0004000f-0000078e mode=6
TX-00040008-00000789 mode=6
TX-00040018-0000078d mode=6
TX-00040007-0000078d mode=6
TX-0004000c-0000078e mode=6
TX-0004001e-0000078c mode=6
TX-00040016-0000078e mode=6
TX-00040004-0000078d mode=6
TX-0004001d-0000078c mode=6
TX-00040011-0000078c mode=6
TX-00040001-0000078d mode=6
TX-00040010-0000078e mode=6
TX-00040000-0000078e mode=6
TX-00040012-0000078e mode=6
TX-00040020-0000078d mode=6
TX-00040003-0000078e mode=6
TX-0004001c-0000078e mode=6
TX-0004000a-0000078e mode=6
TX-00040019-0000078e mode=6
TX-0004000e-0000078e mode=6
TX-0004001f-0000078e mode=6
TX-00040005-0000078f mode=6
TX-0004000b-00000789 mode=6
TX-00040014-0000078e mode=6
TX-00040021-00000789 mode=6
TX-00040002-00000782 mode=6
TX-0004001a-00000778 mode=6
TX-00040013-0000078e mode=6
TX-00040009-0000078e mode=6
TX-0004001b-0000078e mode=6
TX-00040006-0000078c mode=6
TX-0004000d-0000078f mode=6
TX-00040017-0000078f mode=6
TX-0004000f-0000078f mode=6
TX-00040008-0000078a mode=6
TX-00040018-0000078e mode=6
TX-00040007-0000078e mode=6
TX-0004000c-0000078f mode=6
TX-0004001e-0000078d mode=6
TX-00040016-0000078f mode=6
TX-00040004-0000078e mode=6
TX-0004001d-0000078d mode=6
TX-00040011-0000078d mode=6
TX-00040001-0000078e mode=6
TX-00040010-0000078f mode=6
TX-00040000-0000078f mode=6
TX-00040012-0000078f mode=6
TX-00040020-0000078e mode=6
TX-00040003-0000078f mode=6
TX-0004001c-0000078f mode=6
TX-0004000a-0000078f mode=6
TX-00040019-0000078f mode=6
TX-0004000e-0000078f mode=6
TX-0004001f-0000078f mode=6
TX-00040005-00000790 mode=6
TX-0004000b-0000078a mode=6
TX-00040014-0000078f mode=6
TX-00040021-0000078a mode=6
TX-00040002-00000783 mode=6
TX-0004001a-00000779 mode=6
TX-00040013-0000078f mode=6
TX-00040009-0000078f mode=6
TX-0004001b-0000078f mode=6
TX-00040006-0000078d mode=6
TX-0004000d-00000790 mode=6
TX-00040017-00000790 mode=6
TX-0004000f-00000790 mode=6
TX-00040008-0000078b mode=6
TX-00040018-0000078f mode=6
TX-00040007-0000078f mode=6
TX-0004000c-00000790 mode=6
TX-0004001e-0000078e mode=6
TX-00040016-00000790 mode=6
TX-00040004-0000078f mode=6
TX-00040004-0000078f mode=6
TX-00040011-0000078e mode=6
TX-00040001-0000078f mode=6
TX-00040010-00000790 mode=6
注意：Oracle存在自治事务，自制事务是由主事务调用但是独立于主事务的事务。在自治事务被调用执行时，主事务被挂起，在自治事务内部，一系列的DML可以被执行并且commit或rollback. 自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。由于自治事务的独立性，它的commit和rollback并不影响主事务的执行效果。在自治事务执行结束后，主事务获得控制权，又可以继续执行了。

其他锁（系统锁）的记录：
CU-XXXXXXX-00000000 mode=6 Cursor bind 游标绑定锁多个
DL-00015bb4-00000000 mode=3 指向TEST_T1  Direct loader parallel index create
TT-00000003-00000010 mode=4 指向I_OBJ# 临时表队列锁Temporary table enqueue
TS-0000000e-01c00082 mode=6 指向SEG$    New block allocation enqueue
HW-0000000e-01c00082 mode=6  指向SEG$    特定区块空间管理锁
PW-00000001-00000000 mode=4
KO-00010013-00000001 mode=6
MR-00000007-00000002 mode=4
MR-00000006-00000002 mode=4
MR-00000008-00000002 mode=4
MR-000000c9-00000002 mode=4
MR-00000003-00000002 mode=4

来源：行足迹旅游转载请注明

posted on 2018-01-21 21:02 行足迹旅游阅读(205) 评论(0) 编辑收藏举报