| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Two kinds of deadlocks (Was: Re: locking issues)
Whenever a deadlock occurs, you need to differentiate between a self-deadlock
and a deadlock between two sessions. Kevin's udump file (if I remember
right)indicates it's a deadlock between two sessions. I've seen messages from
news:comp.databases.oracle.server that increasing INITRANS solved this kind of
deadlock. I think it's obvious PCTFREE shouldn't be too small for this purpose.
If the deadlock is caused by the same session that experiences the deadlock
(again look at the udump file), make sure you don't have any invalid objects on
the table reported in the deadlock udump file, such as invalid triggers. Steve
Adams helped me solve a problem like this (thanks Steve). With this deadlock,
it's helpful to set the following in the SQL session (from
www.ixora.com.au/q+a/1999_10.htm):
alter session set events '4020 trace name processstate forever, level 10';
or if you don't know or can't access the session, put event='4020 trace name processstate forever, level 10' in init.ora file and bounce the database. The udump file thus created when the deadlock occurs next time may have strings like "status=INVL". Look at the corresponding object name after "name=".
(It's often suggested that application should be written in a way to ensure the
same order in running DML on the table. This is less of a problem in most shops
where only one piece of code does that particular DML to that particular table,
as in the case of using a Web server as a middle-tier. But if multiple programs
do the same DML on that table, yes, the order is relevant.)
Yong Huang
yong321_at_yahoo.com
 |
 |