Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Locks

Re: Oracle Locks

From: yong huang <yong321_at_yahoo.com>
Date: Fri, 5 Jan 2001 11:16:01 -0800 (PST)
Message-Id: <10732.125875@fatcity.com>


Hi,

Since Jasmine didn't tell us the ORA error number, I want to add that in addition to the between-session (for lack of a better term) deadlocks which are ORA-60 or ORA-12829, there's another type, self-deadlocks caused and experienced by the same session. This is ORA-4020, a library cache deadlock. The way to resolve this deadlock is manually compile invalid triggers (advice from Steve Adams). Also read the new book "Oracle8i Tuning and Administration: The Essential Reference" by Heidi Thorpe, another Oracle expert from Australia.

There's one more type of deadlock associated with OPS, ORA-99. I have no additional comments.

Yong Huang
yong321_at_yahoo.com

you wrote:

Oracle detects deadlocks between two sessions automatically and rollbacks curren
t sql statement for one of them but, of course, it doesn't solve the problem. Yo
u need to determine the cause of the deadlock in your particular case. Have a lo
ok at the user_dump_dest catalog and try to find a trace file which appropriates
 your offended session. In that file, you will be able to find a small table (or
 matrix) which shows relations between the two sessions. Have a look at block mo
des of the sessions, they point the right way for further investigation. The pos
sible causes of deadlocks are
1. unindexed fk fields in a child table
2. number of concurent sessions which update a db block is higher than number of
 available ITL slots in the block and there are no additional free space to expa
nd the next slot
3. you have high competition on a set of particular rows for example, an arbitra
l table
4. ....

The solution of the problem depends on the cause of the deadlock. Take a look at
 www.ixora.com.au You will find detail explanations there.

Ed

We are using Oracle 8.0.4 on Windows NT 4.0 with the Siebel Application. I am e
xperiencing DEADLOCK problems which as a result is causing exports to fail. Can
 anyone make suggestions as to how release these deadlocks apart from shutdown/ statup? How can I identify from v$locks which are the culprits?

Any help would be appreciated.

Regards
Jasmine



Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online! Received on Fri Jan 05 2001 - 13:16:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US