enq: TX - row lock contention“等待事件的处理
enq: TX - row lock contention“等待事件的处理
session1:
SQL> conn scott/triger
Connected.
SQL> CREATE TABLE tx_eg ( num number, txt varchar2(10), 易做图 varchar2
(10) ) INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
COMMIT;
Table created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;
1 row updated.
session2:oracle@localhost ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 24 15:43:20 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
SQL> conn scott/triger
Connected.
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;
解决方法:
SQL> select * from v$session_wait;
SID SEQ# EVENT
P1TEXT
P1 P1RAW P2TEXT
P2 P2RAW
---------- ----------
----------------------------------------------------------------
----------------------------------------------------------------
---------- ----------------
----------------------------------------------------------------
---------- ----------------
P3TEXT
P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
WAIT_TIME SECONDS_IN_WAIT STATE
----------------------------------------------------------------
---------- ---------------- ------------- -----------
----------------------------------------------------------------
---------- --------------- -------------------
135 33 enq: TX - row lock contention
name|mode
1415053318 0000000054580006 usn<<16 | slot
131073 0000000000020001
sequence
1761 00000000000006E1 4217450380 1 Application
0 2554 WAITING
136 33 enq: TX - row lock contention
name|mode
1415053318 0000000054580006 usn<<16 | slot
131073 0000000000020001
sequence
1761 00000000000006E1 4217450380 1 Application
0 912 WAITING
138 87 SQL*Net message from client
driver id
1650815232 0000000062657100 #bytes
1 0000000000000001
0 00 2723168908 6 Idle
0 1987 WAITING
141 5425 i/o slave wait &nbs