RE: determining bind values in deadlock situations

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "Vlado.Barun@xxxxxxx" <Vlado.Barun@xxxxxxx>, "tanel@xxxxxxxxxx" <tanel@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 3 Mar 2009 22:35:17 -0500

If you don't mind me jumping in here, Vlado, I think I can clarify what Tanel 
was trying to say.

Assume you have a simple two session deadlock.  Session 1 takes a TX lock after 
executing
a SQL statement, call it sql_a.  Session 2 takes a TX lock after executing a 
SQL, call it sql_b.
Now, no deadlock yet, no waiting yet.  Now, session 1 executes a SQL, call it 
sql_c, and it tries to take a TX lock
that's not compatible w/ the TX lock held by session 2.  So, session 1 starts 
waiting on session 2
to commit or rollback.  Still no deadlock though.  Finally, session 2 executes 
some SQL, call it sql_d, 
that wants to take a TX lock that's not compatible w/ the TX lock held by 
session 1.  So, it starts waiting on
session 1.  This is a deadlock.

So, oracle raises ORA-60, and causes a tracefile to be written.  The SQLs 
involved in the deadlock
are written to the trace file.  Those are the sql_c and sql_d mentioned above.  
But, this does not tell 
you what SQL statements initially took the TX locks.  That is, the sql_a and 
sql_b from the example 
above are not in the tracefile, and are generally not possible to identify, 
short of having 10046 tracing
enabled.

Hope that helps,

-Mark
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Barun, Vlado [Vlado.Barun@xxxxxxx]
Sent: Tuesday, March 03, 2009 9:07 PM
To: tanel@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: determining bind values in deadlock situations

Tanel, thank you for the very useful information you posted on your blog about 
this.

One correction though to your statement “…but you don't necessarily know when, 
why and by which statement these locks were taken for…”.
The lmd0 trace files do show the statements involved in the deadlock… Maybe I’m 
misunderstanding your statement…


Regards,

Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations
Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun@xxxxxxx

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Tanel Poder
Sent: Tuesday, March 03, 2009 7:38 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: determining bind values in deadlock situations

On a related topic,

It's possible to make Oracle to automatically dump more stuff when ORA-60 or 
any other error happens, with KSD diagnostic events...

You can issue something like this at system level for example (after thinking 
what you want to achieve and what events levels are safe):

SQL> alter session set events '60 trace name hanganalyze_global level 4, 
forever; name systemstate level 522, lifetime 1';

Session altered.
That would give you global hanganalyze every time the error occurs and a local 
systemstate dump only the first time it happens in a session. System state 
dumps process stacks thanks to level 512 + 10, that can be useful for 
diagnosing low-level deadlocks and hangs.

Btw, knowing the current SQL statement and bind variable values for both 
sessions might still not give the full picture of the root cause of the 
deadlock. You know what for the sessions are currently waiting, but you don't 
necessarily know when, why and by which statement these locks were taken for 
(and why they are still being held).

I just published a related blog entry about the diagnostic event setting syntax 
and some more complex constructs:

http://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-events-part-1-syntax-for-ksd-debug-event-handling/


--
Regards,
Tanel Poder
http://blog.tanelpoder.com<http://blog.tanelpoder.com/>

________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Scott
Sent: 03 March 2009 20:43
To: Vlado.Barun@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: determining bind values in deadlock situations
The big question is how do you know you are getting a deadlock?

ORA-60, RAC or not will still get generated. It may take longer in RAC because 
the detection method does change. However you can still get non-table deadlocks 
in RAC that are not always dumped in to a tracefile. Then you need to run 
hanganalyze or a systemstate dump and look for open chains. There serveral bugs 
in 10g that related to library cache related deadlocks that are not always 
indicated in a tracefile.

Scott

________________________________
From: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
To: Jared Still <jkstill@xxxxxxxxx>
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 2, 2009 7:21:12 PM
Subject: RE: determining bind values in deadlock situations
Jared,

Was your test done in a non-RAC environment?
I can find the rowid’s the way you mentioned in a non-RAC environment, but not 
in a RAC environment.

There is no trace file generated for a deadlock in udump on any of my RAC nodes…

Are you able to get a deadlock trace file in a RAC environment?

Thank you for the time you are spending on this…

Regards,

Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations
Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun@xxxxxxx

From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Monday, March 02, 2009 1:28 PM
To: Barun, Vlado
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: determining bind values in deadlock situations




--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2009