數據庫的等待事件TX-row lock contention怎么解決

本篇內容主要講解“數據庫的等待事件TX-row lock contention怎么解決”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“數據庫的等待事件TX-row lock contention怎么解決”吧!

--創建外鍵參考表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.

到此，相信大家對“數據庫的等待事件TX-row lock contention怎么解決”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！