Can a Table Lock eliminate a deadlock or make it worse Oracle

deadlocklockingoracle

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.

Best Answer

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.

Related Question