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