RE: Tx - row lock contention after implementing transaction management in application server - oracle-l - FreeLists
に面白い投稿があったので、自分でも試してみた。
外部キーがトランザクションエンキュー(行ロック)競合の原因になるケースがあるらしい。
以下のようなケースで発生する。
- セッション1で親テーブルにレコードAをinsertして、commit/rollbackしていない状態のままにする。
- セッション2で子テーブルにレコードAを外部参照するレコードをinsertしようとする。
- セッション2は「TX - row lock contention」で待機する。
日本語が正確じゃない気がする。。。
実際に試してみる。
- テーブルを作成する。
$ sqlplus scott/tiger SQL> create table parent(p_id number primary key); SQL> create table child(c_id number primary key, p_id number references parent(p_id));
- あるセッションで親テーブルにinsertする。
$ sqlplus scott/tiger SQL> insert into parent values(1); 1 row created.
- 別セッションで子テーブルにinsertする。
$ sqlplus scott/tiger SQL> insert into child values(1,1);
- ロックの競合状況を見てみる。
$ sqlplus scott/tiger SQL> set feedback off set pagesize 9999 set linesize 2000 set trimspool on set trimout on col block for 9999 col sid for 9999 col status for a8 col machine for a9 col username for a9 col type for a2 col lmode for 99 col request for 99 col program for a10 col serialno for 999999 col lock_time for a10 col sql for a20 select a.blocking_session block, a.sid sid, a.status status, substr(a.machine, 1,9) machine, a.username username, b.type type, b.lmode lmode, b.request request, a.program program, a.serial# serialno, to_char(b.ctime/60, '9990.9') lock_time, c.sql_text sql from v$session a, v$lock b, v$sqlarea c where a.sid=b.sid and a.sql_address = c.address; BLOCK SID STATUS MACHINE USERNAME TY LMODE REQUEST PROGRAM SERIALNO LOCK_TIME SQL ----- ----- -------- --------- --------- -- ----- ------- ---------- -------- ---------- -------------------- 193 202 ACTIVE solaris10 SCOTT TX 0 4 sqlplus@so 225 0.1 insert into child va laris101 ( lues(1,1) TNS V1-V3) 193 202 ACTIVE solaris10 SCOTT TM 2 0 sqlplus@so 225 0.1 insert into child va laris101 ( lues(1,1) TNS V1-V3) 193 202 ACTIVE solaris10 SCOTT TM 3 0 sqlplus@so 225 0.1 insert into child va laris101 ( lues(1,1) TNS V1-V3) 193 202 ACTIVE solaris10 SCOTT TX 6 0 sqlplus@so 225 0.1 insert into child va laris101 ( lues(1,1) TNS V1-V3)
- 親テーブルへのinsertをcommitする。
SQL> commit;
- 子テーブルへのinsertが実行される。
1 row created.
- ロック待ちが解消されている。
SQL> r 1 select a.blocking_session block, 2 a.sid sid, 3 a.status status, 4 substr(a.machine, 1,9) machine, 5 a.username username, 6 b.type type, 7 b.lmode lmode, 8 b.request request, 9 a.program program, 10 a.serial# serialno, 11 to_char(b.ctime/60, '9990.9') lock_time, 12 c.sql_text sql 13 from v$session a, 14 v$lock b, 15 v$sqlarea c 16 where a.sid=b.sid 17* and a.sql_address = c.address no rows selected
ちなみに親テーブルへのinsertをrollbackすると、子テーブルへinsertしようとしているセッションでは ORA-02291 が発生する。
insert into child values(1,1) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SYS_C004448) violated - parent key not found
普通に考えると当たり前の話だな。というかこうなってもらわないと困る。
子テーブルの外部キーにセットできる値は「親テーブルに存在する値」か「NULL」。上記の例では、親テーブルへのinsertがcommitされるかrollbackされるかで、「親テーブルに存在するか否か」が変わるからcommit/rollbackされるまで待たないといけない。
