Re: puzzling deadlock

From: Dave Hau <davehau123_at_netscape.net>
Date: Thu, 25 Sep 2003 20:23:39 GMT
Message-ID: <3F734EC9.7070708_at_netscape.net>


Sybrand Bakker wrote:
> 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.

But there is no table lock (TM) shown in the deadlock graph. The only locks shown as either being held or requested are row locks (TX).

Also, how does a full table scan result in a table level lock? Which mode of TM are you talking about?

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

A table lock in row share mode does not exclude other sessions from obtaining row locks in exclusive mode. So I'm not sure this will help resolve the original deadlock in any way.

  • Dave

>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Sep 25 2003 - 22:23:39 CEST

Original text of this message