创建索引、删除索引和REBUILD索引的锁行为

数据库版本信息：

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/