数据库版本信息:
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
创建索引的锁行为
SESSION1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
27
SESSION2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
125
SESSION1:
SQL> insert into lixia.test values(4,'test4');
1 row created.
SESSION3:
查看锁信息
SQL> select sid,type,id1,id2,lmode from v$lock where sid in (125,27);
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
125 AE 100 0 4
125 TO 79833 1 3
27 AE 100 0 4
27 TM 170376 0 3
27 TX 589839 5469 6
SESSION2(SID 为27)的会话持有排他模式的TX锁和RX(行级排他锁)模式的表锁。在RX模式下,允许其他的事务通过DML语句修改相同表里的其他数据行,但是不允许
其他事务对相同的表添加排他锁(X锁)
SESSION2:
SQL> alter session set tracefile_identifier='create_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index lixia.idx_test_id on lixia.test(id);
create index lixia.idx_test_id on lixia.test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set events '10046 trace name context off';
创建索引索引失败,提示资源忙。
查看10046追踪文件,发现在创建索引时需要先对表添加表级共享锁(S锁)。
=====================
PARSING IN CURSOR #139950258093056 len=48 dep=1 uid=0 oct=26 lid=0 tim=1445518323040883 hv=439152460 ad='884c06f8' sqlid='6c12yxnd2tvuc'
LOCK TABLE "LIXIA"."TEST" IN SHARE MODE NOWAIT
END OF STMT
PARSE #139950258093056:c=0,e=274,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445518323040882
EXEC #139950258093056:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445518323040951
ERROR #139950258093056:err=54 tim=1445518323040961
CLOSE #139950258093056:c=0,e=3,dep=1,type=0,tim=1445518323040989
=====================
PARSE ERROR #139950256770136:len=48 dep=0 uid=0 oct=9 lid=0 tim=1445518323041052 err=54
create index lixia.idx_test_id on lixia.test(id)
SESSION1:
提交事务
SQL> commit;
Commit complete.
SESSION2:
创建索引成功。
SQL> create index lixia.idx_test_id on lixia.test(id);
Index created.
======================================================================
REBUILD 索引的锁行为
SESSION1:
SQL> insert into lixia.test values(5,'test5');
1 row created.
SESSION2:
查看锁信息。
SQL> select sid,type,id1,id2,lmode from v$lock where sid in (125,27);
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
125 AE 100 0 4
125 TO 79833 1 3
27 AE 100 0 4
27 TM 170376 0 3
27 TX 589848 5472 6
SESSION2(SID 为27)的会话持有排他模式的TX锁和RX(行级排他锁)模式的表锁。
SESSION2:
SQL> ALTER INDEX lixia.idx_test_id REBUILD;
ALTER INDEX lixia.idx_test_id REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set tracefile_identifier='rebuild_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> ALTER INDEX lixia.idx_test_id REBUILD;
ALTER INDEX lixia.idx_test_id REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL>
SQL> alter session set events '10046 trace name context off';
Session altered.
[root@oradb ~]# cd /app/oracle/diag/rdbms/dilas1/dilas1/trace
[root@oradb trace]# more dilas1_ora_2631_rebuild_index.trc
=====================
PARSING IN CURSOR #139950257818784 len=66 dep=1 uid=0 oct=26 lid=0 tim=1445517412599506 hv=4081004650 ad='87735708' sqlid='6fhc8krtmyc3a'
LOCK TABLE FOR INDEX "LIXIA"."IDX_TEST_ID" IN SHARE MODE NOWAIT
END OF STMT
PARSE #139950257818784:c=3998,e=121530,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445517412599506
EXEC #139950257818784:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445517412599572
ERROR #139950257818784:err=54 tim=1445517412599581
CLOSE #139950257818784:c=0,e=2,dep=1,type=0,tim=1445517412599653
=====================
PARSE ERROR #139950257819728:len=37 dep=0 uid=0 oct=9 lid=0 tim=1445517412599682 err=54
ALTER INDEX lixia.idx_test_id REBUILD
查看10046的追踪文件在继续索引REBUILD时有对表添加共享模式的锁。
=====================================================================
删除索引的锁行为
在SESSION2对TEST 表持有TX锁和RX 模式的TM锁的情况下,删除索引。
SQL> alter session set tracefile_identifier='drop_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> drop index lixia.idx_test_id;
drop index lixia.idx_test_id
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set events '10046 trace name context off';
Session altered.
查看10046追踪文件,发现在删除索引时会对表添加排他模式的表锁。
PARSING IN CURSOR #139950258171424 len=70 dep=1 uid=0 oct=26 lid=0 tim=1445517922051712 hv=2912218121 ad='884e9760' sqlid='9q373jyqt9v09'
LOCK TABLE FOR INDEX "LIXIA"."IDX_TEST_ID" IN EXCLUSIVE MODE NOWAIT
END OF STMT
PARSE #139950258171424:c=1000,e=1058,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445517922051711
EXEC #139950258171424:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445517922051774
ERROR #139950258171424:err=54 tim=1445517922051783
CLOSE #139950258171424:c=0,e=2,dep=1,type=0,tim=1445517922051809
=====================
PARSE ERROR #139950258695176:len=28 dep=0 uid=0 oct=10 lid=0 tim=1445517922051824 err=54
drop index lixia.idx_test_id
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21582653/viewspace-1815734/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21582653/viewspace-1815734/