From: Jonathan Lewis on 3 Nov 2005 07:37
"Dusan Bolek" <spambin(a)seznam.cz> wrote in message
> Thank you, that looks like something. I will try to catch a deadlock
> message in the alert log as quickly as possible, then find user
> responsible (via pid) and from V$SQL the statement that could be
> causing this trouble. It is not 100% reliable way, but could be
If the deadlock is on mode 5 enqueues at both
ends, it is most likely to be the foreign key locking
issue, possibly with a referential integrity constraint
declared as 'on delete cascade' but not covered by
a suitable index.
Look of foreign keys without indexes, and then
look for code that deletes the parent, or updates
the columns of the indexes protecting the parent key.
Cost Based Oracle: Fundamentals
Now available to pre-order.
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated 4th Sept 2005
From: Dusan Bolek on 3 Nov 2005 11:39
Thank you, Jonathan. That looks very interesting. Unfortunately, the
code accessing this database is too complex to be investigated
in this way. However, I have got from dba_constraints all referential
constraints without associated index that were created with ON
DELETE CASCADE. There are twelve of them and I will ask the guy
responsible for this database to create appropriate indexes for
all of them. Maybe that will solve the problem.
From: Dusan Bolek on 4 Nov 2005 10:45
> Metalink Note:262226.1 offers some interpretation. Your process 131321
> on node 1 is waiting for a TX lock in mode 5 (the two hex numbers,
> 0x2b90011 and 0x5f20, may be id1 and id2 in v$lock, respectively).
> Process 131317 on the same node is holding it.
The strange thing is that I can find none of the processes that are
participating in a deadlock.
When checked against V$PROCESS, even immediately after a new dead lock
emerges in trace file,
there is no row returned from that table.
From: yong321 on 4 Nov 2005 15:24
Dusan Bolek wrote:
> > Metalink Note:262226.1 offers some interpretation. Your process 131321
> > on node 1 is waiting for a TX lock in mode 5 (the two hex numbers,
> > 0x2b90011 and 0x5f20, may be id1 and id2 in v$lock, respectively).
> > Process 131317 on the same node is holding it.
> The strange thing is that I can find none of the processes that are
> participating in a deadlock.
> When checked against V$PROCESS, even immediately after a new dead lock
> emerges in trace file,
> there is no row returned from that table.
> Dusan Bolek
You're right. I did some testing and find that the two numbers,
[131321,1285], in your case, do not in any way denote a process
(Note:262226.1 says the first number is PID). Instead they correspond
to transaction_id0 and transaction_id1 of v$ges_blocking_enqueue,
respectively (or the same in v$dlm_locks). Documentation says they're
lower and upper 4 bytes of the transaction identifier where the lock
belongs to. I can't find more information about it. Perhaps for our
purpose, we can conceptually think of the combination of the two
numbers, i.e. a transaction identifier, as a process identifier.
By the way, I do see the SQL involved in the global deadlock (tested in
126.96.36.199.0 on Linux):
*** 2005-11-04 13:38:33.199
user session for deadlock lock 0x7553ab14
Current SQL Statement:
update test set a = :"SYS_B_0" where a = :"SYS_B_1"
Global Wait-For-Graph(WFG) at ddTS[0.28] :
BLOCKED 0x7553ab14 5 [0xf001d][0x8353],[TX] [2162689,7995] 0
In any case, follow Jonathan's practical advice.
From: dusan75 on 6 Nov 2005 13:46
Yes, I have got info from Oracle that in some cases this numbers should
be related to transaction id instead of process id. However, I do not
know how it is encoded, because I'm also using Log Miner to find
offending SQL, but still with no success. :-(