Re: Deadlock Question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 Jun 2005 20:27:26 +0000 (UTC)
Message-ID: <d7nq3e$bal$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

Session 1:

create table t1 (id number primary key); create table t2 (id number primary key);

insert into t1 values(1);

Session 2:

insert into t2 values(1);

Session 1:

insert into t2 values(1);

start to wait

Session 2:

insert into t1 values(1);

start to wait

Session 1:

After ca. 3 seconds:
ORA-00060: deadlock detected while waiting for resource

Trace file for session 1:
DEADLOCK DETECTED Current SQL statement for this session:

insert into t2 values(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

TX-0007002b-000169bd 12 9 X 13 10 S

TX-00020022-00016a34 13 10 X 12 9 S

session 9: DID 0001-000C-00000028 session 10: DID 0001-000D-00000002

session 10: DID 0001-000D-00000002 session 9: DID 0001-000C-00000028

Rows waited on:

Session 10: no row

Session 9: no row

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005






<wagen123_at_yahoo.com> wrote in message 
news:1117741042.088472.124300_at_f14g2000cwb.googlegroups.com...


> Oracle10g 10.1.0.3 (64 bit)

> Solaris 2.8 (64 bit)

>

> Billy --> You are correct. As per Metalink Note:223303.1 in 9.2.0 and

> later versions, the foreign keys don't have to be indexed.

>

> Could some one explain the following via an example.

>

> Quote from J. Lewis: the problem of two processes trying to insert new

> rows into two tables, and crossing over in their use of uncommitted

> primary keys.

>

> Quote from Metalink: what happens when you have deadlock on insert due

> to primary key overlap. This is relatively easy to reproduce, and

> results in

> deadlock where each process holds X mode and waits on S mode. Also,

> row waited on is 'no row'.

>

> Thanks for any pointers.

> wagen

>

>

>

> Billy wrote:

>> wagen123_at_yahoo.com wrote:

>> > Hi,

>> >

>> > Quote fron Steve Adams website - "you need indexes on all the foreign

>> > key columns. Otherwise, you need table locks to be disabled on any

>> > table that is missing a foreign key index"

>>

>> If I'm not mistaken this is no longer the case with 9i and later (the

>> child table lock)

>>

>> One always has to be careful with articles on Oracle as Oracle is

>> always elvolving and what was true a year or three ago may not be the

>> case anymore. Can get quite "interesting" at times..

>>

>> --

>> Billy

>

Received on Thu Jun 02 2005 - 15:27:26 CDT