puzzling deadlock

[Robert Brown]

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

[Andy Hassall]

Re: puzzling deadlock

On 25 Sep 2003 08:20:41 -0700, robertbrown1971 @yahoo.com (Robert Brown) wrote:

>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.
>
>Current SQL statement for this session:
>DELETE userlogins WHERE numlogins <= 0
---------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

>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)

Given that all the rows are all in the same block, is it an ITL deadlock
issue?

What is your INITRANS and MAXTRANS set to? Is that block too full to allow
expansion of the interested transaction list?

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)