Wow, I did not relize that. Thanks
"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
news:1153608524.576336.206130_at_i3g2000cwc.googlegroups.com...
> 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 Mon Jul 24 2006 - 07:04:34 CDT
