Re: Deadlock - advice needed
- From: "Oxnard" <oxnardNO_SPAM@xxxxxxxxxxx>
- Date: Mon, 24 Jul 2006 07:04:34 -0500
Wow, I did not relize that. Thanks
"Charles Hooper" <hooperc2000@xxxxxxxxx> wrote in message
news:1153608524.576336.206130@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
.
- References:
- Deadlock - advice needed
- From: oliviug
- Re: Deadlock - advice needed
- From: Charles Hooper
- Deadlock - advice needed
- Prev by Date: Re: ORA-29850: invalid option for creation of domain indexes
- Next by Date: Re: AIX vs Solaris
- Previous by thread: Re: Deadlock - advice needed
- Next by thread: Re: random records OT
- Index(es):
Relevant Pages
|
Loading
