Re: Deadlock encountered

"CrazyKarma" <skalki_at_gmail.com> wrote in message news:1189092441.492168.286450_at_r29g2000hsg.googlegroups.com...
> Using Oracle 10gR1
>
> Apparently, enabling constraints with or without no validate option
> and accessing that table across a DBLINK creates deadlock. Steps are
> broken down below
>
> * Over a database link execute a block of code that will
> disable, exchange partition and enable constraints.
> * Over a dblink query this specific table where the above
> actions are taking place
>
> You will encounter a deadlock. Wierd thing is when you query the table
> in question on the local schema then you won't encounter deadlock.
>
> Combination of DBLINK and enabling constraints creates deadlocks. is
> this an expected behaviour? any rational explantion would help
>
> -CrazyKarma
>

Are you sure it's a real deadlock and not an assumed deadlock based on a lock time-out.

It may be that the local database is allowing read-consistency across DDL, and therefore doesn't have to worry about the timing of the exchange partition, while the remote database can't see into the library cache so well, and has to wait on a lock. Purely conjecture, of course; but it's the sort of variation in local/remote information that could make a big difference.

(It seems a bit odd to use a remote node to operate a partition exchange, by the way, which may be why the mechanism has previously been tested to destruction).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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