Re: strange dead lock question

"lsllcm" <lsllcm_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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html