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), 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杀掉进程，结束会话