sequence可能引发的各种lock/enqueue

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)：则各节点会把不同范围的值sequence值cache到内存中；

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

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

 

 

实验：oracle版本11.2.0.2

先创建nocache的sequence

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

 

创建cache为20

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-697433/