We have a situation in which an Oracle Database is having deadlocks issues.
I said that perhaps we should lock into some table locks, and a guys response to me was
"Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it."
Is what he is saying true?
Error we get: "Deadlock detected while waiting for resource Stack Trace"
Here is most of email on the error that we get:
System Error: Error for Step Type:REMOVE PRED OPEN MAN User Error: ORA-00060: deadlock detected while waiting for resource Stack Trace: Timestamp 2015/08/21 14:02:39:130363000130363000 dbms_utility.format_error_backtrace: ORA-06512: at "OM.FOLDER_PKG", line 2661 ORA-06512: at "OM.DOC_LOG_STEP_PKG", line 2436 dbms_utility.format_error_stack: ORA-00060: deadlock detected while waiting for resource
This is PROBABLY useful, this is from a developer that is familiar with this happening every few months of which NO DBA at the company seems to be able to be able to figure it out.
This is what the main developer on this project said:
Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it.
Locks table X at 10:00:00 (all times are just examples)
Attempts to lock table Y at 10:00:02. DEADLOCK – transaction 2 is waiting on transaction 1 to unlock table X and transaction 1 is waiting on transaction 2 to unlock table Y
Locks table Y at 10:00:00
Attempts to lock table X at 10:00:01. Blocks waiting for transaction 1 to release the lock
Can replace the word ‘table’ with ‘record’. Problem remains the same. Think lower granularity of table instead of record could make the problem worse.
We can handle the issue by:
- Locking tables in a consistent order - this is difficult to do because we have many thousands of lines of code we’d need to make sure that we’d be calling things in the same order. It would also lead to uglier code where natural processing flow would have been changed simply to avoid deadlock.
- Locking all top level transactions on a single object at the very start (they can then lock sub-tables/ objects to their hearts content). The natural object on the folder side to do this one would be a WORK_FOLDER.
- Rolling back on deadlock and retrying – we have already implemented this but it only does one retry and sometimes Oracle is taking too long to detect that the deadlock occurs. And occasionally that one retry is not enough.