Deadlock - advice needed

From: oliviug <oliviug_at_gmail.com>
Date: 22 Jul 2006 09:25:19 -0700
Message-ID: <1153585519.110909.219810@m73g2000cwd.googlegroups.com>

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 ? Received on Sat Jul 22 2006 - 11:25:19 CDT