enq: TX

 

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殺掉進程，結束會話