Question:
I am getting this
ORA-00060: deadlock detected while waiting for resource
during a large update, against two separate tables. What I don't
understand is why a deadlock can occur when having an update on two
different tables, but I expect that the deadlock is ITL related ?
java.sql.SQLException: ORA-00060:
deadlock detected while waiting for resource
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE DBRT.DBRT_REALOP_DC_CYCLICS SET ... WHERE
DBRT_TROD_PK = :43
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
TX-0007000a-00019a9f
42 44 X 28 39 S
TX-001a0017-0000896d
28 39 X 42 44 S
session 44:
DID 0001-002A-00028438 session 39: DID 0001-001C-00004319
session 39: DID 0001-001C-00004319 session 44: DID
0001-002A-00028438
Rows waited
on:
Session 39: obj - rowid =
0003559F - AAA1WfAB3AAAAAAAAA
(dictionary objn - 218527, file - 119, block - 0, slot - 0)
Session 44: obj - rowid = 0003DFE5 - AAA9/lADDAAAAAAAAA
(dictionary objn - 253925, file - 195, block - 0, slot
- 0)
Answer: Whenever you have
competing DML
running against the same data, you run the risk of a deadlock. This deadlock
condition is an age-old issue known as the
perpetual embrace.
This infinite loop is caused by either an application or bad ad hoc SQL, but
Oracle is clever enough to recognize it and throw the ORA-00060 rather than
continuing the deadlock situation.
Using the above dump, you can see the objects that were locked, one in
file 119 (ROWID=0003559F) and file 195 (ROWID=0003DFE5). You can use
Oracle scripts to
resolve these file numbers and ROWIDs, and get the name of the table where
your deadlock occurred. To learn more, see my notes on Oracle
deadlocks.
For more informaiton on this error, see
MOSC Note: 62365.1. The ORA-00060
deadlock error details note:
ORA-00060: deadlock detected while
waiting for resource
Cause: Transactions deadlocked
one another while waiting for resources.
Action:
Look at the trace file to see the transactions and resources involved. Retry
if necessary.
There are several causes for this error:
- Too high activity - Re-running the job during a
less busy period can fix this ORA-00060 deadlock error.
- Poor application design - As noted in the trace
file: "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."
- ASSM - This error can happen when the target
tablespace is using "segment space management auto". See these
notes on deadlocks with
ASSM. Beware, ASSM can cause huge
deadlocks, because ASSM does not perform well under heavy DML loads.
- RAM shortage - In rare cases this ORA-00060
deadlock error can happen with a RAM shortage at the Java layer or
Oracle layer.
As indicated by the oerr notes, the trace file can be used to find out exactly where the
deadlock happened.
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
