'no row' deadlock - ORA-00060
Samuel Becker
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.
Jonathan Lewis
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 ...
Nuno Souto
>
>a) Is the table involved in a parent/child relation
> where there is no index on the child table
> representing a foreign key
>
Jonathan, just a quick check:
If the FK is, say, a column called COL_FK. And there is no index on
that column alone. But there is a unique index on (COL_FK,COL_SEQ).
This would have the same effect in negating deadlocks as the FK index
you mention above?
TIA for any feedback. This relates to work I'm currently doing and
I'd appreciate the heads-up.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Jonathan Lewis
Correct.
It doesn't even have to be a unique index.
Any index starting with the FK columns
will do.
--
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.
Nuno Souto wrote in message <3c063c7f.9915051@news>...
Nuno Souto
>
>Correct.
>
>It doesn't even have to be a unique index.
>Any index starting with the FK columns
>will do.
>
Thanx a lot.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Peter Sylvester
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
Samuel Becker
> 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
>
Thanks a lot. I have created an index on a child table and it works !
Nevertheless, there's something i don't understand: this child table
has already an unique index like Nuno Suto has mentionned: "(COL_FK, COL_SEQ)"
where is COL_FK is the foreign key. And if i don't create another index
(not unique) on COL_FK, i get deadlocks...
Regards
Jonathan Lewis
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...@mitre.org>...
Ricky Sanchez
blocks already too tightly packed to permit the itl list to grow. In
such cases, you will have to recreate the table with the INITRANS value
set to the higher value. And, 100 seems an awfully high value. It only
needs to be high enough to cover concurrent transactions per block.
Moreover, if you also set pctfree to a somewhat higher value, the new
itl list will have more room to grow if it needs to.
- ricky