Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Deadlock Question
Session 1:
insert into t1 values(1);
Session 2:
Session 1:
start to wait
Session 2:
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...Received on Thu Jun 02 2005 - 15:27:26 CDT
> 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
>
![]() |
![]() |