Re: puzzling deadlock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 28 Sep 2003 20:10:12 +0100
Message-ID: <bl7blc$81k$1$8302bc10@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 Sun Sep 28 2003 - 14:10:12 CDT