enq: TX - row lock contention“等待事件的处理
session1：
SQL> conn scott/triger
Connected.
SQL> CREATE TABLE tx_eg ( num number, txt varchar2(10), sex 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
msg ptr
2188218896 00000000826D9210
0 00
0 00 2723168908 6 Idle
0 661 WAITING
发现enq: TX - row lock contention
sql>select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock
where block=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME
BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
----------
135 TX 131073 1761 0 6 1840
0
136 TX 131073 1761 0 6 198
0
138 TX 131073 1761 6 0 1852
1
---查看sid 138 在做什么
SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session where
sid=138;
SID EVENT
----------
----------------------------------------------------------------
SQL_ADDRESS SQL_ID
---------------- -------------
138 SQL*Net message from client
00
去看看具体等待什么吧
SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session
where sid in (7879,8167);
SID EVENT SQL_ADDRESS
SQL_ID
----- --------------------------------------------------
---------------- -------------
7879 enq: TX - row lock contention
C000000CCE8A76F0 08qruv04r2v6d
8167 enq: TX - row lock contention
C000000CCE8A76F0 08qruv04r2v6d
看看sql语句是什么
SQL> select SQL_ID,SQL_TEXT from v$sql
2* where sql_id = '08qruv04r2v6d'
-----查看等待的对象
SQL> SELECT owner, object_name, subobject_name, value
2 FROM v$segment_statistics
3 WHERE statistic_name='row lock waits'
4 AND value > 0
5 ORDER BY 4 DESC;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME VALUE
------------------------------ ----------
SCOTT TX_EG
方法二：
SQL> SELECT row_wait_obj#,
2 row_wait_file#,
3 row_wait_block#,
4 row_wait_row#
5 FROM v$session
6 WHERE sid=135;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
54999 4 485 0
SQL> select object_name from dba_objects where object_id in (54999);
OBJECT_NAME
---------------------------------------------------------------------
-----------
TX_EG
SQL> select sid,serial# from v$session where sid=5364;
SID SERIAL#
----- ----------
5364 24841
--杀会话
alter system kill session '5364,24841';
-------查找对应的操作系统进程号：
SQL> select paddr from v$session where sid= 5364;
PADDR
----------------
C000000D2194FCD0
SQL> select spid from v$process where addr='C000000D2194FCD0';
SPID
------------------------
19669
整合为一句：
Select spid from v$process where addr in(select paddr from v$session
where sid= xxxx;)
SQL> ! ps -ef | grep 19669
orapp0 19669 1 0 Jul 20 ? 0:03 oraclePP0
(DESCRIPTION=(LOCAL=NO)(SDU=32767))
和客户应用管理人员确认后，执行alter system kill session '5364,24841';
命令没能杀掉该会话，只好在操作系统层面通过kill -9 19669杀掉进程，结束会话