Re: 'no row' deadlock

From: Peter Sylvester <peters_at_mitre.org>
Date: Thu, 29 Nov 2001 12:41:55 -0500
Message-ID: <3C067363.FA22C624@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.
Received on Thu Nov 29 2001 - 11:41:55 CST

Re: 'no row' deadlock - ORA-00060