When testing my application with multiple users, I have encountered an ORA-00060 deadlock error. I'm not sure how to determine the cause of this error, so if anyone could help me it would be greatly appreciated.
I looked in the trace file generated for this event and it shows me which query caused this:
UPDATE TABLE_A SET CK=CK+1 WHERE A_ID=(
SELECT A_ID FROM TABLE_B WHERE SOME_COLUMN=:SOMECOLUMN
)
It also generated this:
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-0002a675-00000000 51 200 SX SSX 52 22 SX SSX
TM-0002a675-00000000 52 22 SX SSX 51 200 SX SSX
session 200: DID 0001-0033-0000014A session 22: DID 0001-0034-0000005A
session 22: DID 0001-0034-0000005A session 200: DID 0001-0033-0000014A
Rows waited on:
Session 200: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
(dictionary objn - 173745, file - 20, block - 339, slot - 0)
Session 22: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
(dictionary objn - 173745, file - 20, block - 339, slot - 0)
How do I use the above information to find out what caused the problem? There aren't any unindexed foreign keys on either TABLE_A or TABLE_B (which I believe is commonly the cause of this type of error).
When I run this query:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_ID = 173745;
I get the name of a primary key on a completely different table! Could this be a clue as to why the deadlock is happening?