3

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

UPDATE:

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.

e.g.

Transaction 1

  1. Locks table X at 10:00:00 (all times are just examples)

  2. 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

Transaction 2

  1. Locks table Y at 10:00:00

  2. 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.
  • Might adding explicit table locks mitigate a deadlock problem? Possible, I guess. Might it make the problem worse? Sure. Might it do nothing? Absolutely. Rather than speculating, though, was does the deadlock trace file tell you is the actual source of the deadlock? What is the issue that causes your application to generate that deadlock? – Justin Cave Aug 24 '15 at 0:07
  • I updated my question with a Transaction scenario from the developer on this critical project. Hopefully it is not too long of a novel to read Justin. I was look for more specific on the deadlock trace and the specific causes. (I know that so many developers changed sprocs over the last 3 years that some felt like this is a nightmare to figure out. Most of us come from sql server backgrounds which doesn't directly help with the Oracle – Brian Thornton Aug 24 '15 at 0:13
  • What does "locks table X" mean here? Forcing explicit table locking with the lock table command is very, very uncommon. I'm hard-pressed to imagine many situations where that would be reasonable in an OLTP application. Perhaps you mean that the code is locking a row in table X and a row in table Y and deadlocking on that? – Justin Cave Aug 24 '15 at 0:18
  • That was from an email from the main developer on this specific application so I am trying to make sense of that as well. I think that you are correct. – Brian Thornton Aug 24 '15 at 0:54
1

In addition to someone doing full table locks, there are two other things that I can think of. If you have a table where there are several columns that have a parent child relationship to the same column in a child table and if the child column is not indexed, then indexing it could help. I had a case with an online content management system that had unindexed foreign keys that caused cascading locks. I was able to resolve that by using the Tom Kyte query to find the missing indexes and add them in.

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

Another possibility is that you have cases where two triggers are firing on different tables and they are each trying to update the other table. You can tell this by looking at the trace log that gets produced from a dead lock and figuring out what objects and rows are locked, then check your triggers to see if any of your triggers are running that SQL.

It would be helpful to know how long this has been happening and what changed around the time that this became an issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.