Insertからプライマリ・キーまたは一意制約テーブルへのブロックテスト


各シーンのロック、待機イベントの特徴を理解することで、生産上の問題を迅速に特定できます.
テストテーブルの作成
create table testf
(
a varchar(255),
b varchar(255),
c varchar(255),
d varchar(255),
e varchar(255),
f varchar(255),
g varchar(255),
h varchar(255),
i varchar(255),
j varchar(255),
k varchar(255),
l varchar(255),
m varchar(255),
n varchar(255),
o varchar(255),
p varchar(255),
q varchar(255),
r varchar(255),
s varchar(255),
t varchar(255),
u varchar(255),
v varchar(255),
w varchar(255),
x varchar(255),
y varchar(255),
z varchar(255)
);

プライマリ・キー制約の追加
alter table testf add constraint pk_a primary key (a);

シーン1:2つのセッションが同じ値をプライマリ・キー、一意制約フィールドに挿入し、セッション1が完了し、セッション2の失敗は一意制約の失敗を報告します.
session 1 insertテスト文の実行
begin
for x in 1..500000 loop
insert into testf values (x,'aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa');
commit;
end loop;
end;
/

ロックの表示
select * from v$lock;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0700000188A238A8 0700000188A238D0        519 TM     798999          0          3          0         26          0
0700000188ADE520 0700000188ADE558        519 TX     655367   33178285          6          0         26          0

session 2 insertテスト文の実行
begin
for x in 1..500000 loop
insert into testf values (x,'aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa');
commit;
end loop;
end;
/

ロックの表示
select * from v$lock;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0700000188A238A8 0700000188A238D0        519 TM     798999          0          3          0         18          0
0700000188A239A8 0700000188A239D0        524 TM     798999          0          3          0          5          0
0700000188ABFF40 0700000188ABFF78        524 TX     589843   30690293          6          0          5          0
0700000188ADEDC0 0700000188ADEDF8        519 TX     393234   23268430          6          0         18          1

block=1ブロックスレッド
待機イベントv$session_の表示wait
SID USERNAME   MACHINE    EVENT                          P1TEXT             P1 P2TEXT             P2 WAIT_CLASS      P3TEXT             P3 STATE
---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------- -------------------
519 SYS        lpar2      buffer busy waits              file#               1 block#          50800 Concurrency     class#              1 WAITED SHORT TIME
524 SYS        lpar2      enq: TX - row lock contention  name|mode  1415053316 usn<<16 |      393234 Application     sequence     23268430 WAITING
slot
489 SYS        lpar2      SQL*Net message to client      driver id  1650815232 #bytes              1 Network                             0 WAITED SHORT TIME

セッション1がinsertを完了するまで、セッション2はエラーを返します.
ERROR at line 1:
ORA-00001: unique constraint (SYS.PKA) violated
ORA-06512: at line 3

シーン2:2つのスレッドがプライマリ・キー、一意制約フィールドに異なる値を挿入し、いずれも完了します.session 1テストsqlを実行
session 1
begin
for x in 1..500000 loop
insert into testf values (x,'aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa');
commit;
end loop;
end;
/

session 2テストsqlを実行
begin
for x in 1..500000 loop
insert into testf values (x+500000,'aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa');
commit;
end loop;
end;
/

ブロックされていないプロセス
select * from v$lock;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0700000188A238A8 0700000188A238D0        519 TM     798999          0          3          0        103          0
0700000188A239A8 0700000188A239D0        524 TM     798999          0          3          0         86          0
0700000188ABEE00 0700000188ABEE38        524 TX     655395   33173632          6          0         86          0
070000018AB3F708 070000018AB3F740        519 TX     524319   29519550          6          0        103          0

待機イベントの表示
SID USERNAME   MACHINE    EVENT                          P1TEXT             P1 P2TEXT             P2 WAIT_CLASS      P3TEXT             P3 STATE
---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------- -------------------
519 SYS        lpar2      buffer busy waits              file#               1 block#          66558 Concurrency     class#              1 WAITED SHORT TIME
524 SYS        lpar2      buffer busy waits              file#               1 block#          66562 Concurrency     class#              1 WAITED SHORT TIME
489 SYS        lpar2      SQL*Net message to client      driver id  1650815232 #bytes              1 Network                             0 WAITED SHORT TIME

最終的にはすべて完了します.