I have found following errors on Oracle.. I cant understand what is happening here. Cant understand which session is waiting for what.... Also is it about Raw level lock or something?
Can anyone please help me?
*** 2015-03-10 10:23:30.288
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001c0006-0000a440 94 2903 X 123 2284 X
TX-001d0011-0000fac7 123 2284 X 94 2903 X
session 2903: DID 0001-005E-00018DE5 session 2284: DID 0001-007B-0001863D
session 2284: DID 0001-007B-0001863D session 2903: DID 0001-005E-00018DE5
Rows waited on:
Session 2903: obj - rowid = 0001285E - AAASheAAIAABMBIAAV
(dictionary objn - 75870, file - 8, block - 311368, slot - 21)
Session 2284: obj - rowid = 0001285E - AAASheAAIAABL9/AAq
(dictionary objn - 75870, file - 8, block - 311167, slot - 42)
----- Information for the OTHER waiting sessions -----
Session 2284:
sid: 2284 ser: 207 audsid: 2893404 user: 87/ESS_ADMIN flags: 0x45
pid: 123 O/S info: user: oracle, term: UNKNOWN, ospid: 3209
image: oracle@pbifmsdb2
client details:
O/S info: user: root, term: unknown, ospid: 1234
machine: pbifmsapp2 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
UPDATE SEC_LOGIN_AUDIT SET LOGOUT_TIME= SYSDATE,LOGOUT_TYPE=:1 ,LST_UPDT_DT= SYSDATE WHERE UPPER(USER_NAME) IN UPPER(:2 ) AND LOGOUT_TYPE IS NULL AND TRUNC(LOGIN_DATE_TIME) BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=cm6x9vt91y3v3) -----
UPDATE SEC_LOGIN_AUDIT SET LOGOUT_TIME= SYSDATE,LOGOUT_TYPE=:1 ,LST_UPDT_DT= SYSDATE WHERE UPPER(USER_NAME) IN UPPER(:2 ) AND LOGOUT_TYPE IS NULL AND TRUNC(LOGIN_DATE_TIME) BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)
===================================================
PROCESS STATE
-------------
Process global information:
process: 0xc000001154e37f70, call: 0xc00000113a5ee668, xact: 0xc0000011489d4808, curses: 0xc0000011598a5760, usrses: 0xc0000011598a5760
----------------------------------------
SO: 0xc000001154e37f70, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0xc000001154e37f70, name=process, file=ksu.h LINE:11459 ID:, pg=0
(process) Oracle pid:94, ser:1, calls cur/top: 0xc00000113a5ee668/0xc00000113a5ee668
flags : (0x0) -
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 134 0 2
last post received-location: ksl2.h LINE:2165 ID:kslpsr
last process to post me: c00000115cda7358 10 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:282 ID:ksasnd
last process posted by me: c00000115cda7358 10 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0xc000001170f9b220
O/S info: user: oracle, term: UNKNOWN, ospid: 9438
OSD pid info: Unix process pid: 9438, image: oracle@pbifmsdb2
Dump of memory from 0xC000001154DE40C8 to 0xC000001154DE42D0
C000001154DE40C0 00000000 00000000 [........]
C000001154DE40D0 00000000 00000000 00000000 00000000 [................]
Repeat 31 times
(FOB) flags=2066 fib=c000001173a56518 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/ess_data.dbf
fno=8 lblksz=8192 fsiz=339200
(FOB) flags=2066 fib=c000001173a55900 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/iwas_data.dbf
fno=6 lblksz=8192 fsiz=3678848
(FOB) flags=2050 fib=c000001173a58348 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/temp01.dbf
fno=201 lblksz=8192 fsiz=4194176
(FOB) flags=2050 fib=c000001173a56518 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/ess_data.dbf
fno=8 lblksz=8192 fsiz=339200
(FOB) flags=2050 fib=c000001173a546d0 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/undotbs01.dbf
fno=3 lblksz=8192 fsiz=231040
(FOB) flags=2050 fib=c000001173a55f00 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/iwas_index.dbf
fno=7 lblksz=8192 fsiz=470400
(FOB) flags=2050 fib=c000001173a55900 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/iwas_data.dbf
fno=6 lblksz=8192 fsiz=3678848
(FOB) flags=2050 fib=c000001173a53ab8 incno=0 pending i/o cnt=0
fname=/iwdmsdata/PIWDMS/DATA/system01.dbf
fno=1 lblksz=8192 fsiz=204800
----------------------------------------
SO: 0xc0000011598a5760, type: 4, owner: 0xc000001154e37f70, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0xc000001154e37f70, name=session, file=ksu.h LINE:11467 ID:, pg=0
(session) sid: 2903 ser: 7 trans: 0xc0000011489d4808, creator: 0xc000001154e37f70
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
DID: , short-term DID:
txn branch: 0xc00000114052f870
oct: 6, prv: 0, sql: 0xc00000116796d070, psql: 0xc0000011675cd1f0, user: 87/ESS_ADMIN
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: root, term: unknown, ospid: 1234
machine: pbifmsapp2 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current Wait Stack:
0: waiting for 'enq: TX - row lock contention'
name|mode=0x54580006, usn<<16 | slot=0x1d0011, sequence=0xfac7
wait_id=471131 seq_num=12392 snap_id=1
wait times: snap=3.004466 sec, exc=3.004466 sec, total=3.004466 sec
wait times: max=1 min 0 sec, heur=3.004466 sec
wait counts: calls=1 os=1
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 2284, ser: 207
Dumping final blocker:
inst: 1, sid: 2284, ser: 207
Wait State:
fixed_waits=0 flags=0x22 boundary=0x0000000000000000/-1
Session Wait History:
elapsed time of 1.625218 sec since current wait
0: waited for 'SQL*Net message from client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471130 seq_num=12391 snap_id=1
wait times: snap=0.000630 sec, exc=0.000630 sec, total=0.000630 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000003 sec of elapsed time
1: waited for 'SQL*Net message to client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471129 seq_num=12390 snap_id=1
wait times: snap=0.000000 sec, exc=0.000000 sec, total=0.000000 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000039 sec of elapsed time
2: waited for 'SQL*Net message from client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471128 seq_num=12389 snap_id=1
wait times: snap=0.002370 sec, exc=0.002370 sec, total=0.002370 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000001 sec of elapsed time
3: waited for 'SQL*Net message to client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471127 seq_num=12388 snap_id=1
wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000069 sec of elapsed time
4: waited for 'SQL*Net message from client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471126 seq_num=12387 snap_id=1
wait times: snap=0.000659 sec, exc=0.000659 sec, total=0.000659 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000004 sec of elapsed time
5: waited for 'SQL*Net message to client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471125 seq_num=12386 snap_id=1
wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.001259 sec of elapsed time
6: waited for 'SQL*Net message from client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471124 seq_num=12385 snap_id=1
wait times: snap=0.000447 sec, exc=0.000447 sec, total=0.000447 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000036 sec of elapsed time
7: waited for 'SQL*Net message to client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471123 seq_num=12384 snap_id=1
wait times: snap=0.000006 sec, exc=0.000006 sec, total=0.000006 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 1.741446 sec of elapsed time
8: waited for 'SQL*Net message from client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471122 seq_num=12383 snap_id=1
wait times: snap=0.001060 sec, exc=0.001060 sec, total=0.001060 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000002 sec of elapsed time
9: waited for 'SQL*Net message to client'
driver id=0x74637000, #bytes=0x1, =0x0
wait_id=471121 seq_num=12382 snap_id=1
wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000038 sec of elapsed time
Sampled Session History of session 2903 serial 7
---------------------------------------------------
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
The history is displayed in reverse chronological order.
sample interval: 1 sec, max history 120 sec
---------------------------------------------------
[3 samples, 10:23:27 - 10:23:29]
waited for 'enq: TX - row lock contention', seq_num: 12392
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x1d0011
p3: 'sequence'=0xfac7
time_waited: >= 2 sec (still in wait)
[3 samples, 10:23:24 - 10:23:26]
not in wait at each sample
[13 samples, 10:23:11 - 10:23:23]
idle wait at each sample
[1 sample, 10:23:10]
not in wait at each sample
[8 samples, 10:23:02 - 10:23:09]
idle wait at each sample
[2 samples, 10:23:00 - 10:23:01]
not in wait at each sample
[5 samples, 10:22:54 - 10:22:59]
idle wait at each sample
[2 samples, 10:22:52 - 10:22:53]
not in wait at each sample
[1 sample, 10:22:51]
idle wait at each sample
[1 sample, 10:22:50]
not in wait at each sample
[2 samples, 10:22:48 - 10:22:49]
idle wait at each sample
[2 samples, 10:22:46 - 10:22:47]
not in wait at each sample
[9 samples, 10:22:37 - 10:22:45]
idle wait at each sample
[2 samples, 10:22:35 - 10:22:36]
not in wait at each sample
[1 sample, 10:22:34]
idle wait at each sample
[2 samples, 10:22:32 - 10:22:33]
not in wait at each sample
[1 sample, 10:22:31]
idle wait at each sample
[1 sample, 10:22:30]
not in wait at each sample
[1 sample, 10:22:29]
idle wait at each sample
[2 samples, 10:22:27 - 10:22:28]
not in wait at each sample
[1 sample, 10:22:26]
idle wait at each sample
[1 sample, 10:22:25]
waited for 'db file scattered read', seq_num: 9258
p1: 'file#'=0x6
p2: 'block#'=0x1fecd0
p3: 'blocks'=0x4
time_waited: 0.007933 sec (sample interval: 0 sec)
[5 samples, 10:22:20 - 10:22:24]
idle wait at each sample
[4 samples, 10:22:16 - 10:22:19]
not in wait at each sample
[3 samples, 10:22:13 - 10:22:15]
idle wait at each sample
[2 samples, 10:22:11 - 10:22:12]
not in wait at each sample
[7 samples, 10:22:04 - 10:22:10]
idle wait at each sample
[6 samples, 10:21:58 - 10:22:03]
not in wait at each sample
[30 samples, 10:21:30 - 10:21:57]
idle wait at each sample
---------------------------------------------------
Sampled Session History Summary:
longest_non_idle_wait: 'enq: TX - row lock contention'
[3 samples, 10:23:27 - 10:23:29]
time_waited: >= 2 sec (still in wait)
Can anyone please help me?
You have had two session that each waited for a resource that was held by the other session.
For example:
Session 1
update table_ .... where x = 100;
Session 1 has now locked the record x=100. No other session can modify this record until Session1 either commits or rolls back (that is: ends the transaction).
Session 2
update table_ .... where x = 200;
Session 2 has now locked the record x=200.
Then, still session 2:
update table_ .... where x=100;
Since Session 1 has locked that record, this statement will wait until it can do the modification (that is, until Session 1 end its transaction).
Session 1
update table_ .... where x=200;
Now, this is the deadlock. Since x=200 is locked by Session 2, Session 1 cannot proceed until the transaction of Session 2 ends. But Session 2 cannot proceed because it is also waiting for Session 1.
Hence the Deadlock Error ORA-00060.