数据库的等待事件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怎么解决”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！