I think I'll resist the temptation to review
the entire trace file. However, since this
is a v9 deadlock dump, I think you should
find that you have a complete processstate
dump after the initial deadlock graph.
Somewhere near the end of the dump you
should find the CURSOR section, which
should
This looks like a v9 trace file, which means
there may be new issues involved that I
haven't come across yet. For example, 9.2
introduces a mode 2 TM lock on pk/fk activity
for some reason that I haven't worked out,
so this may be a side-effect.
However, (assuming no big changes from v8)
this is
Yes it is 9013. This is not an array based update. As per the trace file
same statement is being executed by both sessions. I can directly send you
the trace file if there is a need.
There are triggers on the tables, I'll look into parent table activity. But
there are indexes on all foreign keys
Jonathan,
What do you make out of following deadlock graph. This is happenning when 2
instance of same batch process are running. We are absolutely certain that
these batch processes are not working on same set of records (although
records can be in same block). object f9d5 is wcu_po_line table.
It is possible for ITL starvation to result in deadlocks,
and changing INITRANS (and rebuilding the problem
objects) would help - but no-one can give you an
appropriate answer without seeing the deadlock
graph that usually comes as
The following information
It would also help if you told use
Hello Dick
If both processes first update table a and then table b there will be no
deadlock problem.
The first process will lock the row for update in table a and goes on to
update table b.
The second one will attempt to lock the row in table a and will wait for the
first to finish.
This can
Jonathan,
What do you mean by ITL starvation? And how would it result in a
deadlock?
Dan Fink
-Original Message-
Sent: Friday, December 20, 2002 1:44 AM
To: Multiple recipients of list ORACLE-L
It is possible for ITL starvation to result in deadlocks,
and changing INITRANS
Set maxtrans to 2 on a table.
Insert three rows into the same block
and commit.
Use three sessions to update one row
each. The third transaction has to wait
for one of the other two transactions to
commit, as there are insufficient ITL
(interested transaction list) entries for
three
Jonathan,
Thanks for the explanation. I've always addressed deadlocks as a
row-level issue, but now I see how it can also be a block-level issue. What
a way to start the holidays, with new knowledge (and some tests to run!).
Dan Fink
-Original Message-
Sent: Friday, December 20,
Jonathon,
This produces ITL waits for sessions Y and Z; but this is not deadlock. The
deadlock occurs due to a situation where the Session 1 waits for something
to finish in Session 2, which in turn waits for Session 1 AND, this is
important, Oracle detects it and kills one of them, rolling back
There is a deadlock here - but I confused the
issue by making complete garbage of the last
phrase. Instead of:
both X and Y might end up waiting for A.
I should have said
both Y and Z might end up waiting for X
(which is when you won't get the deadlock)
The critical point comes in the
Please allow me to provide a case in point on the subject that we discovered and
fixed some time ago.
We have 2 PeopleSoft SQR's that are used for material movement into and out
of the stock room. Both run on a scheduled basis and it is NOT odd to see both
running at the same time. Now
12 matches
Mail list logo
