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, 'th th ', '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,200 5, Oracle. All rights reserved.

Connected:
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;

 

Solution:

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 100000000000006e1 4217450380 1 Application

0 2554 WAITING

136 33 enq: TX-row lock contention

Name | mode

1415053318 0000000054580006 usn <16 | slot

131073 0000000000020001
Sequence

1761 100000000000006e1 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 201710000826d9210

0 00

0 00 2723168908 6 Idle

0 661 WAITING

 

Enq: TX-row lock contention found

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

--- View what sid 138 is doing
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

Let's see what you are waiting.
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

What is an SQL statement?
SQL> select SQL _ID, SQL _TEXT from v $ SQL
2 * where SQL _id = '08qruv04r2v6d'

----- View the waiting object

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

 

Method 2:
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 & gt; select sid, serial # from v $ session where sid = 5364;
Sid serial #
---------------
5364 24841

 

 

-- Kill a session
Alter system kill session '2017 41 ';

------- Find the corresponding operating system process Number:
SQL> select paddr from v $ session where sid = 5364;
PADDR
----------------
C000000D2194FCD0
SQL> select spid from v $ process where addr = 'c000000d2194fcd0 ';
SPID
------------------------
19669

 

 

Integration into one sentence:
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 ))

 

After confirming with the customer's application management personnel, execute alter system kill session '0000001 ';

The command failed to kill the session and ended the session at the operating system level by kill-9 19669.