Re: puzzling deadlock

From: Sybrand Bakker <gooiditweg_at_sybrandb.nospam.demon.nl>
Date: Thu, 25 Sep 2003 17:38:15 +0200
Message-ID: <ro26nvo4cb0i251ur1b8fo0sdh4k1bb7ug@4ax.com>

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

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

Not really puzzling. You can find out easily what object refer to data_object_id 6911 (from dba_objects)

Also, my crystall ball tells there isn't any index on the userlogins table, so your statement ends in up in full table scan and consequently in table level lock.
Either you need to revise your strategy (recommended, as it doesn't look like you have a scalable application, but as far as I know I have never seen a scalable jdbc application), or you need to lock the table explicitly with
lock table userlogins in row share mode;

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Sep 25 2003 - 10:38:15 CDT