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