3 Replies Latest reply: Nov 19, 2012 11:13 AM by 12cdb RSS

    enq: TX - row lock contention-Trace file shared.

    user9256814
      Hi Team,

      I am having enq: TX - row lock contention in top wait event. it is occuring between 10pm - 2am.

      We are having sqlloader job running every one hour(conventional path). But for the specific period of time i am getting "Global Enqueue Services Deadlock detected". Between 10-5. I analyzed realated trace file it is make me little confusion.I found there are four insert query culprit for this locking. out of four sql , tow of them are ran by same SID, other two insert ran by same id. I got confused because how same sid locking them self. trace file below. Please help me on this. during this period oracle maintanence window is active.Thanks

      Trace file:

      *** 2012-10-09 03:40:31.135
      user session for deadlock lock 0x15365e060
      sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
      pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
      image: oracle@sgh0909
      client details:
      O/S info: user: user, term: , ospid: 30888
      machine: sgj89909 program: sqlldr@sgj89909 (TNS V1-V3)
      application name: SQL Loader Conventional Path Load, hash value=1935272243
      action name: der/sample.ctl, hash value=3241011749
      current SQL:

      INSERT INTO sample (ID,SU,TIME,PMAN_S,CV_OS,PRD_US,P_DS,PM_LY_DRD_S,PMP_PY_DIDS,PP_S,PMP_RECV_FRMS) VALUES (:NE,:SID,CAST(TO_DATE(:PNG_TE, 'DDMMYYY,PMP_TRAN_FRMS,PMP_RECV_FRMS) VALUES (:NE_ID,:SUB_ID,CAST(TO_DATE(:POLLING_TIME, 'DDMMYYYYHH24MISS') AS DATE),:PMP_TRAN_OCTS,:PMP_RECV_OCTS,:PMP_DISCARD_US,:PMP_DISCARD_DS,:PMP_POLICY_DISCARD_US,:PMP_POLICY_DISCARD_DS,:PMP_TRAN_FRMS,:PMP_RECV_FRMS) ÷     {+  (Cõ{+  (Cõ{+  ð÷         ð÷         ú;6üΠì/'    „P'            û[þ‡»q         hdšÙÿ   –    $µ            +       л{+  XdšÙÿ   cšÙÿ                         л{+                              M                         L                             šÙ     ƒß    @ešÙÿ  ’Ü    cšÙÿ  lcšÙÿ      M „P'       L

      *** 2012-10-09 03:40:31.533
      Short stack dump:
      <-ksedsts()+315<-ksdxfstk()+32<-ksdxcb()+1764<-sspuser()+112<-__restore_rt()<-semtimedop()+10<-sskgpwwait()+265<-skgpwwait()+156<-ksliwat()+1546<-kslwaitctx()+162<-kjusuc()+3868<-ksipgetctx()+1456<-ksqcmi()+22853<-ksqgtlctx()+4311<-ksqgelctx()+601<-ktcwit1()+333<-ktbgtl0()+1321<-kdimod0()+4581<-kdiblLockPiece()+968<-kdiblLockRange()+1639<-kdiblcfls()+189<-kdiblFinish1()+978<-kdibliFinish()+73<-kxibDoFinish()+90<-kxibFinish()+431<-qerltcEndLoadProcessing()+614<-qerltcLoadStateMachine()+858<-qerltcInsertValues()+218<-qerltcFetch()+1373<-insexe()+1095<-opiexe()+5609<-opiodr()+1220<-ttcpip()+1227<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_main()+244<-_start()+41
      user session for deadlock lock 0x1537feef8
      sid: 1107 ser: 44863 audsid: 8797823 user: 49/iurth flags: 0x45
      pid: 57 O/S info: user: oracle, term: UNKNOWN, ospid: 4540
      image: oracle@sgh0909
      client details:
      O/S info: user: user, term: , ospid: 7569
      machine: sgj89909 program: sqlldr@sgj89909 (TNS V1-V3)
      application name: SQL Loader Conventional Path Load, hash value=1935272243
      action name: der/sample.ctl, hash value=3241011749
      current SQL:

      INSERT INTO sample (ID,SU,TIME,PMAN_S,CV_OS,PRD_US,P_DS,PM_LY_DRD_S,PMP_PY_DIDS,PP_S,PMP_RECV_FRMS) VALUES (:NE,:SID,CAST(TO_DATE(:PNG_TE, 'DDMMYYY,PMP_TRAN_FRMS,PMP_RECV_FRMS) VALUES (:NE_ID,:SUB_ID,CAST(TO_DATE(:POLLING_TIME, 'DDMMYYYYHH24MISS') AS DATE),:PMP_TRAN_OCTS,:PMP_RECV_OCTS,:PMP_DISCARD_US,:PMP_DISCARD_DS,:PMP_POLICY_DISCARD_US,:PMP_POLICY_DISCARD_DS,:PMP_TRAN_FRMS,:PMP_RECV_FRMS) ÷     {+  (Cõ{+  (Cõ{+  ð÷         ð÷         –R<üΠì/'    „P'                            hdšÙÿ  _ΕΎ    $µ            +       л{+  XdšÙÿ   cšÙÿ                         л{+                              M                         L                              šÙ     ƒß    @ešÙÿ  ’Ü    cšÙÿ  lcšÙÿ      M „P'       L
      Short stack dump:
      <-ksedsts()+315<-ksdxfstk()+32<-ksdxcb()+1764<-sspuser()+112<-__restore_rt()<-semtimedop()+10<-sskgpwwait()+265<-skgpwwait()+156<-ksliwat()+1546<-kslwaitctx()+162<-kjusuc()+3868<-ksipgetctx()+1456<-ksqcmi()+22853<-ksqgtlctx()+4311<-ksqgelctx()+601<-ktcwit1()+333<-ktbgtl0()+1321<-kdimod0()+4581<-kdiblLockPiece()+968<-kdiblLockRange()+1639<-kdiblcfls()+189<-kdiblFinish1()+978<-kdibliFinish()+73<-kxibDoFinish()+90<-kxibFinish()+431<-qerltcEndLoadProcessing()+614<-qerltcLoadStateMachine()+858<-qerltcInsertValues()+218<-qerltcFetch()+1373<-insexe()+1095<-opiexe()+5609<-opiodr()+1220<-ttcpip()+1227<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_main()+244<-_start()+41
      user session for deadlock lock 0x1528de490
      sid: 1107 ser: 44863 audsid: 8797823 user: 49/iurth flags: 0x45
      pid: 57 O/S info: user: oracle, term: UNKNOWN, ospid: 4540
      image: oracle@sgh0909
      client details:
      O/S info: user: user, term: , ospid: 7569
      machine: sgj89909 program: sqlldr@sgj89909 (TNS V1-V3)
      application name: SQL Loader Conventional Path Load, hash value=1935272243
      action name: der/sample.ctl, hash value=3241011749
      current SQL:

      INSERT INTO sample (ID,SU,TIME,PMAN_S,CV_OS,PRD_US,P_DS,PM_LY_DRD_S,PMP_PY_DIDS,PP_S,PMP_RECV_FRMS) VALUES (:NE,:SID,CAST(TO_DATE(:PNG_TE, 'DDMMYYY,PMP_TRAN_FRMS,PMP_RECV_FRMS) VALUES (:NE_ID,:SUB_ID,CAST(TO_DATE(:POLLING_TIME, 'DDMMYYYYHH24MISS') AS DATE),:PMP_TRAN_OCTS,:PMP_RECV_OCTS,:PMP_DISCARD_US,:PMP_DISCARD_DS,:PMP_POLICY_DISCARD_US,:PMP_POLICY_DISCARD_DS,:PMP_TRAN_FRMS,:PMP_RECV_FRMS) ÷     {+  (Cõ{+  (Cõ{+  ð÷         ð÷         ÍïEüΠì/'    „P'                            hdšÙÿ  €¦    $µ            +       л{+  XdšÙÿ   cšÙÿ                         л{+                              M                         L                              šÙ     ƒß    @ešÙÿ  ’Ü    cšÙÿ  lcšÙÿ      M „P'       L

      *** 2012-10-09 03:40:32.452
      Short stack dump:
      <-ksedsts()+315<-ksdxfstk()+32<-ksdxcb()+1764<-sspuser()+112<-__restore_rt()<-semtimedop()+10<-sskgpwwait()+265<-skgpwwait()+156<-ksliwat()+1546<-kslwaitctx()+162<-kjusuc()+3868<-ksipgetctx()+1456<-ksqcmi()+22853<-ksqgtlctx()+4311<-ksqgelctx()+601<-ktcwit1()+333<-ktbgtl0()+1321<-kdimod0()+4581<-kdiblLockPiece()+968<-kdiblLockRange()+1639<-kdiblcfls()+189<-kdiblFinish1()+978<-kdibliFinish()+73<-kxibDoFinish()+90<-kxibFinish()+431<-qerltcEndLoadProcessing()+614<-qerltcLoadStateMachine()+858<-qerltcInsertValues()+218<-qerltcFetch()+1373<-insexe()+1095<-opiexe()+5609<-opiodr()+1220<-ttcpip()+1227<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_main()+244<-_start()+41
      user session for deadlock lock 0x1528de2e0
      sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
      pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
      image: oracle@sgh0909
      client details:
      O/S info: user: user, term: , ospid: 30888
      machine: sgj89909 program: sqlldr@sgj89909 (TNS V1-V3)
      application name: SQL Loader Conventional Path Load, hash value=1935272243
      action name: der/sample.ctl, hash value=3241011749
      current SQL:

      INSERT INTO sample (ID,SU,TIME,PMAN_S,CV_OS,PRD_US,P_DS,PM_LY_DRD_S,PMP_PY_DIDS,PP_S,PMP_RECV_FRMS) VALUES (:NE,:SID,CAST(TO_DATE(:PNG_TE, 'DDMMYYY,PMP_TRAN_FRMS,PMP_RECV_FRMS) VALUES (:NE_ID,:SUB_ID,CAST(TO_DATE(:POLLING_TIME, 'DDMMYYYYHH24MISS') AS DATE),:PMP_TRAN_OCTS,:PMP_RECV_OCTS,:PMP_DISCARD_US,:PMP_DISCARD_DS,:PMP_POLICY_DISCARD_US,:PMP_POLICY_DISCARD_DS,:PMP_TRAN_FRMS,:PMP_RECV_FRMS) ÷     {+  (Cõ{+  (Cõ{+  ð÷         ð÷         DlJüΠì/'    „P'                            hdšÙÿ  ¾®    $µ            +       л{+  XdšÙÿ   cšÙÿ                         л{+                              M                         L                              šÙ     ƒß    @ešÙÿ  ’Ü    cšÙÿ  lcšÙÿ      M „P'       L
      Short stack dump:
      <-ksedsts()+315<-ksdxfstk()+32<-ksdxcb()+1764<-sspuser()+112<-__restore_rt()<-semtimedop()+10<-sskgpwwait()+265<-skgpwwait()+156<-ksliwat()+1546<-kslwaitctx()+162<-kjusuc()+3868<-ksipgetctx()+1456<-ksqcmi()+22853<-ksqgtlctx()+4311<-ksqgelctx()+601<-ktcwit1()+333<-ktbgtl0()+1321<-kdimod0()+4581<-kdiblLockPiece()+968<-kdiblLockRange()+1639<-kdiblcfls()+189<-kdiblFinish1()+978<-kdibliFinish()+73<-kxibDoFinish()+90<-kxibFinish()+431<-qerltcEndLoadProcessing()+614<-qerltcLoadStateMachine()+858<-qerltcInsertValues()+218<-qerltcFetch()+1373<-insexe()+1095<-opiexe()+5609<-opiodr()+1220<-ttcpip()+1227<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_main()+244<-_start()+41
      Global blockers dump start:---------------------------------
      DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x2a001d][0x1cb9d],[TX]
      ----------resource 0x1341d3c30----------------------
      resname : [0x2a001d][0x1cb9d],[TX]
      Local node : 4
      dir_node : 4
      master_node : 4
      hv idx : 14
      hv last r.inc : 32
      current inc : 32
      hv status : 0
      hv master : 4
      open options : dd
      grant_bits : KJUSERNL KJUSEREX
      grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
      count : 3 0 0 0 0 1
      val_state : KJUSERVS_NOVALUE
      valblk : 0x5f4f4354532c504d505f524543565f4f OCTS,PMPRECV_O
      access_node : 4
      vbreq_state : 0
      state : x0
      resp : 0x1341d3c30
      On Scan_q? : N
      Total accesses: 8568
      Imm. accesses: 7210
      Granted_locks : 1
      Cvting_locks : 3
      value_block: 5f 4f 43 54 53 2c 50 4d 50 5f 52 45 43 56 5f 4f
      GRANTED_Q :
      lp 0x1537feef8 gl KJUSEREX rp 0x1341d3c30 [0x2a001d][0x1cb9d],[TX]
      master 4 gl owner 0x154b13128 possible pid 4540 xid 39000-0005-00048D1F bast 0 rseq 1329 mseq 0 history 0x14951495
      open opt KJUSERDEADLOCK
      CONVERT_Q:
      lp 0x1537ff0a8 gl KJUSERNL rl KJUSERPR rp 0x1341d3c30 [0x2a001d][0x1cb9d],[TX]
      master 4 gl owner 0x155b0dd28 possible pid 12781 xid 37000-0005-00082EBC bast 0 rseq 1329 mseq 0 history 0x49a5149a
      convert opt KJUSERGETVALUE
      lp 0x1513ef168 gl KJUSERNL rl KJUSERPR rp 0x1341d3c30 [0x2a001d][0x1cb9d],[TX]
      master 4 gl owner 0x159aaf240 possible pid 1347 xid 48000-0005-0001FACA bast 0 rseq 1329 mseq 0 history 0x49a5149a
      convert opt KJUSERGETVALUE
      lp 0x15365e060 gl KJUSERNL rl KJUSERPR rp 0x1341d3c30 [0x2a001d][0x1cb9d],[TX]
      master 4 gl owner 0x154b105e0 possible pid 8601 xid 47000-0005-00024CDE bast 0 rseq 1329 mseq 0 history 0x49a5149a
      convert opt KJUSERGETVALUE
      ----------enqueue 0x1537feef8------------------------
      lock version : 2128337
      Owner node : 4
      grant_level : KJUSEREX
      req_level : KJUSEREX
      bast_level : KJUSERNL
      notify_func : (nil)
      resp : 0x1341d3c30
      procp : 0x1510e0ee0
      pid : 12781
      proc version : 10700
      oprocp : (nil)
      opid : 0
      group lock owner : 0x154b13128
      possible pid : 4540
      xid : 39000-0005-00048D1F
      dd_time : 0.0 secs
      dd_count : 0
      timeout : 0.0 secs
      On_timer_q? : N
      On_dd_q? : N
      lock_state : GRANTED
      Open Options : KJUSERDEADLOCK
      Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
      History : 0x14951495
      Msg_Seq : 0x0
      res_seq : 1329
      valblk : 0x2c3a504d505f504f4c4943595f444953 ,:PMP_POLICY_DIS
      DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
      possible owner[57.4540] on resource TX-002A001D-0001CB9D

      *** 2012-10-09 03:40:32.794
      Submitting asynchronized dump request [28]
      DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x2f0003][0x1eee9],[TX]
      ----------resource 0x1355e6868----------------------
      resname : [0x2f0003][0x1eee9],[TX]
      Local node : 4
      dir_node : 4
      master_node : 4
      hv idx : 64
      hv last r.inc : 32
      current inc : 32
      hv status : 0
      hv master : 4
      open options : dd
      grant_bits : KJUSERNL KJUSEREX
      grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
      count : 1 0 0 0 0 1
      val_state : KJUSERVS_NOVALUE
      valblk : 0x5f4f4354532c504de00e0e5101000000 _OCTS,PMQ
      access_node : 4
      vbreq_state : 0
      state : x0
      resp : 0x1355e6868
      On Scan_q? : N
      Total accesses: 13955
      Imm. accesses: 11923
      Granted_locks : 1
      Cvting_locks : 1
      value_block: 5f 4f 43 54 53 2c 50 4d e0 0e 0e 51 01 00 00 00
      GRANTED_Q :
      lp 0x1528de2e0 gl KJUSEREX rp 0x1355e6868 [0x2f0003][0x1eee9],[TX]
      master 4 gl owner 0x154b105e0 possible pid 8601 xid 47000-0005-00024CDE bast 0 rseq 1980 mseq 0 history 0x49a51495
      open opt KJUSERDEADLOCK
      CONVERT_Q:
      lp 0x1528de490 gl KJUSERNL rl KJUSERPR rp 0x1355e6868 [0x2f0003][0x1eee9],[TX]
      master 4 gl owner 0x154b13128 possible pid 4540 xid 39000-0005-00048D1F bast 0 rseq 1980 mseq 0 history 0x49a5149a
      convert opt KJUSERGETVALUE
      ----------enqueue 0x1528de2e0------------------------
      lock version : 2643245
      Owner node : 4
      grant_level : KJUSEREX
      req_level : KJUSEREX
      bast_level : KJUSERNL
      notify_func : (nil)
      resp : 0x1355e6868
      procp : 0x1510f9800
      pid : 4540
      proc version : 21634
      oprocp : (nil)
      opid : 0
      group lock owner : 0x154b105e0
      possible pid : 8601
      xid : 47000-0005-00024CDE
      dd_time : 0.0 secs
      dd_count : 0
      timeout : 0.0 secs
      On_timer_q? : N
      On_dd_q? : N
      lock_state : GRANTED
      Open Options : KJUSERDEADLOCK
      Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
      History : 0x49a51495
      Msg_Seq : 0x0
      res_seq : 1980
      valblk : 0x2c3a504d505f504f4c4943595f444953 ,:PMP_POLICY_DIS
      DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
      possible owner[71.8601] on resource TX-002F0003-0001EEE9

      *** 2012-10-09 03:40:32.794
      Submitting asynchronized dump request [28]
      Global blockers dump end:-----------------------------------
      Global Wait-For-Graph(WFG) at ddTS[0.108ea] :
      BLOCKED 0x15365e060 3 wq 2 cvtops x1 TX 0x2a001d.0x1cb9d [47000-0005-00024CDE] 4
      BLOCKER 0x1537feef8 3 wq 1 cvtops x28 TX 0x2a001d.0x1cb9d [39000-0005-00048D1F] 4
      BLOCKED 0x1528de490 3 wq 2 cvtops x1 TX 0x2f0003.0x1eee9 [39000-0005-00048D1F] 4
      BLOCKER 0x1528de2e0 3 wq 1 cvtops x28 TX 0x2f0003.0x1eee9 [47000-0005-00024CDE] 4