Re: puzzling deadlock

On 25 Sep 2003 08:20:41 -0700, robertbrown1971_at_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)