Re: strange dead lock question

On Aug 12, 11:02 pm, lsllcm <lsl..._at_gmail.com> wrote:
> 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,
>
> 1. update R3APPNBR
> UPDATE R3APPNBR SET B1_PER_ID1 = B1_PER_ID1 WHERE SERV_PROV_CODE
> = :1 AND B1_PER_ID1 = :2
>
> 2. insert into B1PERMIT (There is one unique index on
> (B1_TRACKING_NBR) )
> INSERT INTO B1PERMIT VALUES (SERV_PROV_CODE, B1_PER_ID1,
> B1_PER_ID2, ...,B1_TRACKING_NBR)
>
> 3. update RSERV_PROV by pk
> UPDATE RSERV_PROV SET REC_DATE= :1 ,REC_FUL_NAM=:2 ,LAST_PROJECT_NBR
> = :3 WHERE SERV_PROV_CODE = :4 AND REC_STATUS = 'A'
>
> 4. update B1PERMIT.B1_TRACKING_NBR column
> UPDATE B1PERMIT set B1_PER_GROUP = :1 , B1_MODULE_NAME = :6 ,
> PROJECT_NBR = :7 , B1_TRACKING_NBR = :8 WHERE SERV_PROV_CODE = :9
> AND B1_PER_ID1 = :10 AND B1_PER_ID2 = :11 AND B1_PER_ID3 = :12
>
> The step 2 and 4 is the root cause.
>
> I will do one experimentation.
>
> Thanks again- Hide quoted text -
>
> - Show quoted text -

After I do the experimentation today, it is the root cause

Thanks Received on Mon Aug 13 2007 - 02:07:38 CDT