RE: [Fwd: Re: [Fwd: Re: Deadlock inserting into same rowid (different block)]]

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Thu, 21 Mar 2013 16:21:13 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F714F8833C0E_at_EIHQEXVM2.ei.local>



Thx... That saves time... I opened the SR Sev-3 Yesterday afternoon and no response yet to tell me the same thing -- (except surely leaving out the helpful information on what to actually do next time).

Hopefully the deadlock will stop when the developer insures that both the batch processes on each node do not go at the same time, but then that would leave a hole in the discovery.

If it continues to happen, I will follow your steps below, but I might have to leave it for a day or more... hopefully the trace only gathers pertinent info.

Much Obliged,

Joel Patterson
Database Administrator
904 928-2790

--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use
of the addressee and may contain information that is confidential,
subject to copyright or constitutes a trade secret. If you are not the
intended recipient, you are hereby notified that any dissemination,
copying or distribution of this message, or files associated with this
message, is strictly prohibited. If you have received this message in
error, please notify us immediately by replying to the message and
deleting it from your computer. Messages sent to and from us may be
monitored. Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company. [v.1.1]

From: D'Hooge Freek [mailto:Freek.DHooge_at_uptime.be]
Sent: Thursday, March 21, 2013 4:01 PM
To: Patterson, Joel
Cc: oracle-l_at_freelists.org
Subject: Re: [Fwd: Re: [Fwd: Re: Deadlock inserting into same rowid (different block)]]

Joel,

If the bind variables are not listed in the logfile generated for the deadlock, you could try to use following statement to get a errorstack dump after a deadlock:

alter system set events 'deadlock trace name errorstack level 3, lifetime 1';

This will trigger a level 3 errorstack when a session encounters a deadlock.
You could then use following instructions to get the values of the bind
variable:
http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output

Note that this will not show you the variables used by the other session involved, but you can query the table for the values found in the dumpfile (as there is only 1 session rolled back). If you find a record with the same value in the table, then there is something really strange going on (as a sequence value retrieved but not used should be lost).

Also I think the event will only be set for new sessions, not sessions already present on the database.


About the evolution remark, I'm using the evolution email client and it seems that when I post non plain text emails, these are reduced to gibberish like &‡¥üéëiÏÞiÈg¡×¡jÈ­þÊ0žÇ­¡÷r±ê®zwžé›z»ÿø when appearing on freelists.
This has nothing to do with your emails.

Kind regards,

--
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(03) 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer.html




On do, 2013-03-21 at 19:47 +0100, Patterson, Joel wrote:

> Don't take what I said verbatim. It was an analogy to try and describe what I thought was the problem after discussing it with the developer -- with the caveat that we actually had to research the nuts and bolts.
>
> The RAC suggestion was from what I remembered when RAC first came out -- right after oracle parallel server or something like that. At that time there were application specific things necessary to deal with the nodes, and I thought sequences was one of those issues -- so thanks for clarifying that point, at least for modern versions. I don't have any more RAC than attempting to create one eight year ago.
>
> In any event, one of those nuts and bolts is hibernate -- which I have found elicits knashing of teeth and rolling of eyes when I bring it up -- so there is a variable. But I do not believe it is using hibernates SequenceGenerator because the developer spefically pointed out the DB sequence and the fact that the cache was increased to 500 which I double checked in dba_sequences.
>
> Remember, I don't have an actual explanation yet -- one of my next steps is to find the values for the bind variables in the insert statements shown in the trace file and see if they are using the same values -- those insert statements are for the child table.
>
> BTW nice URL on hibernate_sequences... (picture finger pointing to tongue sticking out of mouth). Sorry..., I'm sure there is science behind it.
>
> Not sure about what was meant by evolution email, but my original originated with the trace file deadlock graph. At some point either the mail gets two big or I have answered two responses generating a spin off. Sorry about that if that is what happened.
>
>
>
>
>
> Joel Patterson
> Database Administrator
> 904 928-2790
-- http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 21 2013 - 21:21:13 CET

Original text of this message