??ORACLE(?):PMON Release Lock
- by Liu Maclean(???)
?????Oracle????????????PMON???????,??????ORACLE PROCESS,??cleanup dead process????release enqueue lock ,???cleanup latch?
????????????????, ????????????Pmon cleanup dead process?release lock???????????
??Oracle=> MicroOracle, Maclean???????????Oracle behavior:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
SQL> select pid,program from v$process;
PID PROGRAM
---------- ------------------------------------------------
1 PSEUDO
2 [email protected] (PMON)
3 [email protected] (PSP0)
4 [email protected] (VKTM)
5 [email protected] (GEN0)
6 [email protected] (DIAG)
7 [email protected] (DBRM)
8 [email protected] (PING)
9 [email protected] (ACMS)
10 [email protected] (DIA0)
11 [email protected] (LMON)
12 [email protected] (LMD0)
13 [email protected] (LMS0)
14 [email protected] (RMS0)
15 [email protected] (LMHB)
16 [email protected] (MMAN)
17 [email protected] (DBW0)
18 [email protected] (LGWR)
19 [email protected] (CKPT)
20 [email protected] (SMON)
21 [email protected] (RECO)
22 [email protected] (RBAL)
23 [email protected] (ASMB)
24 [email protected] (MMON)
25 [email protected] (MMNL)
26 [email protected] (MARK)
27 [email protected] (D000)
28 [email protected] (SMCO)
29 [email protected] (S000)
30 [email protected] (LCK0)
31 [email protected] (RSMN)
32 [email protected] (TNS V1-V3)
33 [email protected] (W000)
34 [email protected] (TNS V1-V3)
35 [email protected] (TNS V1-V3)
37 [email protected] (ARC0)
38 [email protected] (ARC1)
40 [email protected] (ARC2)
41 [email protected] (ARC3)
43 [email protected] (GTX0)
44 [email protected] (RCBG)
46 [email protected] (QMNC)
47 [email protected] (TNS V1-V3)
48 [email protected] (TNS V1-V3)
49 [email protected] (Q000)
50 [email protected] (Q001)
51 [email protected] (GCR0)
SQL> drop table maclean;
Table dropped.
SQL> create table maclean(t1 int);
Table created.
SQL> insert into maclean values(1);
1 row created.
SQL> commit;
Commit complete.
?????????, ?????????:PID=2 PMONPID=11 LMONPID=18 LGWRPID=20 SMONPID=12 LMD
??????2???”enq: TX – row lock contention”?????,???KILL??????,??????PMON?recover dead process?release TX lock:
PROCESS A:
QL> select addr,spid,pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
ADDR SPID PID
---------------- ------------------------ ----------
00000000BD516B80 17880 46
SQL> select distinct sid from v$mystat;
SID
----------
22
SQL> update maclean set t1=t1+1;
1 row updated.
PROCESS B
SQL> select addr,spid,pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
ADDR SPID PID
---------------- ------------------------ ----------
00000000BD515AD0 17908 45
SQL> update maclean set t1=t1+1;
HANG..............
PROCESS B ??"enq: TX – row lock contention"?HANG?
????PROCESS C?? ?SMON?10500 event trace ??PMON?KST TRACE:
SQL> set linesize 200 pagesize 1400
SQL> select * from v$lock where sid=22;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000BDCD7618 00000000BDCD7670 22 AE 100 0 4 0 48 2
00007F63268A9E28 00007F63268A9E88 22 TM 77902 0 3 0 32 2
00000000B9BB4950 00000000B9BB49C8 22 TX 458765 892 6 0 32 1
PROCESS A holde?ENQUEUE LOCK??? AE?TM?TX
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system set "_trace_events"='10000-10999:255:2,20,33';
System altered.
SQL> ! kill -9 17880
KILL PROCESS A ???PROCESS B??update
?PMON ? PROCESS B ?errorstack ?KST TRACE?????
SQL> oradebug setorapid 2;
Oracle pid: 2, Unix process pid: 17533, image: [email protected] (PMON)
SQL> oradebug dump errorstack 4;
Statement processed.
SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_pmon_17533.trc
SQL> oradebug setorapid 45;
Oracle pid: 45, Unix process pid: 17908, image: [email protected] (TNS V1-V3)
SQL> oradebug dump errorstack 4;
Statement processed.
SQL>oradebug tracefile_name
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_17908.trc
??PMON? KST TRACE:
2012-05-18 10:37:34.557225 :8001ECE8:db_trace:ktur.c@5692:ktugru(): [10444:2:1] next rollback uba: 0x00000000.0000.00
2012-05-18 10:37:34.557382 :8001ECE9:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:2:1] KSL POST SENT postee=18 num=4 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 id2=0 name= type=0
2012-05-18 10:37:34.557514 :8001ECEA:db_trace:ksq.c@8540:ksqrcli(): [10704:2:1] ksqrcl: release TX-0007000d-0000037c mode=X
2012-05-18 10:37:34.558819 :8001ECF0:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:2:1] KSL POST SENT postee=45 num=5 loc='kji.h LINE:3418 ID:kjata: wake up enqueue owner' id1=0 id2=0 name= type=0
2012-05-18 10:37:34.559047 :8001ECF8:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:2:1] KSL POST SENT postee=12 num=6 loc='kjm.h LINE:1224 ID:kjmpost: post lmd' id1=0 id2=0 name= type=0
2012-05-18 10:37:34.559271 :8001ECFC:db_trace:ksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
2012-05-18 10:37:34.559291 :8001ECFD:db_trace:ktu.c@8652:ktudnx(): [10813:2:1] ktudnx: dec cnt xid:7.13.892 nax:0 nbx:0
2012-05-18 10:37:34.559301 :8001ECFE:db_trace:ktur.c@3198:ktuabt(): [10444:2:1] ABORT TRANSACTION - xid: 0x0007.00d.0000037c
2012-05-18 10:37:34.559327 :8001ECFF:db_trace:ksq.c@8540:ksqrcli(): [10704:2:1] ksqrcl: release TM-0001304e-00000000 mode=SX
2012-05-18 10:37:34.559365 :8001ED00:db_trace:ksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
2012-05-18 10:37:34.559908 :8001ED01:db_trace:ksq.c@8540:ksqrcli(): [10704:2:1] ksqrcl: release AE-00000064-00000000 mode=S
2012-05-18 10:37:34.559982 :8001ED02:db_trace:ksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
2012-05-18 10:37:34.560217 :8001ED03:db_trace:ksfd.c@15379:ksfdfods(): [10298:2:1] ksfdfods:fob=0xbab87b48 aiopend=0
2012-05-18 10:37:34.560336 :GSIPC:kjcs.c@4876:kjcsombdi(): GSIPC:SOD: 0xbc79e0c8 action 3 state 0 chunk (nil) regq 0xbc79e108 batq 0xbc79e118
2012-05-18 10:37:34.560357 :GSIPC:kjcs.c@5293:kjcsombdi(): GSIPC:SOD: exit cleanup for 0xbc79e0c8 rc: 1, loc: 0x303
2012-05-18 10:37:34.560375 :8001ED04:db_trace:kss.c@1414:kssdch(): [10809:2:1] kssdch(0xbd516b80 = process, 3) 1 0 exit
2012-05-18 10:37:34.560939 :8001ED06:db_trace:kmm.c@10578:kmmlrl(): [10257:2:1] KMMLRL: Entering: flg(0x0) rflg(0x4)
2012-05-18 10:37:34.561091 :8001ED07:db_trace:kmm.c@10472:kmmlrl_process_events(): [10257:2:1] KMMLRL: Events: succ(3) wait(0) fail(0)
2012-05-18 10:37:34.561100 :8001ED08:db_trace:kmm.c@11279:kmmlrl(): [10257:2:1] KMMLRL: Reg/update: flg(0x0) rflg(0x4)
2012-05-18 10:37:34.563325 :8001ED0B:db_trace:kmm.c@12511:kmmlrl(): [10257:2:1] KMMLRL: Update: ret(0)
2012-05-18 10:37:34.563335 :8001ED0C:db_trace:kmm.c@12768:kmmlrl(): [10257:2:1] KMMLRL: Exiting: flg(0x0) rflg(0x4)
2012-05-18 10:37:34.563354 :8001ED0D:db_trace:ksl2.c@2598:kslwtbctx(): [10005:2:1] KSL WAIT BEG [pmon timer] 300/0x12c 0/0x0 0/0x0 wait_id=78 seq_num=79 snap_id=1
PMON??dead process A??????????TX Lock:ksqrcl: release TX-0007000d-0000037c mode=X
?????Post Process B,??Process B ?acquire?TX lock???????:KSL POST SENT postee=45 num=5 loc=’kji.h LINE:3418 ID:kjata: wake up enqueue owner’ id1=0 id2=0 name= type=0
Process B???PMON??????????ksl2.c@14563:ksliwat(): [10005:45:151] KSL POST RCVD poster=2 num=5 loc=’kji.h LINE:3418 ID:kjata: wake up enqueue owner’ id1=0 id2=0 name= type=0 fac#=3 posted=0×3 may_be_posted=1kslwtbctx(): [10005:45:151] KSL WAIT BEG [latch: ges resource hash list] 3162668560/0xbc827e10 91/0x5b 0/0×0 wait_id=14 seq_num=15 snap_id=1kslwtectx(): [10005:45:151] KSL WAIT END [latch: ges resource hash list] 3162668560/0xbc827e10 91/0x5b 0/0×0 wait_id=14 seq_num=15 snap_id=1
?RAC????POST LMD(lock Manager)??,????????GES??:2012-05-18 10:37:34.559047 :8001ECF8:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:2:1] KSL POST SENT postee=12 num=6 loc=’kjm.h LINE:1224 ID:kjmpost: post lmd’ id1=0 id2=0 name= type=0
??ksqrcl: release TX????????:ksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
??PMON abort Process A???Transaction2012-05-18 10:37:34.559291 :8001ECFD:db_trace:ktu.c@8652:ktudnx(): [10813:2:1] ktudnx: dec cnt xid:7.13.892 nax:0 nbx:02012-05-18 10:37:34.559301 :8001ECFE:db_trace:ktur.c@3198:ktuabt(): [10444:2:1] ABORT TRANSACTION – xid: 0×0007.00d.0000037c
??Process A?????maclean??TM lock:ksq.c@8540:ksqrcli(): [10704:2:1] ksqrcl: release TM-0001304e-00000000 mode=SXksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
??Process A?????AE ( Prevent Dropping an edition in use) lock:ksq.c@8540:ksqrcli(): [10704:2:1] ksqrcl: release AE-00000064-00000000 mode=Sksq.c@8826:ksqrcli(): [10704:2:1] ksqrcl: SUCCESS
??cleanup process Akjcs.c@4876:kjcsombdi(): GSIPC:SOD: 0xbc79e0c8 action 3 state 0 chunk (nil) regq 0xbc79e108 batq 0xbc79e118GSIPC:kjcs.c@5293:kjcsombdi(): GSIPC:SOD: exit cleanup for 0xbc79e0c8 rc: 1, loc: 0×303kss.c@1414:kssdch(): [10809:2:1] kssdch(0xbd516b80 = process, 3) 1 0 exit
0xbd516b80??PROCESS A ?paddr ????
kssdch???????? ??process???state object SO KSS: delete children of state obj.
PMON ??kmmlrl()????instance goodness??update for session drop deltakmmlrl(): [10257:2:1] KMMLRL: Entering: flg(0×0) rflg(0×4)kmmlrl_process_events(): [10257:2:1] KMMLRL: Events: succ(3) wait(0) fail(0)kmmlrl(): [10257:2:1] KMMLRL: Reg/update: flg(0×0) rflg(0×4)kmmlrl(): [10257:2:1] KMMLRL: Update: ret(0)kmmlrl(): [10257:2:1] KMMLRL: Exiting: flg(0×0) rflg(0×4)
????????PMON???? 3s???”pmon timer”??kslwtbctx(): [10005:2:1] KSL WAIT BEG [pmon timer] 300/0x12c 0/0×0 0/0×0 wait_id=78 seq_num=79 snap_id=1
