[oracle@roger ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 28 21:27:18 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn roger1/roger1
Connected.
SQL> create table nm_tx1 as select * from dba_objects;
Table created.
SQL> create table nm_tx2 as select * from dba_objects where object_id <30000;
Table created.
SQL> alter table nm_tx1 add constraint pk_nm_tx1 primary key (object_id);
Table altered.
SQL> alter table nm_tx2 add constraint pk_nm_tx2 foreign key (object_id)
2 references nm_tx1 (object_id);
Table altered.
SQL>
SQL> col owner for a15
SQL> col CONSTRAINT_NAME for a30
SQL> col TABLE_NAME for a25
SQL> set lines 150
SQL> select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME,status from dba_constraints where owner='ROGER1';
2 and table_name like '%NM%';
OWNER CONSTRAINT_NAME C TABLE_NAME INDEX_NAME STATUS
--------------- ------------------------------ - ------------------------- ------------------------------ --------
ROGER1 PK_NM_TX2 R NM_TX2 ENABLED
ROGER1 PK_NM_TX1 P NM_TX1 PK_NM_TX1 ENABLED
SQL>
----session 1
SQL> show user
USER is "SYS"
SQL> update roger1.nm_tx1 set owner='SYS' where object_id >10000 and object_id <15000;
4895 rows updated.
Elapsed: 00:00:00.12
SQL>
---session 2
SQL> show user
USER is "SYS"
SQL> update roger1.nm_tx1 set owner='SYS' where object_id >10000 and object_id <15000;
---一直处于等待状态
---session 3
SQL> conn /as sysdba
Connected.
SQL> set lines 150
SQL> col event for a60
SQL> select event,count(*) from v$session where wait_class# <>6 group by event;
EVENT COUNT(*)
------------------------------------------------------------ ----------
enq: TX - row lock contention 1 ---出现了该event
SQL*Net message to client 1
Elapsed: 00:00:00.03
SQL>
----session 4
SQL> show user
USER is "SYS"
SQL> delete from roger1.nm_tx2 where object_id >1000 and object_id <1500;
3992 rows deleted.
Elapsed: 00:00:00.15
SQL> --不提交
----session 5
SQL> show user
USER is "ROGER1"
SQL> set timing on
SQL> delete from nm_tx1 where object_id >1000 and object_id <1500;
--处于等待状态
查询event：
1* select event,count(*) from v$session where wait_class# <>6 group by event
SQL> /
EVENT COUNT(*)
------------------------------------------------------------ ----------
enq: TM - contention 1
SQL*Net message to client 1
Elapsed: 00:00:00.04
SQL>
-------实验2
SQL> create table tab_a (id number primary key);
Table created.
Elapsed: 00:00:00.39
SQL> create table tab_b (id number references tab_a(id) ,name varchar2(4));
Table created.
Elapsed: 00:00:00.06
SQL> insert into tab_a values(100);
1 row created.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.04
SQL> insert into tab_b values(100,'lizx'); --
1 row created.
Elapsed: 00:00:00.01
SQL> commit;
Commit complete.
Elapsed: 00:00:00.05
SQL> select * from tab_a;
ID
----------
100
Elapsed: 00:00:00.08
SQL> select * from tab_b;
ID NAME
---------- ----
100 lizx
Elapsed: 00:00:00.01
SQL> insert into tab_a values(200);
1 row created.
Elapsed: 00:00:00.01
SQL>
SQL> conn /as sysdba
Connected.
SQL> SQL>
SQL>
SQL>
SQL> set timing on
SQL> insert into tab_b values(200,'htht'); ---一直等待。。。
SQL> select event,count(*) from v$session where wait_class# <>6 group by event;
EVENT COUNT(*)
------------------------------------------------------------ ----------
enq: TX - row lock contention 1
SQL*Net message to client 1
Elapsed: 00:00:00.23
SQL> --此时的等待事件
SQL> select sess.sid,
2 sess.serial#,
lo.oracle_username,
3 4 lo.os_user_name,
5 ao.object_name,
6 lo.locked_mode
7 from v$locked_object lo,
8 dba_objects ao,
9 v$session sess
10 where ao.object_id = lo.object_id and lo.session_id = sess.sid;
SID SERIAL# ORACLE_USERNAME OS_USER_NAME OBJECT_NAME LOCKED_MODE
---------- ---------- ------------------------------ ------------------------------ ------------------------------ -----------
137 53 SYS oracle TAB_A 3
135 11 SYS oracle TAB_A 2
137 53 SYS oracle TAB_B 2
135 11 SYS oracle TAB_B 3
SQL> select s.sid,s.serial#,p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid in(137,135);
SID SERIAL# SPID
---------- ---------- ------------
135 11 5377
137 53 5386
SQL> select sid,serial#,username,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from v$session where event='enq: TX - row lock contention';
SID SERIAL# USERNAME SQL_ID BLOCKING_SESSION BLOCKING_SE
---------- ---------- ------------------------------ ------------- ---------------- -----------
135 11 SYS 08p87k41wg761 137 VALID
SQL> oradebug setospid 5377
Oracle pid: 21, Unix process pid: 5377, image: oracle@roger (TNS V1-V3)
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/admin/roger/udump/roger_ora_5377.trc
SQL>
SQL> oradebug setospid 5386
Oracle pid: 23, Unix process pid: 5386, image: oracle@roger (TNS V1-V3)
SQL> oradebug dump processstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/admin/roger/udump/roger_ora_5386.trc
SQL>
SQL> select event#,NAME,PARAMETER1,PARAMETER2,PARAMETER3 FROM v$event_name where name='enq: TX - row lock contention';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- --------------------------------------------- ------------------------- ------------------------- -------------------------
186 enq: TX - row lock contention name|mode usn<<16 | slot sequence
SQL>
SQL> select owner,index_name from dba_indexes where table_name='TAB_A';
OWNER INDEX_NAME
------------------------------ ------------------------------
SYS SYS_C005268
SQL> select owner,index_name from dba_indexes where table_name='TAB_B';
no rows selected
SQL> select object_id from dba_objects where object_name='SYS_C005268';
OBJECT_ID
----------
52496
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
5 7 424 1653 6 49
9 26 435 2527 6 54
SQL> select usn,name from v$rollname where usn in(5,9);
USN NAME
---------- ---------------------------------------------
5 _SYSSMU5$
9 _SYSSMU9$
SQL> select object_id,data_object_id from dba_objects where object_name='SYS_C005268';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52496 52496
SQL> select owner,object_name,object_id,object_type from dba_objects where object_id=52497;
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS TAB_B 52497 TABLE
SQL>
SO: 0x2df00c08, type: 4, owner: 0x2de24cec, flag: INIT/-/-/0x00
(session) sid: 135 trans: 0x2ca16584, creator: 0x2de24cec, flag: (80000041) USR/- BSY/-/-/-/-/-
DID: 0001-0015-0000000C, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 2, prv: 0, sql: 0x2d9ff67c, psql: (nil), user: 0/SYS
service name: SYS$USERS
O/S info: user: oracle, term: pts/2, ospid: 5376, machine: roger
program: sqlplus@roger (TNS V1-V3)
application name: sqlplus@roger (TNS V1-V3), hash value=96210805
waiting for 'enq: TX - row lock contention'blocking sess=0x0x2df033b8seq=9 wait_time=0 seconds since wait started=57
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
Dumping Session Wait History
for 'enq: TX - row lock contention' count=1 wait_time=2931132
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931051
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2930298
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931106
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931138
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931233
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2930821
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2930791
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931111
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
for 'enq: TX - row lock contention' count=1 wait_time=2931258
name|mode=54580004, usn<<16 | slot=9001a, sequence=1b3
temporary object counter: 0
从上看，该进程一直在等待enq: TX - row lock contention
SO: 0x2ca16584, type: 40, owner: 0x2df00c08, flag: INIT/-/-/0x00
(trans) flg = 0xe03, flg2 = 0x14000, prx = 0x0, ros = 2147483647 bsn = 0x21 bndsn = 0x24 spn = 0x24
efd = 4
parent xid: 0x0000.000.00000000
env: (scn: 0x0000.0018b619 xid: 0x0005.007.000001a8 uba: 0x01800675.01e7.31 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.0018b619 0sch: scn: 0x0000.00000000)
cev: (spc = 2152 arsp = 2ca5a558 ubk tsn: 7 rdba: 0x01800675 useg tsn: 7 rdba: 0x01800049
hwm uba: 0x01800675.01e7.31 col uba: 0x00000000.0000.00
num bl: 1 bk list: 0x2c9e999c)
cr opc: 0x0 spc: 2152 uba: 0x01800675.01e7.31
(enqueue) TX-00050007-000001A8 DID: 0001-0015-0000000C
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
res: 0x2d470f5c, mode: X, lock_flag: 0x0
own: 0x2df00c08, sess: 0x2df00c08, proc: 0x2de24cec, prv: 0x2d470f64
xga: 0x0, heap: UGA
Trans IMU st: 2 Pool index 1, Redo pool 0x2cbad520, Undo pool 0x2cbad4e8
Redo pool range [0x2ca87034 0x2ca871cc 0x2ca96634]
Undo pool range [0x2cbad800 0x2cbad978 0x2cbbd200]
Redo small pool range [0x0 0x0 0x0]
Undo small pool range [0x0 0x0 0x0]
Pmd 0x2cbac878 opc: 1 bkopc: 0, recstate 0
txnvalid 1 chgvalid 1 cvsvalid 1
IMU Redo pool sz 62976, usage 408
IMU Undo pool sz 64000, usage 376
IMU changes: 2 applied chgs: 2 Redosiz 336, Flush reason 5
Redo Private strand index = 2
Strand index 2 Strand state 1 Available bufs 123
Current IMU pool 1 First buf 0x2ca86fe0 Strand size 66560
-------------------------------------------------------
IMU redo block change list
------------------------------------------------------
tsn 0 rdba 0x40fa3a bh 0x23fee7e8 cv 0x2ca87084
------------------------------------------------------
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0005.007.000001a8 uba: 0x01800675.01e7.31
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040fa3a hdba: 0x0040fa39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) size/delt: 11
fb: --H-FL-- lb: 0x2 cc: 2
null: --
col 0: [ 2] c2 03
col 1: [ 4] 68 74 68 74
------------------------------------------------------
tsn 7 rdba 0x1800049 bh 0x27bf8978 cv 0x2ca87150
------------------------------------------------------
ktudh redo: slt: 0x0007 sqn: 0x000001a8 flg: 0x0012 siz: 108 fbi: 1
uba: 0x01800675.01e7.31 pxid: 0x0000.000.00000000
------------------------------------------------------
------------------------------------------------------
IMU Undo change vector list (latched dump)
------------------------------------------------------
umap: 0x2cbad92c uba: 0x01800675.01e7.31
undobh 0x227fc5b0 cv 0x2cbad848 rcvi 0 Applied
------------------------------------------------------
ktudb redo: siz: 108 spc: 2262 flg: 0x0012 seq: 0x01e7 rec: 0x31
xid: 0x0005.007.000001a8
ktubl redo: slt: 7 rci: 0 opc: 11.1 objn: 52497 objd: 52497 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x01800676.01e7.0e
prev ctl max cmt scn: 0x0000.0018b116 prev tx cmt scn: 0x0000.0018b14d
txn start scn: 0x0000.00000000 logon user: 0 prev brb: 25167474 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040fa3a hdba: 0x0040fa39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1)
------------------------------------------------------
----------------------------------------
SO: 0x2c9e999c, type: 39, owner: 0x2ca16584, flag: -/-/-/0x00
(List of Blocks) next index = 1
index itli buffer hint rdba savepoint
-----------------------------------------------------------
0 2 0x23fee7e8 0x40fa3a 0x23
----------------------------------------
SO: 0x2c9c6754, type: 36, owner: 0x2ca16584, flag: INIT/-/-/0x00
DML LOCK: tab=52497 flg=11 chi=0
his[0]: mod=3 spn=33
(enqueue) TM-0000CD11-00000000 DID: 0001-0015-0000000C
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
res: 0x2d46bc14, mode: SX, lock_flag: 0x0
own: 0x2df00c08, sess: 0x2df00c08, proc: 0x2de24cec, prv: 0x2d46bc1c
----------------------------------------
SO: 0x2c9c66a8, type: 36, owner: 0x2ca16584, flag: INIT/-/-/0x00
DML LOCK: tab=52495 flg=11 chi=0
his[0]: mod=2 spn=33
(enqueue) TM-0000CD0F-00000000 DID: 0001-0015-0000000C
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
res: 0x2d470e54, mode: SS, lock_flag: 0x0
own: 0x2df00c08, sess: 0x2df00c08, proc: 0x2de24cec, prv: 0x2d470e5c
----------------------------------------
SO: 0x2bb9f6e8, type: 53, owner: 0x2df00c08, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=2bb9f6e8 handle=2a35d0d8 mode=N
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x2bb9f734[0x2bb9e738,0x2bb9e738] htb=0x2bb9e738 ssga=0x2bb9e4d4
user=2df00c08 session=2df00c08 count=0 flags=LRU/[4000] savepoint=0x1e
LIBRARY OBJECT HANDLE: handle=2a35d0d8 mtx=0x2a35d18c(0) cdp=0
name=SYS.TAB_B
hash=186a62ed18bde8071175e9d275758835 timestamp=04-28-2011 22:19:10
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0201 lock=N pin=0 latch#=1 hpc=0004 hlc=0004
lwt=0x2a35d134[0x2a35d134,0x2a35d134] ltm=0x2a35d13c[0x2a35d13c,0x2a35d13c]
pwt=0x2a35d118[0x2a35d118,0x2a35d118] ptm=0x2a35d120[0x2a35d120,0x2a35d120]
ref=0x2a35d154[0x2a35d154,0x2a35d154] lnd=0x2a35d160[0x2a288cd0,0x2a36de28]
LIBRARY OBJECT: object=2885b5dc
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 2a3e0e44 2885b698 I/-/A/-/- 0 NONE 00
8 2885b868 29b3d4cc I/-/A/-/- 0 NONE 00
10 2885b8b8 28e86a58 I/-/A/-/- 0 NONE 00
堵塞进程的dump信息：
ktudh redo: slt: 0x001a sqn: 0x000001b3 flg: 0x0012 siz: 108 fbi: 0
uba: 0x018009df.01bf.35 pxid: 0x0000.000.00000000
------------------------------------------------------
tsn 0 rdba 0x40fa32 bh 0x227fcad4 cv 0x2ca76db4
------------------------------------------------------
index redo (kdxlin): insert leaf row
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0009.01a.000001b3 uba: 0x018009df.01bf.36
REDO: SINGLE / -- / --
itl: 2, sno: 1, row size 13
insert key: (3): 02 c2 03
keydata: (6): 00 40 fa 2a 00 01
ktudb redo: siz: 92 spc: 1168 flg: 0x0022 seq: 0x01bf rec: 0x36
xid: 0x0009.01a.000001b3
ktubu redo: slt: 26 rci: 53 opc: 10.22 objn: 52496 objd: 52496 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.004.000001b3 uba: 0x018009de.01bf.13
flg: C--- lkc: 0 scn: 0x0000.0018b37f
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x40fa31 block=0x0040fa32
(kdxlpu): purge leaf row
key :(3): 02 c2 03
SO: 0x2c9f0b9c, type: 39, owner: 0x2ca39400, flag: -/-/-/0x00
(List of Blocks) next index = 2
index itli buffer hint rdba savepoint
-----------------------------------------------------------
0 2 0x23fee960 0x40fa2a 0x23
1 2 0x227fcad4 0x40fa32 0x26
----------------------------------------
SO: 0x2c9c65fc, type: 36, owner: 0x2ca39400, flag: INIT/-/-/0x00
DML LOCK: tab=52497 flg=11 chi=0
his[0]: mod=2 spn=33
(enqueue) TM-0000CD11-00000000 DID: 0001-0017-0000000F
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
res: 0x2d46bc14, mode: SS, lock_flag: 0x0
own: 0x2df033b8, sess: 0x2df033b8, proc: 0x2de25864, prv: 0x2c9c6774
----------------------------------------
SO: 0x2c9c6550, type: 36, owner: 0x2ca39400, flag: INIT/-/-/0x00
DML LOCK: tab=52495 flg=11 chi=0
his[0]: mod=3 spn=33
(enqueue) TM-0000CD0F-00000000 DID: 0001-0017-0000000F
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 res_flag: 0x6
res: 0x2d470e54, mode: SX, lock_flag: 0x0
own: 0x2df033b8, sess: 0x2df033b8, proc: 0x2de25864, prv: 0x2c9c66c8
从上面的表色部分基本上就能看出整个enq: TX - row lock contention的原因了。
下面做个简单的总结，关于index相关的几个event：
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
---------- -------------------------------- -------------- ---------------- ------------- -------------
186 enq: TX - row lock contention name|mode usn<<16 | slot sequence Application
187 enq: TX - allocate ITL entry name|mode usn<<16 | slot sequence Configuration
188 enq: TX - index contention name|mode usn<<16 | slot sequence Concurrency
580 enq: TX - contention name|mode usn<<16 | slot sequence Other
1. enq: TX - row lock contention ，顾名思义，该event是row级别的，要处理该问题，唯一的方式就是调整应用逻辑。
2. enq: TX - allocate ITL entry ，该event明显是index itl不足（通常是在高并发的情况下），当然最简单的方式就是调大init_trans了。
3. enq: TX - index contention ，该event的出现通常也是在高并发的时候，不过一般是由于index split等导致，处理方式的话，一般手段是将index重建为反向键index或将index进行hash分区。如果是跟sequence相关的话，那建议将sequence cache值调大。
4. enq: TX - contention ，该event跟前面3种不一样了，该event属于事务级别。原理跟第一类似，不过是针对事务而言了。
联系客服
