oracle - How to understand ORA-00060 deadlock trace file -
recently got ora-00060 deadlock error.
i read this post , this post , this post i'm not sure problem is: unindexed fk cause or other problem.
my question how understand trace file , how solve it?
below trace file:
*** 2015-06-02 14:53:45.513 deadlock detected ( ora-00060 ) [transaction deadlock] following deadlock not oracle error. deadlock due user error in design of application or issuing incorrect ad-hoc sql. following information may aid in determining deadlock: deadlock graph: ---------blocker(s)-------- ---------waiter(s)--------- resource name process session holds waits process session holds waits tm-00014d94-00000000 497 556 s 332 1414 sx tm-00014d94-00000000 332 1414 sx 416 1038 s tx-0011000f-000000ab 416 1038 x 302 457 s tm-00014d94-00000000 302 457 sx 497 556 s session 556: did 0001-01f1-0000000d session 1414: did 0001-014c-00000022 session 1414: did 0001-014c-00000022 session 1038: did 0001-01a0-0000000c session 1038: did 0001-01a0-0000000c session 457: did 0001-012e-00000028 session 457: did 0001-012e-00000028 session 556: did 0001-01f1-0000000d rows waited on: session 556: obj - rowid = 00014d94 - aaaaaaaaaaaaaaaaaa (dictionary objn - 85396, file - 0, block - 0, slot - 0) session 1414: obj - rowid = 00014d94 - aaaaaaaaaaaaaaaaaa (dictionary objn - 85396, file - 0, block - 0, slot - 0) session 1038: obj - rowid = 00014d94 - aaaaaaaaaaaaaaaaaa (dictionary objn - 85396, file - 0, block - 0, slot - 0) session 457: obj - rowid = 00014fa0 - aaau+gaaeaaaft+aaa (dictionary objn - 85920, file - 4, block - 129918, slot - 0) ----- information other waiting sessions ----- session 1414: sid: 1414 ser: 1424 audsid: 100128 user: 91/sw flags: 0x45 pid: 332 o/s info: user: oracle, term: unknown, ospid: 10179 image: oracle@jwdb client details: o/s info: user: root, term: unknown, ospid: 1234 machine: localhost.localdomain program: jdbc thin client application name: jdbc thin client, hash value=2546894660 current sql: insert t_course_takes (created_at, updated_at, attend, course_id, course_take_type_id, election_mode_id, lesson_id, limit_group_id, paid, remark, semester_id, state, std_id, turn, virtual_cost, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 ) session 1038: sid: 1038 ser: 951 audsid: 100212 user: 91/sw flags: 0x45 pid: 416 o/s info: user: oracle, term: unknown, ospid: 10343 image: oracle@jwdb client details: o/s info: user: root, term: unknown, ospid: 1234 machine: localhost.localdomain program: jdbc thin client application name: jdbc thin client, hash value=2546894660 current sql: delete t_course_takes id=:1 session 457: sid: 457 ser: 2983 audsid: 100099 user: 91/sw flags: 0x45 pid: 302 o/s info: user: oracle, term: unknown, ospid: 10111 image: oracle@jwdb client details: o/s info: user: root, term: unknown, ospid: 1234 machine: jdbcclient program: jdbc thin client application name: jdbc thin client, hash value=2546894660 current sql: insert t_elect_loggers (created_at, updated_at, course_code, course_name, course_take_type_id, course_type, credits, election_mode_id, ip_address, lesson_no, operator_code, operator_name, project_id, remark, screening, semester_id, std_code, std_name, turn, type, virtual_orig, virtual_rest, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 ) ----- end of information other waiting sessions ----- information session: ----- current sql statement session (sql_id=ca9jc1g44ap41) ----- delete t_course_takes id=:1 =================================================== process state ------------- process global information: process: 0x9d0fd98c8, call: 0x95429a500, xact: 0x922b10710, curses: 0x94110e198, usrses: 0x94110e198 ---------------------------------------- so: 0x9d0fd98c8, type: 2, owner: (nil), flag: init/-/-/0x00 if: 0x3 c: 0x3 proc=0x9d0fd98c8, name=process, file=ksu.h line:11459, pg=0 (process) oracle pid:497, ser:7, calls cur/top: 0x95429a500/0x95429a500 flags : (0x0) - flags2: (0x0), flags3: (0x0) intr error: 0, call error: 0, sess error: 0, txn error 0 intr queue: empty ksudlp false @ location: 0 (post info) last post received: 0 0 9 last post received-location: ksq.h line:1877 id:ksqrcl last process post me: 900fd0348 12 0 last post sent: 0 0 9 last post sent-location: ksq.h line:1877 id:ksqrcl last process posted me: 900fce2c8 17 0 (latch info) wait_event=0 bits=0 process group: default, pseudo proc: 0x90102ea98 o/s info: user: oracle, term: unknown, ospid: 10507 osd pid info: unix process pid: 10507, image: oracle@jwdb dump of memory 0x0000000921009c90 0x0000000921009e98 921009c90 00000000 00000000 00000000 00000000 [................] repeat 31 times 921009e90 00000000 00000000 [........] (fob) flags=2050 fib=0x902c3c2b8 incno=0 pending i/o cnt=0 fname=/home/jwdb/oracle/oradata/orcl/undotbs01.dbf fno=3 lblksz=8192 fsiz=238080 (fob) flags=2050 fib=0x902c3c8b8 incno=0 pending i/o cnt=0 fname=/home/jwdb/oracle/oradata/orcl/users01.dbf fno=4 lblksz=8192 fsiz=603680 (fob) flags=2050 fib=0x902c3b6a0 incno=0 pending i/o cnt=0 fname=/home/jwdb/oracle/oradata/orcl/system01.dbf fno=1 lblksz=8192 fsiz=92160 ---------------------------------------- so: 0x94110e198, type: 4, owner: 0x9d0fd98c8, flag: init/-/-/0x00 if: 0x3 c: 0x3 proc=0x9d0fd98c8, name=session, file=ksu.h line:11467, pg=0 (session) sid: 556 ser: 3224 trans: 0x922b10710, creator: 0x9d0fd98c8 flags: (0x45) usr/- flags_idl: (0x1) bsy/-/-/-/-/- flags2: (0x40008) -/- did: , short-term did: txn branch: (nil) oct: 7, prv: 0, sql: 0x9cfed0be0, psql: 0x9afeb20c8, user: 91/sw ksuxds false @ location: 0 service name: sys$users client details: o/s info: user: root, term: unknown, ospid: 1234 machine: localhost.localdomain program: jdbc thin client application name: jdbc thin client, hash value=2546894660 current wait stack: 0: waiting 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=896 snap_id=53 wait times: snap=0.023828 sec, exc=4 min 18 sec, total=4 min 18 sec wait times: max=infinite, heur=4 min 18 sec wait counts: calls=87 os=87 in_wait=1 iflags=0x15a0 there @ least 1 session blocking session. dumping first 3 direct blockers: inst: 1, sid: 457, ser: 2983 inst: 1, sid: 1353, ser: 5618 inst: 1, sid: 907, ser: 5215 dumping final blocker: inst: 1, sid: 1168, ser: 1194 there 1 sessions blocked session. dumping 1 waiter: inst: 1, sid: 1136, ser: 2212 wait event: 'enq: tm - contention' p1: 'name|mode'=0x544d0004 p2: 'object #'=0x14d94 p3: 'table/partition'=0x0 row_wait_obj#: 85396, block#: 0, row#: 0, file# 0 min_blocked_time: 12 secs, waiter_cache_ver: 32536 wait state: fixed_waits=0 flags=0x23 boundary=(nil)/-1 session wait history: elapsed time of 0.000000 sec since current wait 0: waited 'latch: enqueue hash chains' address=0x9313226a0, number=0x1c, tries=0x0 wait_id=839 seq_num=895 snap_id=1 wait times: snap=0.226082 sec, exc=0.226082 sec, total=0.226082 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000000 sec of elapsed time 1: waited 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=894 snap_id=52 wait times: snap=3.000901 sec, exc=4 min 18 sec, total=4 min 18 sec wait times: max=infinite wait counts: calls=87 os=87 occurred after 0.000000 sec of elapsed time 2: waited 'latch: enqueue hash chains' address=0x6000cf38, number=0x1c, tries=0x0 wait_id=838 seq_num=893 snap_id=1 wait times: snap=0.000142 sec, exc=0.000142 sec, total=0.000142 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000000 sec of elapsed time 3: waited 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=892 snap_id=51 wait times: snap=12.003822 sec, exc=4 min 15 sec, total=4 min 15 sec wait times: max=infinite wait counts: calls=86 os=86 occurred after 0.000000 sec of elapsed time 4: waited 'latch: enqueue hash chains' address=0x6000cf38, number=0x1c, tries=0x0 wait_id=837 seq_num=891 snap_id=1 wait times: snap=0.000170 sec, exc=0.000170 sec, total=0.000170 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000000 sec of elapsed time 5: waited 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=890 snap_id=50 wait times: snap=6.001627 sec, exc=4 min 3 sec, total=4 min 3 sec wait times: max=infinite wait counts: calls=82 os=82 occurred after 0.000000 sec of elapsed time 6: waited 'latch: enqueue hash chains' address=0x6000cf38, number=0x1c, tries=0x0 wait_id=836 seq_num=889 snap_id=1 wait times: snap=0.000378 sec, exc=0.000378 sec, total=0.000378 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000000 sec of elapsed time 7: waited 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=888 snap_id=49 wait times: snap=3.000543 sec, exc=3 min 57 sec, total=3 min 57 sec wait times: max=infinite wait counts: calls=80 os=80 occurred after 0.000000 sec of elapsed time 8: waited 'latch: enqueue hash chains' address=0x6000cf38, number=0x1c, tries=0x0 wait_id=835 seq_num=887 snap_id=1 wait times: snap=0.000350 sec, exc=0.000350 sec, total=0.000350 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000000 sec of elapsed time 9: waited 'enq: tm - contention' name|mode=0x544d0004, object #=0x14d94, table/partition=0x0 wait_id=787 seq_num=886 snap_id=48 wait times: snap=3.000880 sec, exc=3 min 54 sec, total=3 min 54 sec wait times: max=infinite wait counts: calls=79 os=79 occurred after 0.000000 sec of elapsed time updated on 6/5/2015
the whole trace file here
Comments
Post a Comment