Re: Export can cause deadlocks in 8i (linux)

Wow,
  I applaud you and all involved in your diligence in solving this problem!

Hope you didn't loose too many nights of sleep.

regards,
cindy

billmil_at_my-deja.com wrote:

> We've dealt for a few months with intermittent deadlocks on our 8i
> database. (8.1.5.0.0 under Linux RH6.1). Every-so-often a user would
> try to insert a record (via a stored procedure) and get an ORA-04020.
>
> It turns out that the export utility (EXP) caused the problem. We run
> a nightly full export of the database and had, for some reasons,
> specified the "INCTYPE=COMPLETE" (i.e. to signify a complete--not
> incremental--backup.). This “INCTYPE” parameter would (somehow) cause
> some tables to deadlock when users tried to create records in the
> morning. Not all tables would experience deadlocks and the deadlocks
> did not occur every day. When users did have deadlocks, they could get
> past the deadlock by simply hitting “back”and resposting the data.
>
> Because the deadlocks weren’t consistent (i.e. not every table and not
> all the time), it took a while to consistently recreate the problem.
> We eventually recreated the problem using small java classes (using
> both the 1.1.1 and 1.2 jdbc drivers) to call the PL/SQL functions. We
> could *not* recreate the problem by calling the pl/sql functions
> directly. (This suggests the problem may lie in the jdbc driver).
>
> For a workaround we removed the “INCTYPE” parameter from our EXP
> parameter file. For us, this parameter was redundant as we perform
> full exports each night (via the “FULL=Y” parameter).
>
> Our Oracle support rep has confirmed this is a new known issue.
>
> Many thanks to the deadlock-maven Steve Adams who graciously and
> helpfully advised us from the Land Down Under and gave great detailed
> info via his book on Oracle internals.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Mar 10 2000 - 00:00:00 CST