By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,732 Members | 860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,732 IT Pros & Developers. It's quick & easy.

puzzling deadlock

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 25 Sep 2003 08:20:41 -0700, ro*************@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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.