Are there any tips & tricks folks have for resolving deadlocks reported
in the alert log files?

Currently on 8.1.7.4


--

jeremy

Jeremy wrote:

> Are there any tips & tricks folks have for resolving deadlocks reported
> in the alert log files?
>
> Currently on 8.1.7.4

Design better schemas and write better code.

Deadlocks in a properly designed schema in which appropriate code
is written are so rare as to be almost unworthy of comment.

The first thing to ask is "What is causing the deadlocks"? What
business process is being supported by what code?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

The dead lock graph in the trace file will give you an idea which
tables are involved in the dead lock.
Also you can write a SERVERERROR trigger to catch more info during the
dead lock error.
regards
Srivenu

On Mon, 16 Feb 2004, newspostings@hazelweb.co.uk wrote:
> Are there any tips & tricks folks have for resolving deadlocks
> reported in the alert log files?
>
> Currently on 8.1.7.4

I've found that you have to walk through you database access code
and make sure you've ordered all access the same way. If you
have implemented subtypes within your database design, these can
cause deadlock headaches as well.

The hard thing is that Oracle doesn't tell you what objects were
involved in the deadlock, and it can be anything.

Good luck. Your codebase will be more solid when you solve it.
It should invariably be an application design/coding issue.

--
Galen Boyer

On 19 Feb 2004 11:12:17 -0600, Galen Boyer <galenboyer@hotpop.com>
wrote:

>The hard thing is that Oracle doesn't tell you what objects were
>involved in the deadlock, and it can be anything.

I don't think this is true. Ora-060 will always result in a trace file
with detailed info about open cursors, lockmodes etc.


--
Sybrand Bakker, Senior Oracle DBA

On Thu, 19 Feb 2004, gooiditweg@sybrandb.demon.nl wrote:
> On 19 Feb 2004 11:12:17 -0600, Galen Boyer
> <galenboyer@hotpop.com> wrote:
>
>>The hard thing is that Oracle doesn't tell you what objects
>>were involved in the deadlock, and it can be anything.
>
> I don't think this is true. Ora-060 will always result in a
> trace file with detailed info about open cursors, lockmodes
> etc.

Hm... Sets up a deadlock situation... Looks at trace file.

I'll be damned. I solved an insidious deadlocking issue a few
weeks ago and didn't use that info. How many times do I have to
tell myself, just look at the trace file you dummy!

--
Galen Boyer

"Galen Boyer" <galenboyer@hotpop.com> wrote in message
news:ud68asvld.fsf@standardandpoors.com...
> On Thu, 19 Feb 2004, gooiditweg@sybrandb.demon.nl wrote:
> > On 19 Feb 2004 11:12:17 -0600, Galen Boyer
> > <galenboyer@hotpop.com> wrote:
> >
> >>The hard thing is that Oracle doesn't tell you what objects
> >>were involved in the deadlock, and it can be anything.
> >
> > I don't think this is true. Ora-060 will always result in a
> > trace file with detailed info about open cursors, lockmodes
> > etc.
>
> Hm... Sets up a deadlock situation... Looks at trace file.
>
> I'll be damned. I solved an insidious deadlocking issue a few
> weeks ago and didn't use that info. How many times do I have to
> tell myself, just look at the trace file you dummy!
>
> --
> Galen Boyer


LOL! I remember the first time I got the "instance terminated disconnection
forced' error message with 9i... panic! terror!... bloody Linux!!.. I
thought I must have done the Linux or Oracle installs wrong, so was about to
re-install the lot... when I thought, "hang on, let's just look at the alert
log" -it's what I always drill into students, after all. Sure enough:
incorrect undo tablespace name. Phew!

Regards
HJR
--
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------

On Fri, 20 Feb 2004, hjr@dizwell.com wrote:
>
> "Galen Boyer" <galenboyer@hotpop.com> wrote in message
> news:ud68asvld.fsf@standardandpoors.com...
>> On Thu, 19 Feb 2004, gooiditweg@sybrandb.demon.nl wrote:
>> > On 19 Feb 2004 11:12:17 -0600, Galen Boyer
>> > <galenboyer@hotpop.com> wrote:
>> >
>> >>The hard thing is that Oracle doesn't tell you what objects
>> >>were involved in the deadlock, and it can be anything.
>> >
>> > I don't think this is true. Ora-060 will always result in a
>> > trace file with detailed info about open cursors, lockmodes
>> > etc.
>>
>> Hm... Sets up a deadlock situation... Looks at trace file.
>>
>> I'll be damned. I solved an insidious deadlocking issue a few
>> weeks ago and didn't use that info. How many times do I have
>> to tell myself, just look at the trace file you dummy!
>>
>> --
>> Galen Boyer
>
>
> LOL! I remember the first time I got the "instance terminated
> disconnection forced' error message with 9i... panic!
> terror!... bloody Linux!!.. I thought I must have done the
> Linux or Oracle installs wrong, so was about to re-install the
> lot... when I thought, "hang on, let's just look at the alert
> log" -it's what I always drill into students, after all. Sure
> enough: incorrect undo tablespace name. Phew!

I wish I was able to join in with the "Phew", but alas, I did it
the old-fashioned way, I tried, cussed, tried something else,
cussed some more, tried something else, blamed someone who left
the company, tried something else, went for coffee...

--
Galen Boyer

Make sure you have your foreign keys indexed. Unindexed foreign keys are a
major cause of deadlocks.


"Jeremy" <newspostings@hazelweb.co.uk> wrote in message
news:MPG.1a9af9c43efcadbb989aba@news.individual.ne t...
> Are there any tips & tricks folks have for resolving deadlocks reported
> in the alert log files?
>
> Currently on 8.1.7.4
>
>
> --
>
> jeremy