Export can cause deadlocks in 8i (linux)

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 Thu Mar 09 2000 - 00:00:00 CST