*** 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 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 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$ 仅在末尾持有和释放一次