Note that the waiters are both waiting on SHARE (S)
locks, not exclusive locks. In the circumstances you
describe, this usually indicates a shortage of ITL slots
in data blocks. Since you are running two update
programs in parallel, and they are presumably updating
lots of rows - which may share blocks even if the rows
do not overlap - then you need to set INITRANS on the
table D03_TAR_COL to 2, just for these processes.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Jaret Lafoudravecmonhné wrote in message ...
>Hello,
>
>I've two program running in the same time, and it often fail in some
>deadlock problems. I'm sure that the 2 jobs doesn't run on the same rows of
>the same table.
>
>This is an extract of the trc file :
>
>
>DEADLOCK DETECTED
>Current SQL statement for this session:
>UPDATE D03_TAR_COL SET
>NSQ_T01=:b1,MNT_PVT_D03=ROUND(:b2,4),MNT_PAC_D03=ROUND(:b3,4),MNT_MAR_D03=R
O
>UND(:b4,4),PCT_MAR_D03=ROUND(:b5,2),DAP_D03=:b6,DMJ_TEC=:b7,UMJ_TEC=:b8,CVE
_
>TEC=CVE_TEC + 1 WHERE COF_NSQ_T16 = :b9 AND NSQ_OFR = :b10 AND NOR_COF =
>:b11 AND NSQ_T16 = :b12 AND NSQ_POF = :b13
>----- PL/SQL Call Stack -----
> object line object
> handle number name
>984414ac 899 package body PCO00.CDB_D03_MAJ
>9815f410 2 anonymous block
>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-000a001a-00000a58 16 25 X 19 24
>S
>TX-00080016-00000d09 19 24 X 16 25
>S
>session 25: DID 0001-0010-00000002 session 24: DID 0001-0013-00000002
>session 24: DID 0001-0013-00000002 session 25: DID 0001-0010-00000002
>Rows waited on:
>Session 24: no row
>Session 25: no row
>
>
>
>Why 'no row' in the 2 last line ?
>
>
>Ludovic Cayen
>
>
>
>
Received on Thu May 23 2002 - 09:29:17 CDT
