| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: strange dead lock question
On Aug 11, 10:12 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> "lsllcm" <lsl..._at_gmail.com> wrote in message
>
> news:1186822659.905196.247810_at_x40g2000prg.googlegroups.com...
>
>
>
>
>
> > *** 2007-08-10 15:27:04.440
> > *** SESSION ID:(26.789) 2007-08-10 15:27:04.439
> > DEADLOCK DETECTED ( ORA-00060 )
> > The following deadlock is not an ORACLE error. It is a
> > deadlock due to user error in the design of an application
> > or from issuing incorrect ad-hoc SQL. The following
> > information may aid in determining the deadlock:
> > Deadlock graph:
> > ---------Blocker(s)-------- ---------
> > Waiter(s)---------
> > Resource Name process session holds waits process session
> > holds waits
> > TX-00030002-000216dc 20 26 X 16
> > 35 S
> > TX-00010002-00042f40 16 35 X 13
> > 13 X
> > TX-00030002-000216dc 13 13 X 20
> > 26 X
> > session 26: DID 0001-0014-00000004 session 35: DID 0001-0010-00000015
> > session 35: DID 0001-0010-00000015 session 13: DID 0001-000D-00000004
> > session 13: DID 0001-000D-00000004 session 26: DID 0001-0014-00000004
> > Rows waited on:
> > Session 35: obj - rowid = 0000ADA2 - AAAK2iABMAAAAAAAAA
> > (dictionary objn - 44450, file - 76, block - 0, slot - 0)
> > Session 13: obj - rowid = 0000B090 - AAALCQABMAAAGNfAAA
> > (dictionary objn - 45200, file - 76, block - 25439, slot - 0)
> > Session 26: obj - rowid = 0000AFAC - AAAK+sABNAAAEcMAAT
> > (dictionary objn - 44972, file - 77, block - 18188, slot - 19)
> > Information on the OTHER waiting sessions:
> > Session 35:
> > pid=16 serial=34 audsid=403683 user: 84/SPOKANE
> > O/S info: user: , term: , ospid: 1234, machine: test02-server
> > program:
> > Current SQL Statement:
>
> > The question is session 26 does not hold any locks, but it blocked
> > session 35.
>
> > The session 35 is update unique index columns
>
> Which version of Oracle ?
>
> I may have the sequence slightly wrong but it looks roughly like:
> Session 13 has changed a data item that did not change a PK value.
> then changed a data item in a way that changed a primary key to
> value V (say)
>
> Session 26 changes some data items without affecting primary keys,
> and tries to change a data item previously changed by session 13 -
> but not (necessarily changing an item with a PK change)
>
> Session 35 changes some data, and then tries to change an item in the
> same table as that changed by session 13 so that it's PK value ALSO
> changes to value V.
>
> At this point, session 35 is stuck waiting for session 13 in a TX/4 wait,
> BUT it is queued behind session 26 which is also waiting for session 13
> although in the more common TX/6 wait.
>
> At this point we need to attempt an update that pushed the system into
> deadlock, and you see session 26 (the first one to start waiting) dump
> the deadlock graph above.
>
> Doing all the necessary actions in the right order may take a little
> experimentation.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Thanks for your analysis, I checked the sql order as the following,
I will do one experimentation.
Thanks again Received on Sun Aug 12 2007 - 10:02:06 CDT
 |
 |