I had checked alertlog file and found the following
Tue Jan 16 11:37:57 2007
ORA-000060: Deadlock detected. More info in file E:\oracle\admin\wizerp\udump\ORA02996.TRC.
and when i checked that ORA02996.TRC file
Current SQL statement for this session:
SELECT 1 FROM journal_mt WHERE journal_id=
( SELECT NVL(MAX(journal_id),1 - 1) FROM journal_mt WHERE journal_id BETWEEN 1 AND 99999999) FOR UPDATE
i also had found some other statements in that particular ORA02996.TRC file
But i suspect that the above mentioned statement is the major source of deadlock
My question is ,should i look in-depth the above mentioned query
or i have to look the other statements in this trace file to resolve the deadlocks?
The same statement should be reason for deadlock.
This is a classical design issue.
You need to:
a) Look for another statement that comes in pair with this one and utilized by the application in mixed order (no certain order in executing two statements caused by multiple implementation of the same function).
b) If journal_id in the PK for journal_mt and there is a child table referencing it that does not have an index on its FK column.
Both are design problems ( a) is application design problem, b) is database design problem). b) can be a result of missing index as a result of incomplete database migration to a new server or simply deleted by accident.
In case of the second scenario you have another table locked... THE ENTIRE TABLE as a result of this "innocent" query.
Click Here to Expand Forum to Full Width