Re: 'no row' deadlock - ORA-00060

If I have read your description correctly, it certainly looks as if you can solve the problem with indexes.

You have:

        Parent table        CLOBS
        Child tables:        REGISTRY_1, REGISTRY_2

From time to time, you update a registry row then delete a row from CLOBS. In the absence of an index representing the FK constraints on the registry tables, the following can happen:

Process 1

    update registry_1 rows
Process 2

    updates registry_2 rows
Process 1

    tries to delete a row from CLOBS
    has to wait for a table lock on registry_2 Process 2

    tries to delete a row from CLOBS
    has to wait for a table lock on registry_1     Deadlock

(There are minor variations in the order table locking and/or lock upgrading, depending on the order of execution and order of table dependency - so I've ignore a couple of
details)

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Peter Sylvester wrote in message <3C067363.FA22C624_at_mitre.org>...


>Jonathan,

>

>I have a situation I ran into recently with deadlocking under Oracle, I

>think the first time I've ever run into it, but it involved use of CLOBs

>in parent/child situation.

>

>Table defs (simplified)...

>

>create table clobs (

>  clob_id number not null,  --generated via clob_id_seq

>  clob_data clob not null,

>  constraint pk_clobs primary key(clob_id)

>)

>storage (initial 1M next 1M pctincrease 0)

>pctfree 5 pctused 60 initrans 4;

>

>create table registry_1 (

>  pk_fields   varchar2(20) not null,

>  clob_id number not null,

>  constraint pk_registry_1 primary key (pk_fields)

>)

>storage (initial 512K next 512K pctincrease 0)

>pctfree 5 pctused 60 initrans 4;

>

>alter table registry_1 add constraint fk_registry_1_clob_id

>foreign key (clob_id) references clobs(clob_id);

>

>I have a transaction that does the following:

>1) Get a new clob_id, using a sequence.

>2) Insert a new record into clobs, populate its clob_data.

>3) Update an existing registry record to point to the new clob_id, or

>insert a new registry record pointing to the clob_id.

>4) Delete old (unreferenced) clobs record.

>5) commit.

>

>I have a second registry table, which also references the clobs table,

>but never uses the same clob_id values. I can run update simutaneous

>threads against each registry, and get occasional ORA-0060 errors.

>

>Note that this is a Java app using the thin JDBC drivers, which I

>believe use PL/SQL to implement some of the LOB support.

>

>Is this issue something that can be solved by adding indexes????

>

>tx

>-Peter

>

>Jonathan Lewis wrote:

>>

>> So far I've discovered 8 reasons why you

>> can get TX locks in mode 4 on an Oracle

>> system; and most of them can lead to

>> 'row-free' deadlocks.

>>

>> The most likely causes on a delete statement,

>> especially when you mention 'same table different

>> rows' are:

>>

>> a)    Is the table involved in a parent/child relation

>>         where there is no index on the child table

>>         representing a foreign key

>>

>> b)    Are there any bitmap indexes on the table,

>>         as a row delete locks a bitmap section,

>>         which covers many table - so a second session

>>         could be waiting for the bitmap section to be

>>         released, without being able to identify which

>>         table row was causing the bitmap section to

>>         be locked.

>>

>> --

>> Jonathan Lewis

>> http://www.jlcomp.demon.co.uk

>>

>> Host to The Co-Operative Oracle Users' FAQ

>> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>>

>> Author of:

>> Practical Oracle 8i: Building Efficient Databases

>>

>> Screen saver or Life saver: http://www.ud.com

>> Use spare CPU to assist in cancer research.

>>

>> Samuel Becker wrote in message ...

>> >Hello,

>> >

>> >executing a multi-threaded application, i get puzzling deadlocks.

>> >Indeed, each thread works on the same tables but not on same rows !

>> >

>> >Notes :

>> >- I have tried to put INITRANS value of all tables and index to 100

instead


>> >  of 1 but it did not change anything.

>> >

>> >- I use Oracle sequences, and sequence name appears in Oracle log. Can

>> Oracle

>> >  sequence induce deadlock ? I don't think so but..

>> >

>> >

>> >

>> >Here's a sample of Oracle log:

>> >

>> >

>> >

>> >*** 2001-11-28 16:54:45.280

>> >*** SESSION ID:(16.2237) 2001-11-28 16:54:45.270

>> >DEADLOCK DETECTED

>> >Current SQL statement for this session:

>> > DELETE FROM TRK_ELEMENTARYTRANSPORT WHERE TRK_ELEMENTARYTRANSPORT_ID=:1

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

>> >TM-000067fb-00000000        18      16    SX   SSX       23      20

SX


>> SSX

>> >TM-000067fb-00000000        23      20    SX   SSX       18      16

SX


>> SSX

>> >session 16: DID 0001-0012-00000002 session 20: DID 0001-0017-00000002

>> >session 20: DID 0001-0017-00000002 session 16: DID 0001-0012-00000002

>> >Rows waited on:

>> >Session 20: no row

>> >Session 16: no row

>> >

>> >Thanks.