ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sequence可能引发的各种lock/enqueue

sequence可能引发的各种lock/enqueue

原创 Linux操作系统 作者:myownstars 时间:2011-06-08 17:36:05 0 删除 编辑

Oracle将数据字典存于sga中的dictionary cache,而想要修改其中的内容,需要获取row cache lock;

对于sequence,调用的时候可能会遇到以下三种锁:

Row cache lock(nocache): sequence设置为nocache,调用sequence.nextval;

enq:SQ-contention(cache+noorder): 在内存上cache范围内,调用sequence.nextval;期间以SSX模式获得SQ锁;

SV(cache+order) RAC环境,sequence赋予了cache+order属性,调用sequence.nextval;

 

同时创建大量会话时,可能也会遇到SQ锁,因为v$session.audsid(auditing sessionid)的值是利用sequence创建的,oracle创建新会话时,利用sys.AUDSESS$nextval创建AUDSID的值;

 

RAC:

1         sequence(cache + noorder):则各节点会把不同范围的值sequencecache到内存中;

比如拥有两个节点的RAC环境下,创建CACHE值为100 sequence时,1节点会使用1-1002节点会使用101-200

2sequence(cache + order):调用sequence.nextval时,以ssx模式获取SV锁;若获取过程中发生争用,则等待DFS lock handle事件;

 

 

实验:oracle版本11.2.0.2

先创建nocachesequence

create sequence SEQ_TEST_ID

minvalue 1

maxvalue 999999999999999999999999999

start with 1

increment by 1

nocache;

 

--session 1

declare

  v_cnt number := 0;

  v_c number(20) := 0;

begin

  for i in 1..1000000000 loop

    select seq_test_id.nextval into v_c from dual;

    v_cnt := v_cnt + 1;

  end loop;

end;

--session 2

declare

  v_cnt number := 0;

  v_c number(20) := 0;

begin

  for i in 1..1000000000 loop

    select seq_test_id.nextval into v_c from dual;

    v_cnt := v_cnt + 1;

  end loop;

end;

--session 3

SQL> select sid,seq#,event from v$session_wait where event like '%row%';

 

       SID       SEQ# EVENT

---------- ---------- ----------------------------------------------------------------

       637      52263 row cache lock

      3434      52204 row cache lock

 

SQL> select sql_text,s.SID,s.SERIAL#,sw.EVENT from v$sql sql, v$session s, v$session_wait sw where sql.SQL_ID = s.SQL_ID

  2  and s.SID = sw.SID  and sw.event like '%row%';

 

SQL_TEXT                                        SID    SERIAL# EVENT

---------------------------------------- ---------- ---------- ----------------------------------------------------------------

SELECT SEQ_TEST_ID.NEXTVAL FROM DUAL            637      49339 row cache lock

SELECT SEQ_TEST_ID.NEXTVAL FROM DUAL           3434      10079 row cache lock

 

创建cache20

create sequence SEQ_TEST_ID

minvalue 1

maxvalue 999999999999999999999999999

start with 1

increment by 1;

-session 1

declare

  v_cnt number := 0;

  v_c number(20) := 0;

begin

  for i in 1..1000000000 loop

    select seq_test_id.nextval into v_c from dual;

    v_cnt := v_cnt + 1;

  end loop;

end;

-session 2

declare

  v_cnt number := 0;

  v_c number(20) := 0;

begin

  for i in 1..1000000000 loop

    select seq_test_id.nextval into v_c from dual;

    v_cnt := v_cnt + 1;

  end loop;

end;

--session 3

SQL> select sid,seq#,event from v$session_wait where event like '%contention%';

 

       SID       SEQ# EVENT

---------- ---------- ----------------------------------------------------------------

       637      33497 enq: SQ - contention

      3434      43336 enq: SQ – contention

 

SQL> select sql_text,s.SID,s.SERIAL#,sw.EVENT from v$sql sql, v$session s, v$session_wait sw where sql.SQL_ID = s.SQL_ID

  2  and s.SID = sw.SID  and sw.event like '%contention%';

 

SQL_TEXT                                        SID    SERIAL# EVENT

---------------------------------------- ---------- ---------- ----------------------------------------------------------------

SELECT SEQ_TEST_ID.NEXTVAL FROM DUAL            637      49339 enq: SQ - contention

SELECT SEQ_TEST_ID.NEXTVAL FROM DUAL           3434      10079 enq: SQ - contention

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-697433/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112401