實驗如下

--建立外來鍵參考表t3
SQL> create table t3 (id number primary key,name varchar2(20),product_id number);

Table created.

--建立主表t2
SQL> create table t2 (id number primary key,name varchar2(20));

Table created.

--給t3表加入外來鍵約束
SQL> alter table t3 add constraint FK_PRODUCTSTAT_PRODUCTID foreign key (PRODUCT_id) references t2 (ID);

Table altered.

SQL>
SQL> insert into t2 values(1,'dh');

1 row created.

SQL> insert into t2 values(2,'cc');

1 row created.

SQL> insert into t2 values(3,'cc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 dh
         2 cc
         3 cc

SQL> select * from t3;

no rows selected

--注意區分：
session1：
SQL> insert into t2 values(4,'cc');

1 row created.

未提交。。。

session 2:
SQL> insert into t3 values(1,'tt',4);

1 row created.

hang住，一直不動。。。

session 3:查詢：
SET LINES 200
col OWNER for a10
col R_OWNER for a10
col R_CONSTRAINT_NAME for a15
select a.table_name,
       a.owner,
       a.constraint_name,
       a.constraint_type,
       a.r_owner,
       a.r_constraint_name,
       b.table_name
from dba_constraints a, dba_constraints b
where a.constraint_type = 'R'
   and a.r_constraint_name = b.constraint_name
   and a.r_owner = b.owner
   and b.table_name = 'T2'
   and b.owner='SYS';

TABLE_NAME                     OWNER      CONSTRAINT_NAME                C R_OWNER    R_CONSTRAINT_NA TABLE_NAME
------------------------------ ---------- ------------------------------ - ---------- --------------- ------------------------------
T3                             SYS        FK_PRODUCTSTAT_PRODUCTID       R SYS        SYS_C0012328    T2

SQL> select sid,serial#,sql_id,program,username,event,blocking_session from v$session where blocking_session is not null;

       SID    SERIAL# SQL_ID        PROGRAM                                          USERNAME                       EVENT                                                  BLOCKING_SESSION
---------- ---------- ------------- ------------------------------------------------ ------------------------------ ---------------------------------------------------------------- ----------------
        53        427 1cgjmu64ztjch sqlplus@wang (TNS V1-V3)                         SYS                            enq: TX - row lock contention                        57
SQL> select sql_text from v$sql where sql_id='1cgjmu64ztjch';

SQL_TEXT
-------------------------------------------------------------
insert into t3 values(1,'tt',4)

SQL>
SQL> select sid,serial#,sql_id,program,username,event,blocking_session from v$session where sid=57;

SID    SERIAL# SQL_ID          PROGRAM                                             USERNAME          EVENT                        BLOCKING_SESSION
---------- ---------- ------------- ------------------------------------------------ ------------------------------
        57        575               sqlplus@wang (TNS V1-V3)                         SYS         SQL*Net message from client

SQL>

現在將session1提交：
SQL> commit;

Commit complete.

SQL>

--session2插入也完成：
SQL> insert into t3 values(1,'tt',4);


1 row created.

SQL>SQL> commit;

Commit complete.

完成！！！！1111

主外來鍵約束之主表插入未提交導致外來鍵表插入hang住的等待事件 TX-row lock contention

相關文章