Re: puzzling deadlock
Date: Sun, 28 Sep 2003 20:10:12 +0100
Message-ID: <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
BUT - I can't work out how to get the 4-way
deadlock without using indexed access
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
delete all dead login rows
paths, and your code looks as if it has to be
doing a tablescan.
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 Sun Sep 28 2003 - 21:10:12 CEST