Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the database waiting event TX-row lock contention". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn how to solve the database waiting event TX-row lock contention!

-- create foreign key reference table T3

SQL > create table T3 (id number primary key,name varchar2 (20), product_id number)

Table created.

-- create master table T2

SQL > create table T2 (id number primary key,name varchar2 (20))

Table created.

-- add foreign key constraints to the T3 table

SQL > alter table T3 add constraint FK_PRODUCTSTAT_PRODUCTID foreign key (PRODUCT_id) references T2 (ID)

Table altered.

SQL >

SQL > insert into T2 values (1dh')

1 row created.

SQL > insert into T2 values (2meme cc')

1 row created.

SQL > insert into T2 values (3meme 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

-- pay attention to the distinction:

Session1:

SQL > insert into T2 values (4cc')

1 row created.

Not submitted.

Session 2:

SQL > insert into T3 values (1)

1 row created.

Hang lives and doesn't move.

Session 3: query:

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. Ownerships

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)

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 >

Now submit the session1:

SQL > commit

Commit complete.

SQL >

-- session2 insertion is also completed:

SQL > insert into T3 values (1)

1 row created.

SQL > SQL > commit

Commit complete.

At this point, I believe you have a deeper understanding of "how to solve the database waiting event TX-row lock contention". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.