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
来源:行足迹旅游转载请注明