SELECT ~ FOR UPDATE SKIP LOCKED その4 - もしもITL不足だったら... Tweet

skip lockedのつづきです。

ITLエントリー不足時のskip lockedの動きを確認してみるか....

100行が1ブロックに収まるような表を作成しておく...計算上、ITLエントリーは最大で4エントリー程度になるように....したつもり....
（ASSMで、INITRANSはデフォルト、ブロックサイズは8KB、PCTFREEは0%）

SCOTT@pdborcl> r
  1  select
  2      objectid
  3      ,file#
  4      ,block#
  5      ,count(id) as num_of_rows
  6  from
  7  (
  8      select
  9           dbms_rowid.rowid_object(rowid) as objectid
 10          ,dbms_rowid.rowid_relative_fno(rowid) as file#
 11          ,dbms_rowid.rowid_block_number(rowid) as block#
 12          ,id
 13      from
 14          q
 15  )
 16  group by
 17      objectid
 18      ,file#
 19      ,block#
 20  order by
 21      objectid
 22      ,file#
 23*     ,block#

  OBJECTID      FILE#     BLOCK# NUM_OF_ROWS
---------- ---------- ---------- -----------
     93077          9     972461         100

※セッション１ - lockできた

SESSION1@pdborcl> select * from q where id = '0001' for update;

ID
----
TEXT_STRING
----------------------------------------------------------------------
0001
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション２ - lockできた。 この時点で 1 + 1 = 2 のITLエントリは使い切っている。

SESSION2@pdborcl> select * from q where id = '0002' for update;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0002
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション３ - lockできた。　ITLがブロック内の空きスペースに作れたため :)

SESSION3@pdborcl> select * from q where id = '0003' for update;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0003
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション４ - 計算上のITLエントリー数の上限値. 問題なくlockできた。

SESSION4@pdborcl> select * from q where id = '0004' for update;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0004
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション５ - 狙い通り！　５つめのITLエントリーを作成するだけの空きがブロック内にない状況なので待機しちゃう :)

SESSION5@pdborcl> select * from q where id = '0005' for update;

待機イベント見れば一目瞭然、ITLエントリー不足で待機してますね！

SYS@pdborcl> select username,event from v$session where username = 'SCOTT'

USERNAME   EVENT
---------- ----------------------------------------
SCOTT      SQL*Net message from client
SCOTT      SQL*Net message from client
SCOTT      enq: TX - allocate ITL entry
SCOTT      SQL*Net message from client
SCOTT      SQL*Net message from client

ここまでは、ITL不足な状況のfor update文ではよく見かける光景ですよね :)

for update skip lockedにすると.....

※セッション１ - locked!

SESSION1@pdborcl> select * from q where id = '0001' for update skip locked;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0001
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション２ - locked!!

SESSION2@pdborcl> select * from q where id = '0002' for update skip locked;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0002
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション３　- locked!!!

SESSON3@pdborcl> select * from q where id = '0003' for update skip locked;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0003
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション４ - locked!!!!

SESSION4@pdborcl> select * from q where id = '0004' for update skip locked;

ID
----
TEXT_STRING
---------------------------------------------------------------------
0004
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

※セッション５（ITLエントリーが確保できず id = '0005'の行をlockすることができないので空振りします。興味深い動きですよね。)

SESSION5@pdborcl> select * from q where id = '0005' for update skip locked;

レコードが選択されませんでした。

次回へつづく.....かもしれない。

---

・SELECT ~ FOR UPDATE SKIP LOCKED　その１ - @sh2ndさんエントリの復習など
・SELECT ~ FOR UPDATE SKIP LOCKED　その２
・SELECT ~ FOR UPDATE SKIP LOCKED　その３