Re: Deadlock - advice needed

oliviug wrote:
> Hi,
>
> by far not an oracle db expert, but I am trying to investigate a
> deadlock issues.
>
> 2 sessions doing this at the same time:
>
> Lock table order_release_bp in share mode;
>
> delete from order_release_bp where ...
>
> DBAs reported deadlocks and this from the logs:
>
> eadlock graph:
> ---------Blocker(s)-------- ---------Waiter(s)---------
> Resource Name process session holds waits process session holds waits
> TM-0002cd0f-00000000 75 196 S SSX 70 208 S SSX
> TM-0002cd0f-00000000 70 208 S SSX 75 196 S SSX
>
> *** SESSION ID:(196.43568) 2006-07-10 06:56:17.957
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM ORDER_RELEASE_BP WHERE MERGE_ID = :B1
>
>
> After reading about the Oracle locking, my guess is the the stored
> procs emits a share table lock(don't know what is the reason for this,
> as this would make sense if you wanted to query and block updates), and
> the delete statement is blocked.
>
> both transaction execute simoultaneosly puting a shared lock which
> block each other's delete statement which require SSX.
>
> I am right to think so ?

Expert Oracle Architecture 9i and 10g (Tom Kyte): Unindexed foreign keys may cause deadlocks - Oracle places a full table lock on the child table after modification of the parent table if the primary keys of the parent table are modified or if the parent table's row is deleted. See page 206 for a SQL statement to identify where this situation may exist in the database.

ALTER TABLE TABLENAME DISABLE TABLE LOCK; can be used to modify tables so that it is much harder to drop tables - can also be used to detect a full table scan due to an unindexed foreign key.

Oracle Database Concepts 10g Release 2:
"Unindexed foreign keys cause DML on the primary key to get a share row exclusive
table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign
key table. This prevents DML on the table by other transactions. The SSX lock is
released immediately after it is obtained. If multiple primary keys are updated or
deleted, the lock is obtained and released once for each row."

Some applications are written such that every column is updated when the program saves a record, even if that column is unchanged. If this happens, a full table lock may occur.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 22 2006 - 17:48:44 CDT