阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle锁的模式和10704事件跟踪对比

204次阅读
没有评论

共计 35901 个字符,预计需要花费 90 分钟才能阅读完成。

锁是数据库用来控制共享资源并发访问的机制,是用来保护和用户相关的资源,如:表、用户、会话等。本文以 Oracle 中锁的种类和模式讲起,后用 10704 做实验观察不同 sql 语句下锁持有情况的不同,主要以 TM 锁为例。

 一、Oracle 锁的种类
Oracle 里锁分为三大类:
高级队列锁:TM,TX 锁等等  视图 V$LOCK
中级内存锁:Library cache lock
                  Library cache pin(堆)
                  Row cache lock;
                  Buffer pin lock;
低级内存锁:Latch
                  Mutex
低级内存锁与中级内存锁的区别,主要在于低级内存锁有 lock free 机制(无锁式编程):即请求 latch 时,如果该 latch 不可用,进程会在 CPU 中等待小段时间(spin)然后重新请求 latch。如果依然不可用,该进程会重复以上步骤去请求 latch。重复的次数默认值为 2000(mutex 为 500)。如果在重复次数内还是请求不到 latch,进程会在 v$session_wait 中记录 latch free 等待事件,然后释放 cpu,转入睡眠状态。当睡眠一定时间后,进程被唤醒并重复上面的过程,一直到成功获取 latch。因此低级内存锁避免了死锁的存在。此外,中级内存锁往往执行多条记录才会释放,而低级内存锁执行几条就可能会释放。

高级队列锁 Lock 与低级内存锁 latch 的对比:
1.Lock 的种类多,如 TM,TX,TT 等等;
2.Lock 作用于数据库 Object,Lacth 只作用于 SGA 内存中;
3.Lock 的释放需要等到事务结束,Latch 是瞬间的占用和释放;
4.Lock 存在死锁,Latch 不存在死锁(因 free lock 机制)

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

本文主要以跟踪高级队列锁 TM 和 TX 为主:
TM 锁锁定的是对象,所以叫对象锁,或者表锁,用来阻塞 DML(或 DDL)的运行,模式上通常 1 号锁较少,主要持有 2,3,4,5,6 号锁。
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 锁。

四、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

2.Create index online:
Session 1
SQL> drop index idx_text01;
SQL> alter session set events ‘10704 trace name context forever, level 12’;
SQL> create index idx_text_ol on text_t1(OBJECT_ID) online;
Session 2
新窗口观察 V$LOCK 中的会话持有锁动态
SQL> select * from v$lock where sid=45;
ADDR KADDR SID TY        ID1  ID2  LMODE    REQUEST CTIME    BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
00000000BED5CD78 00000000BED5CDD0  45 DL      89012    0      3  0    45 0
00000000BED5CE58 00000000BED5CEB0  45 AE        100    0      4  0 11379 0
00000000BED5D398 00000000BED5D3F0  45 DL      89012    0      3  0    45 0
00000000BED5D478 00000000BED5D4D0  45 OD      89012    0      4  0    45 0
00007F2A281DA3A8 00007F2A281DA408  45 TM      89012    0      2  0    45 0
00007F2A281DA3A8 00007F2A281DA408  45 TM      89042    0      4  0    45 0
00000000BBE04B08 00000000BBE04BB8  45 TS 14  25597834      6  0    7 0
00000000BBF03CC8 00000000BBF03D40  45 TX      65555 1310      6  0    45 0
00000000BBF44768 00000000BBF447E0  45 TX      65562 1294      6  0    7 0

SQL> select object_name from dba_objects where object_id=89042;

OBJECT_NAME
——————————————————————————————————————————–
SYS_JOURNAL_89041      Create index online 执行过程中会创建一个临时对象,并在执行结束前删除对象和释放锁。

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

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

TM-00015bb4-00000000 mode=2    指向 TEXT_T1  开头持有结尾释放。持有 2 号 TM 锁,不阻塞 DML 操作
TM-00015bd2-00000000 mode=4    指向 SYS_JOURNAL_89041 临时对象
TM-00000012-00000000 mode=3    指向 OBJ$              持有再释放,首尾两次
TM-0000001c-00000000 mode=3    指向 CON$            持有再释放,首尾两次
TM-0000000e-00000000 mode=3    指向 SEG$              持有再释放 重复九次
TM-00000014-00000000 mode=3    指向 ICOL$            持有再释放 重复四次
TM-00000013-00000000 mode=3    指向 IND$              持有再释放 重复四次
TM-00000004-00000000 mode=3    指向 TAB$              持有再释放 首尾两次
TM-00000015-00000000 mode=3    指向 COL$            持有再释放 重复两次
TM-00000020-00000000 mode=3    指向 CCOL$          持有再释放 重复两次
TM-0000001f-00000000 mode=3    指向 CDEF$            持有再释放 重复两次
TM-00000092-00000000 mode=3    指向 IND_ONLINE$    持有再释放 重复三次
TM-000001c7-00000000 mode=3    指向 IND_STATS$      持有再释放 重复三次
TM-000001eb-00000000 mode=3    指向 WRI$_OPTSTAT_IND_HISTORY
TM-000137d9-00000000 mode=3    指向 SDO_GEOR_DDL__TABLE$$
TM-000000db-00000000 mode=3    指向 OBJECT_USAGE   
TM-000001c9-00000000 mode=3    指向 CACHE_STATS_1$
TM-00000089-00000000 mode=3    指向 COM$ 
TM-000001c0-00000000 mode=3    指向 HIST_HEAD$
TM-000000b6-00000000 mode=3    指向 TRANSIENT_IOT$
TM-00000282-00000000 mode=3    指向 SUMDELTA$
TM-0000027f-00000000 mode=3    指向 SUMPARTLOG$
TM-00000286-00000000 mode=3    指向 SNAP_LOADERTIME$ 
TM-000137d9-00000000 mode=3    指向 SDO_GEOR_DDL__TABLE$$
TM-000000e1-00000000 mode=3    指向 IDL_UB1$
TM-000000e2-00000000 mode=3    指向 IDL_CHAR$
TM-000000e3-00000000 mode=3    指向 IDL_UB2$
TM-000000e4-00000000 mode=3    指向 IDL_SB4$
TM-000000f2-00000000 mode=3    指向 NCOMP_DLL$
TM-0000003d-00000000 mode=3    指向 OBJAUTH$
TM-00000072-00000000 mode=3    指向 ICOLDEP$ 
TM-00000097-00000000 mode=3    指向 JIJOIN$
TM-0000009b-00000000 mode=3    指向 JIREFRESHSQL$ 
TM-0000007b-00000000 mode=3    指向 ECOL$
TM-00000053-00000000 mode=3    指向 COLTYPE$
TM-00000056-00000000 mode=3    指向 SUBCOLTYPE$ 
TM-00000058-00000000 mode=3    指向 NTAB$
TM-00000050-00000000 mode=3    指向 LOB$
TM-0000005c-00000000 mode=3    指向 REFCON$
TM-0000005f-00000000 mode=3    指向 OPQTYPE$

Create index online 时,持有 2 号 TM 锁在 TEXT_T1 上,索引创建完成时释放锁;重建索引期间不会堵塞其它事务的 DML 操作,也保证了一定的并发性;
Create index online 执行中系统会自动创建一个 SYS_JOURNAL_XXXXX 系统临时日志表,用来存放 Create index Online 过程中索引变化的记录日志,持有 4 号 TM 锁,并在 TEXT_T1 的 2 号 TM 锁释放之前进行释放。
Create index online 相比 Create index,执行过程中持有的 3 号 TM 锁更多,数据字典表更新更频繁。

TX 锁的记录以及加到哪个对象上(按时间先后):
OBJ$:
TX-00050009-000009e4 mode=6
CON$:
TX-0005001a-000009db mode=6
SYS_JOURNAL_89041:
TX-00050005-000009df mode=6
TX-0005000e-000009e4 mode=6
ICOL$:
TX-0005001e-000009e0 mode=6
TX-0005001b-000009dd mode=6
CCOL$:
TX-00050020-000009e1 mode=6
TX-00050002-000009e4 mode=6
TX-00050008-000009e5 mode=6
SEG$
TX-00010013-0000051e mode=6
TX-00010014-0000051e mode=6
TX-00010017-0000051e mode=6
TX-00010000-0000051a mode=6
TX-0001001a-0000050e mode=6
TX-0001001c-0000051c mode=6
TX-0001001b-0000051d mode=6
TX-00010019-0000051e mode=6
TX-00010004-0000051e mode=6
TX-0001001f-0000051e mode=6
TX-00010016-0000051d mode=6
TX-0001000a-0000051d mode=6
TX-0001000c-0000051e mode=6
TX-00010012-0000051d mode=6
TX-00010007-0000051e mode=6
TX-00010008-0000051e mode=6
TX-00010015-0000051e mode=6
TX-00010003-0000051f mode=6
TX-00010002-0000051d mode=6
TX-00010021-0000051d mode=6
TX-0001001d-0000051d mode=6
TX-00010009-0000051f mode=6
TX-0001000e-0000051e mode=6
TX-00010011-0000051e mode=6
TX-00010020-0000051f mode=6
TX-00010001-0000051f mode=6
TX-00010006-0000051f mode=6
TX-00010018-0000051e mode=6
TX-00010005-0000051a mode=6
TX-00010010-0000051e mode=6
TX-0001001e-0000051f mode=6
TX-0001000b-0000051f mode=6
TX-0001000d-0000051f mode=6
TX-00010017-0000051f mode=6
TX-00010000-0000051b mode=6
TX-00010014-0000051f mode=6
TX-0001001c-0000051d mode=6
TX-0001001b-0000051e mode=6
TX-00010019-0000051f mode=6
TX-00010004-0000051f mode=6
TX-0001001f-0000051f mode=6
TX-00010016-0000051e mode=6
TX-0001000a-0000051e mode=6
TX-0001000c-0000051f mode=6
TX-00010012-0000051e mode=6
TX-0001000f-0000051f mode=6
TX-00010007-0000051f mode=6
TX-00010008-0000051f mode=6
TX-00010015-0000051f mode=6
TX-00010003-00000520 mode=6
TX-00010002-0000051e mode=6
TX-00010003-00000520 mode=6
TX-00010002-0000051e mode=6
TX-00010021-0000051e mode=6
TX-0001001d-0000051e mode=6
TX-00010009-00000520 mode=6
TX-0001000e-0000051f mode=6
TX-00010011-0000051f mode=6
TX-00010020-00000520 mode=6
TX-00010001-00000520 mode=6
TX-00010006-00000520 mode=6
TX-00010018-0000051f mode=6
TX-00010005-0000051b mode=6
TX-00010010-0000051f mode=6
TX-0001001e-00000520 mode=6
TX-0001000b-00000520 mode=6
TX-0001000d-00000520 mode=6
TX-00010017-00000520 mode=6
TX-00010000-0000051c mode=6
TX-00010014-00000520 mode=6
TX-0001001c-0000051e mode=6
TX-0001001b-0000051f mode=6
TX-00010019-00000520 mode=6
TX-00010004-00000520 mode=6
TX-0001001f-00000520 mode=6
TX-00010016-0000051f mode=6
TX-0001000a-0000051f mode=6
TX-0001000c-00000520 mode=6
TX-00010012-0000051f mode=6
TX-0001000f-00000520 mode=6
TX-00010007-00000520 mode=6
TX-00010008-00000520 mode=6
TX-00010015-00000520 mode=6
TX-00010003-00000521 mode=6
TX-00010002-0000051f mode=6
TX-00010021-0000051f mode=6
TX-0001001d-0000051f mode=6
TX-00010009-00000521 mode=6
TX-0001000e-00000520 mode=6
TX-00010011-00000520 mode=6
TX-00010020-00000521 mode=6
TX-00010001-00000521 mode=6
TX-00010006-00000521 mode=6
TX-00010018-00000520 mode=6
TX-00010005-0000051c mode=6
TX-00010010-00000520 mode=6
TX-0001001e-00000521 mode=6
TX-0001000b-00000521 mode=6
TX-0001000d-00000521 mode=6
TX-00010017-00000521 mode=6
TX-00010000-0000051d mode=6
TX-00010014-00000521 mode=6
TX-0001001c-0000051f mode=6
TX-0001001b-00000520 mode=6
TX-00010019-00000521 mode=6
TX-00010004-00000521 mode=6
TX-0001001f-00000521 mode=6
TX-00010016-00000520 mode=6
TX-0001000a-00000520 mode=6
TX-0001000c-00000521 mode=6
TX-00010012-00000520 mode=6
TX-0001000f-00000521 mode=6
TX-00010007-00000521 mode=6
TX-00010008-00000521 mode=6
TX-00010015-00000521 mode=6
TX-00010003-00000522 mode=6
TX-00010002-00000520 mode=6
TX-00010021-00000520 mode=6
TX-0001001d-00000520 mode=6
TX-00010009-00000522 mode=6
TX-0001000e-00000521 mode=6
TX-00010011-00000521 mode=6
TX-00010020-00000522 mode=6
TX-00010001-00000522 mode=6
TX-00010006-00000522 mode=6
TX-00010018-00000521 mode=6
TX-00010005-0000051d mode=6
TX-00010010-00000521 mode=6
TX-0001001e-00000522 mode=6
TX-0001000b-00000522 mode=6
TX-0001000d-00000522 mode=6
TX-00010017-00000522 mode=6
TX-00010000-0000051e mode=6
TX-00010014-00000522 mode=6
TX-0001001c-00000520 mode=6
TX-0001001b-00000521 mode=6
TX-00010019-00000522 mode=6
TX-00010004-00000522 mode=6
TX-0001001f-00000522 mode=6
TX-00010016-00000521 mode=6
TX-0001000a-00000521 mode=6
TX-0001000c-00000522 mode=6
TX-00010012-00000521 mode=6
TX-0001000f-00000522 mode=6
TX-00010007-00000522 mode=6
TX-00010008-00000522 mode=6
TX-00010015-00000522 mode=6
TX-00010003-00000523 mode=6
TX-00010002-00000521 mode=6
TX-00010021-00000521 mode=6
TX-0001001d-00000521 mode=6
TX-00010009-00000523 mode=6
TX-0001000e-00000522 mode=6
TX-00010011-00000522 mode=6
TX-00010020-00000523 mode=6
TX-00010001-00000523 mode=6
TX-00010006-00000523 mode=6
TX-00010018-00000522 mode=6
TX-00010005-0000051e mode=6
TX-00010010-00000522 mode=6
TX-0001001e-00000523 mode=6
TX-0001000b-00000523 mode=6
TX-0001000d-00000523 mode=6
TX-00010017-00000523 mode=6
TX-00010000-0000051f mode=6
TX-00010014-00000523 mode=6
TX-0001001c-00000521 mode=6
TX-0001001b-00000522 mode=6
TX-00010019-00000523 mode=6
TX-00010004-00000523 mode=6
TX-0001001f-00000523 mode=6
TX-00010016-00000522 mode=6
TX-0001000a-00000522 mode=6
TX-0001000c-00000523 mode=6
TX-00010012-00000522 mode=6

WRI$_OPTSTAT_IND_HISTORY:
TX-0001000f-00000523 mode=6
IND$:
TX-00010007-00000523 mode=6
TX-0004000b-00000795 mode=6
TX-0007000e-00000738 mode=6
TX-0008000e-00000604 mode=6

其他锁 (系统锁) 的记录:
CU-XXXXXXX-00000000 mode=6    Cursor bind 游标绑定锁 多个
DL-00015bb4-00000000 mode=3  指向 TEXT_T1
DL-00015bd2-00000000 mode=3  指向临时对象 SYS_JOURNAL_89041
ZH-00015bd2-00000001 mode=6  指向临时对象 SYS_JOURNAL_89041
ZH-00015bd2-00000002 mode=6  指向临时对象 SYS_JOURNAL_89041
ZH-00015bd2-00000003 mode=6  指向临时对象 SYS_JOURNAL_89041
ZH-00015bd2-00000004 mode=6  指向临时对象 SYS_JOURNAL_89041
OD-00015bb4-00000000 mode=4  指向 TEXT_T1
TT-0000000e-00000010 mode=4    指向 SEG$
TS-0000000e-01c00082 mode=6    指向 SEG$
TS-0000000e-0186978a mode=6    指向 SEG$
TS-0000000e-01c00083 mode=6    指向 SEG$
FB-0000000e-01c00080 mode=6    指向 SEG$
SE-0000002d-00000005 mode=4
HW-0000000e-0186978a mode=6  指向 SEG$
RO-00010013-00000001 mode=6
CR-00010013-00000002 mode=6
CR-00010013-00000001 mode=6
CR-00010013-00000002 mode=6
MR-00000002-00000002 mode=4
TO-000137d9-00000001 mode=3  指向 SDO_GEOR_DDL__TABLE$$
TO-000137d9-00000002 mode=3  指向 SDO_GEOR_DDL__TABLE$$
SE-0000002d-00000005 mode=4

五、Offline rebuild 和 Online rebuild 索引锁的跟踪对比
1.alter index rebuild

Session 1
SQL> drop index idx_text01;
SQL> create index ind_text02 on text_t1(object_id);
SQL> update text_t1 set OBJECT_ID=666 where OBJECT_ID<1000;
510464 rows updated.
SQL> alter session set events ‘10704 trace name context forever, level 12’;
SQL> alter index ind_text02 rebuild;

Session 2
观察 V$lock
SQL> select * from v$lock where sid=45;
ADDR KADDR SID TY        ID1  ID2  LMODE    REQUEST CTIME    BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
00000000BED5CBB8 00000000BED5CC10  45 OD      89050    0      6  0    60 0
00000000BED5CC98 00000000BED5CCF0  45 DL      89012    0      3  0    60 0
00000000BED5CE58 00000000BED5CEB0  45 AE        100    0      4  0  257 0
00000000BED5D650 00000000BED5D6A8  45 DL      89012    0      3  0    60 0
00007FCD9BB78810 00007FCD9BB78870  45 TM      89012    0      4  0    60 0
00000000BBE04B08 00000000BBE04BB8  45 TS 14  25855426      6  0    25 0
00000000BBED43E8 00000000BBED4460  45 TX    131098 1519      6  0    25 0

SQL> select object_name from dba_objects where object_id=89050;
OBJECT_NAME
——————————————————————————————————————————–
IND_TEXT02    6 号 OD 锁指向 rebuild 索引 IND_TEXT02

Session 1
SQL> alter session set events ‘10704 trace name context off’;

Linux
[oracle@localhost trace]$ more orcl_ora_43002.trc

TM-00015bb4-00000000 mode=4    指向 TEXT_T1  开头持有结尾释放。持有 4 号 TM 锁,会阻塞 DML 操作

TM-0000000e-00000000 mode=3    指向 SEG$ 持有再释放,重复两次
TM-000001eb-00000000 mode=3    指向 WRI$_OPTSTAT_IND_HISTORY
TM-000001c9-00000000 mode=3    指向 CACHE_STATS_1$
TM-000001c7-00000000 mode=3    指向 IND_STATS$ 
TM-00000013-00000000 mode=3    指向 IND$
TM-00000012-00000000 mode=3    指向 OBJ$

Alter index rebuild 执行中会有 4 号 TM 锁去锁定 TEXT_T1 表,堵塞其他事务的 DML 操作。同时以 3 号 TM 锁去锁定 SEG$,WRI$_OPTSTAT_IND_HISTORY,CACHE_STATS_1$,IND_STATS$,IND$,OBJ$ 数据字典表,对其进行 DML(Insert)操作来更新维护数据字典表信息。

 

TX 锁的记录以及加到哪个对象上(按时间先后):
TEXT_T1:
TX-00010011-0000052b mode=6
SEG$
TX-00010006-0000052c mode=6
TX-00010001-0000052c mode=6
TX-00010005-00000527 mode=6
TX-0002001a-000005ef mode=6
TX-0002001f-000005f4 mode=6
TX-00020016-000005f4 mode=6
TX-00020019-000005f4 mode=6
TX-0002001e-000005f4 mode=6
TX-0002000f-000005f3 mode=6
TX-00020018-000005f1 mode=6
TX-00020013-000005f4 mode=6
TX-00020012-000005f4 mode=6
TX-00020002-000005f4 mode=6
TX-00020020-000005f4 mode=6
TX-00020015-000005f5 mode=6
TX-0002001d-000005f4 mode=6
TX-00020004-000005f5 mode=6
TX-00020007-000005f6 mode=6
TX-00020003-000005f6 mode=6
TX-00020014-000005eb mode=6
TX-00020009-000005f4 mode=6
TX-00020011-000005f4 mode=6
TX-00020021-000005f5 mode=6
TX-00020005-000005f4 mode=6
TX-00020006-000005f6 mode=6
TX-0002000a-000005f6 mode=6
TX-00020008-000005f5 mode=6
TX-0002001b-000005f5 mode=6
TX-00020001-000005f5 mode=6
TX-00020010-000005f5 mode=6
TX-0002000d-000005f5 mode=6
TX-00020000-000005f7 mode=6
TX-0002001c-000005f5 mode=6
TX-0002000c-000005f4 mode=6
TX-0002000b-000005f6 mode=6
TX-00020017-000005f5 mode=6
TX-0002000e-000005f5 mode=6
TX-0002001f-000005f5 mode=6
TX-00020016-000005f5 mode=6
TX-00020019-000005f5 mode=6
TX-0002001e-000005f5 mode=6
TX-0002000f-000005f4 mode=6
TX-00020018-000005f2 mode=6
TX-00020012-000005f5 mode=6
TX-00020002-000005f5 mode=6
TX-00020020-000005f5 mode=6
TX-00020015-000005f6 mode=6
TX-0002001d-000005f5 mode=6
TX-00020004-000005f6 mode=6
TX-00020007-000005f7 mode=6
TX-00020003-000005f7 mode=6
TX-00020014-000005ec mode=6
TX-00020009-000005f5 mode=6
TX-00020011-000005f5 mode=6
TX-00020021-000005f6 mode=6
TX-00020005-000005f5 mode=6
TX-00020006-000005f7 mode=6
TX-0002000a-000005f7 mode=6
TX-00020008-000005f6 mode=6
TX-0002001b-000005f6 mode=6
TX-00020001-000005f6 mode=6
TX-00020010-000005f6 mode=6
TX-0002000d-000005f6 mode=6
TX-00020000-000005f8 mode=6
TX-0002001c-000005f6 mode=6
TX-0002000c-000005f5 mode=6
TX-0002000b-000005f7 mode=6
TX-00020017-000005f6 mode=6
TX-0002000e-000005f6 mode=6
TX-0002001f-000005f6 mode=6
TX-00020016-000005f6 mode=6
TX-00020019-000005f6 mode=6
TX-0002001e-000005f6 mode=6
TX-0002000f-000005f5 mode=6
TX-00020018-000005f3 mode=6
TX-00020013-000005f6 mode=6
TX-00020012-000005f6 mode=6
TX-00020002-000005f6 mode=6
TX-00020020-000005f6 mode=6
TX-00020015-000005f7 mode=6
TX-0002001d-000005f6 mode=6
TX-00020004-000005f7 mode=6
TX-00020007-000005f8 mode=6
TX-00020003-000005f8 mode=6
TX-00020014-000005ed mode=6
TX-00020009-000005f6 mode=6
TX-00020011-000005f6 mode=6
TX-00020021-000005f7 mode=6
TX-00020005-000005f6 mode=6
TX-00020006-000005f8 mode=6
TX-0002000a-000005f8 mode=6
TX-00020008-000005f7 mode=6
TX-0002001b-000005f7 mode=6
TX-00020001-000005f7 mode=6
TX-00020010-000005f7 mode=6
TX-0002000d-000005f7 mode=6
TX-00020000-000005f9 mode=6
TX-0002001c-000005f7 mode=6
TX-0002000c-000005f6 mode=6
TX-0002000b-000005f8 mode=6
TX-00020017-000005f7 mode=6
TX-0002000e-000005f7 mode=6
TX-0002001f-000005f7 mode=6
TX-00020016-000005f7 mode=6
TX-00020019-000005f7 mode=6
TX-0002001e-000005f7 mode=6
TX-0002000f-000005f6 mode=6
TX-00020018-000005f4 mode=6
TX-00020013-000005f7 mode=6
TX-00020012-000005f7 mode=6
TX-00020002-000005f7 mode=6
TX-00020020-000005f7 mode=6
TX-00020015-000005f8 mode=6
TX-0002001d-000005f7 mode=6
TX-00020004-000005f8 mode=6
TX-00020007-000005f9 mode=6
TX-00020003-000005f9 mode=6
TX-00020014-000005ee mode=6
TX-00020009-000005f7 mode=6
TX-00020011-000005f7 mode=6
TX-00020021-000005f8 mode=6
TX-00020005-000005f7 mode=6
TX-00020006-000005f9 mode=6
TX-0002000a-000005f9 mode=6
TX-00020008-000005f8 mode=6
TX-0002001b-000005f8 mode=6
TX-00020001-000005f8 mode=6
TX-00020010-000005f8 mode=6
TX-0002000d-000005f8 mode=6
TX-00020000-000005fa mode=6
TX-0002001c-000005f8 mode=6
TX-0002000c-000005f7 mode=6
TX-0002000b-000005f9 mode=6
TX-00020017-000005f8 mode=6
TX-0002000e-000005f8 mode=6
TX-0002001f-000005f8 mode=6
TX-00020016-000005f8 mode=6
TX-00020019-000005f8 mode=6
TX-0002001e-000005f8 mode=6
TX-0002000f-000005f7 mode=6
TX-00020018-000005f5 mode=6
TX-00020013-000005f8 mode=6
TX-00020012-000005f8 mode=6
TX-00020002-000005f8 mode=6
TX-00020020-000005f8 mode=6
TX-00020015-000005f9 mode=6
TX-0002001d-000005f8 mode=6
TX-00020004-000005f9 mode=6
TX-00020007-000005fa mode=6
TX-00020003-000005fa mode=6
TX-00020014-000005ef mode=6
TX-00020009-000005f8 mode=6
TX-00020011-000005f8 mode=6
TX-00020021-000005f9 mode=6
TX-00020005-000005f8 mode=6
TX-00020006-000005fa mode=6
TX-0002000a-000005fa mode=6
TX-00020008-000005f9 mode=6
TX-0002001b-000005f9 mode=6
TX-00020001-000005f9 mode=6
TX-00020010-000005f9 mode=6
TX-0002000d-000005f9 mode=6
TX-00020000-000005fb mode=6
TX-0002001c-000005f9 mode=6
TX-0002000c-000005f8 mode=6
TX-0002000b-000005fa mode=6
TX-00020017-000005f9 mode=6
TX-0002000e-000005f9 mode=6
TX-0002001f-000005f9 mode=6
TX-00020016-000005f9 mode=6
TX-00020019-000005f9 mode=6
TX-0002001e-000005f9 mode=6
WRI$_OPTSTAT_IND_HISTORY
TX-0002000f-000005f8 mode=6

SEG$
TX-00070010-0000074a mode=6

其他锁 (系统锁) 的记录:
CU-XXXXXXX-00000000 mode=6    Cursor bind 游标绑定锁 多个
DL-00015bb4-00000000 mode=3    指向 TEXT_T1
OD-00015bda-00000000 mode=6    指向 IND_TEXT02
FB-0000000e-018a85c0 mode=6    指向 SEG$
TT-00000003-00000010 mode=4    指向 I_OBJ#
TS-0000000e-018a85c2 mode=6    指向 SEG$
TS-0000000e-01c00082 mode=6    指向 SEG$
TS-0000000e-01c00083 mode=6    指向 SEG$
HW-0000000e-018a85c2 mode=6    指向 SEG$
DL-00015bb4-00000000 mode=3    指向 TEXT_T1
PW-00000001-00000000 mode=4
MR-00000002-00000002 mode=4

2.Alter index rebuild online
Session 1
SQL> update text_t1 set OBJECT_ID=321 where OBJECT_ID=666;
SQL> alter session set events ‘10704 trace name context forever, level 12’;
SQL> alter index ind_text02 rebuild online;

Session 2
SQL> select * from v$lock where sid=45;
ADDR KADDR SID TY        ID1  ID2  LMODE    REQUEST CTIME    BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
00000000BED5CE58 00000000BED5CEB0  45 DL      89012    0      3  0    73 0
00000000BED5D2B8 00000000BED5D310  45 TO      79833    1      3  0 48673 0
00000000BED5D398 00000000BED5D3F0  45 OD      89012    0      4  0    73 0
00000000BED5D558 00000000BED5D5B0  45 AE        100    0      4  0 48807 0
00000000BED5D650 00000000BED5D6A8  45 DL      89012    0      3  0    73 0
00000000BED5D730 00000000BED5D788  45 OD      89050    0      6  0    73 0
00007F4A6A48A3A8 00007F4A6A48A408  45 TM      89012    0      2  0    73 0
00007F4A6A48A3A8 00007F4A6A48A408  45 TM      89065    0      4  0    73 0
00000000BBE04B08 00000000BBE04BB8  45 TS 14  29360362      6  0    25 0
00000000BBEE55A8 00000000BBEE5620  45 TX    196637 1845      6  0    25 0
00000000BBF33FB8 00000000BBF34030  45 TX    196630 1844      6  0    73 0

SQL> select object_name from dba_objects where object_id in (89012,89065,89050);
OBJECT_NAME
——————————————————————————————————————————–
TEXT_T1                              2 号 TM 锁指向 TEXT_T1;
SYS_JOURNAL_89050          4 号 TM 锁指向临时对象 SYS_JOURNAL_89050,执行结束前释放锁和删除对象
IND_TEXT02                        6 号 OD 锁指向 rebuild 索引 IND_TEXT02

Session 1
SQL> alter session set events ‘10704 trace name context off’;

Linux
[oracle@localhost trace]$ more orcl_ora_43002.trc

TM-00015bb4-00000000 mode=2      指向 TEXT_T1  开头持有结尾释放。持有 2 号 TM 锁,不阻塞 DML 操作

TM-00000012-00000000 mode=3      指向 OBJ$    持有再释放 重复三次
TM-0000001c-00000000 mode=3      指向 CON$    持有再释放 重复两次
TM-00015be5-00000000 mode=4      指向 SYS_JOURNAL_89050    持有再释放 重复两次
TM-0000000e-00000000 mode=3      指向 SEG$    持有再释放 重复十一次
TM-00000014-00000000 mode=3      指向 ICOL$  持有再释放 重复两次
TM-00000013-00000000 mode=3      指向 IND$    持有再释放 重复四次
TM-00000004-00000000 mode=3      指向 TAB$    末尾持有再释放 重复两次
TM-00000015-00000000 mode=3      指向 COL$    持有再释放 重复两次
TM-00000020-00000000 mode=3      指向 CCOL$  持有再释放 重复三次
TM-0000001f-00000000 mode=3      指向 CDEF$  持有再释放 重复四次
TM-000001c7-00000000 mode=3      指向 IND_STATS$  持有再释放 重复三次
TM-00000092-00000000 mode=3      指向 IND_ONLINE$ 持有再释放 重复三次
TM-000000b4-00000000 mode=3      指向 INDREBUILD$ 持有再释放 重复三次
TM-000001eb-00000000 mode=3      指向 WRI$_OPTSTAT_IND_HISTORY
TM-000137d9-00000000 mode=3      指向 SDO_GEOR_DDL__TABLE$$  持有再释放 重复两次
TM-000000db-00000000 mode=3      指向 OBJECT_USAGE
TM-000001c9-00000000 mode=3      指向 CACHE_STATS_1$
TM-00000089-00000000 mode=3      指向 COM$
TM-000001c0-00000000 mode=3      指向 HIST_HEAD$
TM-000000b6-00000000 mode=3      指向 COMPRESSION$
TM-00000282-00000000 mode=3      指向 SUMDELTA$
TM-0000027f-00000000 mode=3      指向 SUMPARTLOG$
TM-00000286-00000000 mode=3      指向 SNAP_LOADERTIME$
TM-000000e1-00000000 mode=3      指向 IDL_UB1$
TM-000000e2-00000000 mode=3      指向 IDL_CHAR$
TM-000000e3-00000000 mode=3      指向 IDL_UB2$
TM-000000e4-00000000 mode=3      指向 IDL_SB4$
TM-000000f2-00000000 mode=3      指向 NCOMP_DLL$
TM-0000003d-00000000 mode=3      指向 OBJAUTH$
TM-00000072-00000000 mode=3      指向 ICOLDEP$
TM-00000097-00000000 mode=3      指向 JIJOIN$
TM-0000009b-00000000 mode=3      指向 JIREFRESHSQL$
TM-0000007b-00000000 mode=3      指向 ECOL$
TM-00000053-00000000 mode=3      指向 COLTYPE$
TM-00000056-00000000 mode=3      指向 SUBCOLTYPE$
TM-00000058-00000000 mode=3      指向 NTAB$
TM-00000050-00000000 mode=3      指向 LOB$
TM-0000005c-00000000 mode=3      指向 REFCON$
TM-0000005f-00000000 mode=3      指向 OPQTYPE$

Alter index rebuild online 会在 TEXT_T1 表持有 2 号 TM 锁,重建索引完成时释放,期间不会堵塞 DML 操作,也保证了表的并发性能;
执行中会创建一个 SYS_JOURNAL_XXXXX 系统临时日志表,用来存放 index rebuild 过程中索引变化的记录日志,持有 4 号 TM 锁,在 TEXT_T1 的 2 号 TM 锁释放之前进行释放。

 

TX 锁的记录以及加到哪个对象上(按时间先后):
OBJ$:
TX-0005000c-00000a21 mode=6
TX-00050014-00000a22 mode=6
TX-0005000c-00000a21 mode=6
TX-00050014-00000a22 mode=6

CON$:
TX-00050021-00000a21 mode=6

SYS_JOURNAL_89050:
TX-0005001f-00000a1c mode=6
TX-00050004-000008dc mode=6

ICOL$:
TX-00050012-00000a1b mode=6
TX-00050017-00000a21 mode=6

CCOL$:
TX-00050006-00000a21 mode=6
TX-00050016-00000a1c mode=6

SYS_JOURNAL_89050:
TX-00020014-000005fe mode=6
TX-00020009-00000607 mode=6

SEG$:
TX-00020011-00000607 mode=6
TX-00020021-00000608 mode=6
TX-0002001d-00000607 mode=6
TX-00020005-00000607 mode=6
TX-00020006-00000609 mode=6
TX-0002000a-00000609 mode=6
TX-00020008-00000608 mode=6
TX-0002001b-00000608 mode=6
TX-00020001-00000608 mode=6
TX-00020010-00000608 mode=6
TX-0002000d-00000608 mode=6
TX-00020000-0000060a mode=6
TX-0002001c-00000608 mode=6
TX-0002000c-00000607 mode=6
TX-0002000c-00000608 mode=6
TX-0002000b-0000060a mode=6
TX-00020017-00000609 mode=6
TX-0002000e-00000609 mode=6
TX-0002001f-00000609 mode=6
TX-00020016-00000609 mode=6
TX-00020019-00000609 mode=6
TX-0002001e-00000609 mode=6
TX-0002000f-00000608 mode=6
TX-0002001a-000005ff mode=6
TX-00020018-00000606 mode=6
TX-00020013-00000609 mode=6
TX-00020012-00000609 mode=6
TX-00020002-00000609 mode=6
TX-00020020-00000609 mode=6
TX-00020015-0000060a mode=6
TX-00020007-0000060b mode=6
TX-00020003-0000060b mode=6
TX-00020011-00000609 mode=6
TX-00020021-0000060a mode=6
TX-00020009-00000609 mode=6
TX-00020005-00000609 mode=6
TX-00020006-0000060b mode=6
TX-0002000a-0000060b mode=6
TX-00020008-0000060a mode=6
TX-0002001b-0000060a mode=6
TX-00020001-0000060a mode=6
TX-00020010-0000060a mode=6
TX-0002000d-0000060a mode=6
TX-00020000-0000060c mode=6
TX-0002001c-0000060a mode=6
TX-0002000c-00000609 mode=6
TX-00020017-0000060a mode=6
TX-0002000e-0000060a mode=6
TX-0002001f-0000060a mode=6
TX-00020016-0000060a mode=6
TX-00020019-0000060a mode=6
TX-0002001e-0000060a mode=6
TX-0002000f-00000609 mode=6
TX-0002001a-00000600 mode=6
TX-00020018-00000607 mode=6
TX-00020013-0000060a mode=6
TX-00020012-0000060a mode=6
TX-00020002-0000060a mode=6
TX-00020015-0000060b mode=6
TX-00020004-0000060b mode=6
TX-00020007-0000060c mode=6
TX-00020003-0000060c mode=6
TX-00020011-0000060a mode=6
TX-00020021-0000060b mode=6
TX-00020009-0000060a mode=6
TX-00020006-0000060c mode=6
TX-0002000a-0000060c mode=6
TX-00020008-0000060b mode=6
TX-0002001b-0000060b mode=6
TX-00020001-0000060b mode=6
TX-00020010-0000060b mode=6
TX-0002000d-0000060b mode=6
TX-00020000-0000060d mode=6
TX-0002001c-0000060b mode=6
TX-0002000c-0000060a mode=6
TX-0002000b-0000060c mode=6
TX-00020017-0000060b mode=6
TX-0002000e-0000060b mode=6
TX-0002001f-0000060b mode=6
TX-00020016-0000060b mode=6
TX-00020019-0000060b mode=6
TX-0002001e-0000060b mode=6
TX-0002000f-0000060a mode=6
TX-0002001a-00000601 mode=6
TX-00020018-00000608 mode=6
TX-00020013-0000060b mode=6
TX-00020012-0000060b mode=6
TX-00020002-0000060b mode=6
TX-00020020-0000060b mode=6
TX-00020015-0000060c mode=6
TX-00020004-0000060c mode=6
TX-00020007-0000060d mode=6
TX-00020003-0000060d mode=6
TX-00020011-0000060b mode=6
TX-00020021-0000060c mode=6
TX-00020009-0000060b mode=6
TX-00020005-0000060b mode=6
TX-00020006-0000060d mode=6
TX-0002000a-0000060d mode=6
TX-00020008-0000060c mode=6
TX-0002001b-0000060c mode=6
TX-00020001-0000060c mode=6
TX-00020010-0000060c mode=6
TX-0002000d-0000060c mode=6
TX-0002000d-0000060c mode=6
TX-00020000-0000060e mode=6
TX-0002001c-0000060c mode=6
TX-0002000c-0000060b mode=6
TX-0002000b-0000060d mode=6
TX-00020017-0000060c mode=6
TX-0002000e-0000060c mode=6
TX-0002001f-0000060c mode=6
TX-00020016-0000060c mode=6
TX-00020019-0000060c mode=6
TX-0002001e-0000060c mode=6
TX-0002000f-0000060b mode=6
TX-0002001a-00000602 mode=6
TX-00020018-00000609 mode=6
TX-00020013-0000060c mode=6
TX-00020012-0000060c mode=6
TX-00020002-0000060c mode=6
TX-00020020-0000060c mode=6
TX-00020015-0000060d mode=6
TX-00020004-0000060d mode=6
TX-00020007-0000060e mode=6
TX-00020003-0000060e mode=6
TX-00020011-0000060c mode=6
TX-00020021-0000060d mode=6
TX-00020009-0000060c mode=6
TX-00020005-0000060c mode=6
TX-00020006-0000060e mode=6
TX-0002000a-0000060e mode=6
TX-00020008-0000060d mode=6
TX-0002001b-0000060d mode=6
TX-00020001-0000060d mode=6
TX-00020010-0000060d mode=6
TX-0002000d-0000060d mode=6
TX-00020000-0000060f mode=6
TX-0002001c-0000060d mode=6
TX-0002000c-0000060c mode=6

SDO_GEOR_DDL__TABLE$$:
TX-0005001c-00000a1e mode=6

SEG$:
TX-000a001b-000004ca mode=6
TX-0001001f-0000053b mode=6
TX-0006001a-00000a2e mode=6
TX-00050010-00000a21 mode=6

其他锁 (系统锁) 的记录:
CU-XXXXXXX-00000000 mode=6    Cursor bind 游标绑定锁 多个
OD-00015bda-00000000 mode=6    指向 IND_TEXT02
TT-0000000e-00000010 mode=4    指向 SEG$
TT-00000003-00000010 mode=4    指向 I_OBJ#
DL-00015bb4-00000000 mode=3    指向 TEXT_T1
DL-00015be9-00000000 mode=3    指向 SYS_JOURNAL_89050 临时对象
ZH-00015be9-00000001 mode=6    指向 SYS_JOURNAL_89050 临时对象
ZH-00015be9-00000002 mode=6    指向 SYS_JOURNAL_89050 临时对象
ZH-00015be9-00000003 mode=6    指向 SYS_JOURNAL_89050 临时对象
ZH-00015be9-00000004 mode=6    指向 SYS_JOURNAL_89050 临时对象
TS-0000000e-01c000ea mode=6    指向 SEG$
TS-0000000e-01893e02 mode=6    指向 SEG$
HW-0000000e-01c00082 mode=6    指向 SEG$
RO-00010013-00000001 mode=6
RO-00010013-00000002 mode=6
CR-00010013-00000001 mode=6
CR-00010013-00000002 mode=6

关于不同索引语句产生的锁的总结:
offline create index /rebuild 和 online create index /rebuild 的不同点
1.offline 始终持有 4 号 TM 锁来锁定表,堵塞其他事务 DML 操作;online 持有 2 号 TM 锁来锁定表,不会堵塞其他事务的 DML 操作,并发性能更好;
2.online 会自动产生 SYS_JOURNAL_XXXX 的系统临时表来记录日志;
3.online 时持有的 3 号 TM 锁相比 offline 时要复杂得多。

create index 和 alter index rebuild 的不同点
1.create index/online 是新建索引,当 table 上没有索引时,需要新建;
2.alter index rebuild/online 是重建索引,当索引中删除更改的索引中的被删除项占总的项数的百分比
3. 两者维护的数据字典表数量有一定的差异 

 

六、DROP/TRUNCATE/CREATE Table 锁的跟踪分析
1.CREATE TABLE
create table test_t3 as select * from text;

TM-0000000e-00000000  mode=3  指向 SEG$
TM-00000004-00000000  mode=3  指向 TAB$
TM-00000015-00000000  mode=3  指向 COL$

create table 时被操作表本身不持有锁,只是锁了几个数据字典表去更新

2.DROP TABLE
insert into test_t1 select * from text;
drop table test_t1

TM-00015b62-00000000 mode=6  指向 TEXT_T1  开始持有最后释放

TM-000137d9-00000000 mode=3    指向 SDO_GEOR_DDL__TABLE$$
TM-00000282-00000000 mode=3    指向 SUMDELTA$ 
TM-0000027f-00000000 mode=3    指向 SUMPARTLOG$
TM-00000286-00000000 mode=3    指向 SNAP_LOADERTIME$
TM-0000008e-00000000 mode=3    指向 RECYCLEBIN$
TM-000000e1-00000000 mode=3    指向 IDL_UB1$ 
TM-000000e2-00000000 mode=3    指向 IDL_CHAR$
TM-000000e3-00000000 mode=3    指向 IDL_UB2$
TM-000000e4-00000000 mode=3    指向 IDL_SB4$
TM-000000e5-00000000 mode=3    指向 ERROR$
TM-00000012-00000000 mode=3    指向 OBJ$
TM-000137cf-00000000 mode=3    指向 SDO_GEOR_SYSDATA_TABLE
TM-000139b3-00000000 mode=3    指向 SDO_TIN_PC_SYSDATA_TABLE
TM-000139b1-00000000 mode=3    指向 SDO_TIN_PC_SEQ 
TM-000137d9-00000000 mode=3    再次指向 SDO_GEOR_DDL__TABLE$$
(以上 TM 锁全部释放后)
TM-00000012-00000000 mode=3    指向 OBJ$ 
TM-0000000e-00000000 mode=3    指向 SEG$

drop table 持有 6 号 TM 排他锁,禁止其他事务执行任何 DML 语句。

2.TRUNCATE TABLE
truncate table test_t2

TM-00015b46-00000000  mode=6  指向 TEST_T2 开始持有最后释放
TM-0000028a-00000000  mode=3  指向 MLOG$
TM-000001e4-00000000  mode=3  指向 MON_MODS$
TM-00000047-00000000  mode=3  指向 SUPEROBJ$
TM-000001c5-00000000  mode=3  指向 TAB_STATS$
TM-00000004-00000000  mode=3  指向 TAB$ 
TM-0000000e-00000000  mode=3  指向 SEG$
TM-00000012-00000000  mode=3  指向 OBJ$
(以上 TM 锁全部释放后)
TM-0000000e-00000000  mode=3  指向 SEG$ 

truncate table 持有 6 号 TM 排他锁,禁止其他事务执行任何 DML 语句。

七、总结

锁模式锁描述解释sql 操作
0none   
1null 预留模式,仅起标志作用 
2Row-S 行共享(RS)行级共享锁,其他对象只能查询这些数据行Create index online,Alter index rebuild online
3Row-X 行专用(RX)行级排他锁,在提交前不允许做 DML 操作insert into table,update table,delete from xxx
4Share 共享锁(S)共享锁Create index,Alter index rebuild
5S/Row-X 共享行专用(SRX)共享行级排他锁 
6exclusive 专用(X)排他锁drop table,truncate tabledrop index

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-07/145535.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计35901字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中