创建索引、删除索引和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

 

 

SESSION2SID 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

 

SESSION2SID 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时有对表添加共享模式的锁。

 

 

 

=====================================================================

删除索引的锁行为

 

SESSION2TEST 表持有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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值