Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: deadlocks and redologs interaction

Re: deadlocks and redologs interaction

From: Brian Peasland <>
Date: Wed, 12 Sep 2007 09:40:20 -0500
Message-ID: <46e7ee50$0$31535$>

carab1n3r wrote:
> On Sep 11, 7:02 pm, Steve Howard <> wrote:

>> On Sep 11, 9:55 pm, carab1n3r <> wrote:
>>> On Sep 11, 5:32 pm, Steve Howard <> wrote:
>>>> On Sep 11, 8:20 pm, carab1n3r <> wrote:
>>>>> I'm still relatively green as a DBA, so excuse the possibly simple
>>>>> question...
>>>>> We have a DB that is seeing a lot of deadlocks, occasionally hundreds
>>>>> at a time.  Yes, typically this is an external-application/development
>>>>> problem.
>>>>> However, I'm trying to determine and/or explain what impact lots-o-
>>>>> deadlocks has on redolog, particularly the switching.  The reason I
>>>>> mention it is that during these "deadlock storms", we occasionally run
>>>>> out of redologs to switch to.
>>>>> The drumbeat I'm hearing is to "just add more redolog groups!!!" since
>>>>> that's easy to do, but we're only running into this redolog-switching
>>>>> problem during the deadlock storms, and my gut is that this is just a
>>>>> bandaid and will eventually not resolve the problem as deadlock rates
>>>>> continue to increase.
>>>>> So can someone explain to me (in such a way that I can explain it to
>>>>> others), when we have lots of deadlocks, what impact does that have on
>>>>> redologs?
>>>>> Thanks a lot in advance for the advice.
>>>> Hi,
>>>> Which version and OS is this running on?  What do you mean by
>>>> deadlocks?  Are exceptions being thrown in the app with an ORA-
>>>> prefix?
>>>> Regards,
>>>> Steve
>>> Oracle enterprise 10g (
>>> RHEL4u3
>>> In alert.log:
>>> ORA-00060: Deadlock detected. More info in file [path to
>>> file]SID_ora_23130.trc
>>> A count on "Deadlock detected" shows 702 deadlocks in the alert.log
>>> just for today (Sept 11).
>>> In our other logs, we see a lot of Exclusive and "Row Exclusive"
>>> locks.
>> In general, those types of deadlocks have nothing to do with redo logs
>> (you would have to really stretch to make the connection).  Far more
>> often, deadlocks are caused by unindexed foreign keys.
>> Search on for a script to point you to any foreign
>> keys without an index, as well a full discussion as to why this causes
>> deadlocks.
>> Regards,
>> Steve

> Thanks for the tips on unindexed foreign keys.
> It sound like you understand my issue, but just to clarify (in case I
> didn't explain things well), I personally am clear that redologs are
> not causing deadlocks. What I am looking for, though, is an
> explanation of the impact an extensive # of deadlocks can have on the
> ability of oracle to put redologs in an INACTIVE state in v$log.

There is no connection here. As I stated in my previous response to a similar thread you posted in c.d.o.m, it is more likely that your application users are generating large amounts of redo which is why you see the high rate of redo log switching. Since your app is prone to deadlocks, the higher rate of DML can lead to higher incidences of deadlocks.

To be more specific to your question above, there is no impact to the number of deadlocks and the ability to put redo logs in an INACTIVE state.

> My suspicion is that an extensive # of deadlocks can interfere with
> redolog rotation; since redologs are circular and sequential, I
> suspect a redolog cannot be freed up until a deadlocked transaction is
> completed or rolled back. Have enough deadlocks across enough
> redologs, and the DB has to stop accepting new transactions in the
> interim (we have 10 groups, btw. 100MB each).

Your suspicion is incorrect. You have 10 groups of 100MB each. Doing the math, this leads to 1GB of total redo (10*100MB=1000MB=1GB). If your suspicion were correct, the largest transaction could only be 1GB. Yet Oracle will let you have a transaction that generates more than Xgroups*Ysize_per_group redo bytes.

> Perhaps my paragraph above is a good synopsis of our problem, but I'd
> like to hear whether my suspicions are an accurate interpretation of
> how Oracle manages deadlocks WRT redologs.

Deadlocks have nothing to do with redo logs. As I stated before in my reply to the thread you posted in c.d.o.m, this is a red herring. It is a symptom of poor application design. Your poorly written application is generating tons of DML activity, hence the redo log switches. However, it is that specific DML activity (not the redo logs) which is leading to the deadlock conditions.



Brian Peasland

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Posted via a free Usenet account from
Received on Wed Sep 12 2007 - 09:40:20 CDT

Original text of this message