puzzling deadlock

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