导航：首页 > 数据库 >

数据库的等待事件TX-row lock contention怎么解决

发表于：2024-11-28 作者：千家信息网编辑

千家信息网最后更新 2024年11月28日，本篇内容主要讲解"数据库的等待事件TX-row lock contention怎么解决"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"数据库的等待事件TX-

千家信息网最后更新 2024年11月28日数据库的等待事件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.