Re: puzzling deadlock

I think I see how the order of the row set for the implicit query could be different for the four sessions. This could happen if there's an update before the delete like you suggested, and the update causes the b-tree index to rebalance the tree thus affecting the order of the rows in the resulting row set.

• Dave

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:RJKdb.10214$MX.7624_at_newssvr25.news.prodigy.com...
> Can you show me how to get the 4-way deadlock using indexed access paths,
> and why the OP's code looks as if it has to be doing a tablescan?
>
> I guess the most interesting aspect of this deadlock is that the four
> sessions are deadlocked on exactly the _same_ delete statement, with each
> session holding a row and waiting for a row in exactly the _same_ block.
>
> From my understanding of how a delete statement is processed, Oracle
 should
> process the implicit query of selecting all rows where numlogins <=0
 first,
> then go through the resulting row set and delete the rows one by one.
 What
> I don't understand is why the row order in the resulting row set is
> different for each of the four sessions, thus resulting in the 4-way
> deadlock. One possibility is that the four sessions use different query
> plans to process the implicit queries, but why?
>
> Another possibility I thought of was parallelized DML, since Oracle 9.2
 does
> support intra-partition parallelized deletes. But the granularity of
> parallelization is block. Rows within a single block cannot be assigned
 to
> multiple threads. Since this deadlock involves only rows within the same
> block, this cannot be the reason for the deadlock.
>
> Also, it's not clear why the deadlock only happens on one Oracle instance
> and cannot be reproduced on other instances.
>
> You also asked whether connections are being made and broken extremely
> rapidly. How might this be relevant?
>
> Regards,
> Dave
>
>
>
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:bl7blc$81k$1$8302bc10_at_news.demon.co.uk...
> >
> > Your trace file is showing data deadlocks (X -mode
> > is the giveaway), and this is clearly an Oracle 9
> > trace file, as it shows the sql and rows from all the
> > other processes involved.
> >
> > I can engineer a deadlock that looks like this,
> > and I would guess that there is some code in
> > your application that does:
> > update my login row
> > delete all dead login rows
> >
> > BUT - I can't work out how to get the 4-way
> > deadlock without using indexed access
> > paths, and your code looks as if it has to be
> > doing a tablescan.
> >
> > I think you need to track down where your
> > application calls this line from - check the
> > steps that precede it, and then work out
> > the logical error that causes this to happen.
> >
> > Are connections being made and broken
> > extremely rapidly ?
> >
> >
> > --
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > The educated person is not the person
> > who can answer the questions, but the
> > person who can question the answers -- T. Schick Jr
> >
> >
> > One-day tutorials:
> > http://www.jlcomp.demon.co.uk/tutorial.html
> >
> > ____Finland__September 22nd - 24th
> > ____Norway___September 25th - 26th
> > ____UK_______December (UKOUG conference)
> >
> > Three-day seminar:
> > see http://www.jlcomp.demon.co.uk/seminar.html
> > ____USA__October
> > ____UK___November
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > "Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message
> > news:240a4d09.0309250720.759a7626_at_posting.google.com...
> > > I have a deadlock that's happening on one oracle instance but cannot
> > > be reproduced on any other. It is always caused by the same SQL
> > > statement colliding with itself and only happens under very high
 load.
> > >
> > > The statement is
> > >
> > > DELETE from userlogins WHERE numlogins <= 0
> > >
> > > the schema for the userlogins table is
> > >
> > > userlogins (userid integer, numlogins integer)
> > >
> > > The deadlock graph is below. Any help is really appreciated.
> > >
> > > - robert
> > >
> > >
> > >
> > > *** SESSION ID:(107.52266) 2003-09-15 20:08:54.290
> > > DEADLOCK DETECTED
> > > Current SQL statement for this session:
> > > DELETE userlogins WHERE numlogins <= 0
> > > 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-000a0026-00001c05 39 107 X 15 157
> > > X
> > > TX-00110029-00000596 15 157 X 25 133
> > > X
> > > TX-0012000a-0000052c 25 133 X 59 109
> > > X
> > > TX-00060018-0000230a 59 109 X 39 107
> > > X
> > > session 107: DID 0001-0027-00000002 session 157: DID
> > > 0001-000F-00000002
> > > session 157: DID 0001-000F-00000002 session 133: DID
> > > 0001-0019-00000002
> > > session 133: DID 0001-0019-00000002 session 109: DID
> > > 0001-003B-00000002
> > > session 109: DID 0001-003B-00000002 session 107: DID
> > > 0001-0027-00000002
> > > Rows waited on:
> > > Session 157: obj - rowid = 00001AFF - AAABr/AAHAAAGImAAL
> > > (dictionary objn - 6911, file - 7, block - 25126, slot - 11)
> > > Session 133: obj - rowid = 00001AFF - AAABr/AAHAAAGImAAK
> > > (dictionary objn - 6911, file - 7, block - 25126, slot - 10)
> > > Session 109: obj - rowid = 00001AFF - AAABr/AAHAAAGImABl
> > > (dictionary objn - 6911, file - 7, block - 25126, slot - 101)
> > > Session 107: obj - rowid = 00001AFF - AAABr/AAHAAAGImAAS
> > > (dictionary objn - 6911, file - 7, block - 25126, slot - 18)
> > > Information on the OTHER waiting sessions:
> > > Session 157:
> > > pid=15 serial=60169 audsid=634417 user: 14/SCHEMA45
> > > O/S info: user: oracle, term: unknown, ospid: , machine:
 jdbcclient
> > > program: JDBC-1.0-Client
> > > application name: JDBC-1.0-Client, hash value=0
> > > Current SQL Statement:
> > > DELETE userlogins WHERE numlogins <= 0
> > > Session 133:
> > > pid=25 serial=22487 audsid=634303 user: 14/SCHEMA45
> > > O/S info: user: oracle, term: unknown, ospid: , machine:
 jdbcclient
> > > program: JDBC-1.0-Client
> > > application name: JDBC-1.0-Client, hash value=0
> > > Current SQL Statement:
> > > DELETE userlogins WHERE numlogins <= 0
> > > Session 109:
> > > pid=59 serial=35127 audsid=634320 user: 14/SCHEMA45
> > > O/S info: user: oracle, term: unknown, ospid: , machine:
 jdbcclient
> > > program: JDBC-1.0-Client
> > > application name: JDBC-1.0-Client, hash value=0
> > > Current SQL Statement:
> > > DELETE userlogins WHERE numlogins <= 0
> >
> >
>
>
Received on Mon Sep 29 2003 - 02:19:16 CEST