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
