What is an Oracle deadlock?
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"! The doc
note that a retry may work:
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.
Deadlocks in Oracle result in this error:
ORA-00060: deadlock detected while waiting
for resource
If your
ORA-00060 is caused by competing resources,
the perpetual embrace happens when the
aborted task (Task B) attempts to lock a row
which is being held by another task (Task
A), which, in-turn, is waiting for task B to
release a lock. To prevent a perpetual
wait, Oracle aborts the transaction that
caused the deadlock.
See my
notes here on
resolving the deadlock detected error.
Resolving
Oracle deadlocks
There are several remedies for resolving
aborted tasks from deadlocks:
-
Tune the application -
Single-threading related updates and
other application changes can often
remove deadlocks. Re-scheduling
batch update jobs to low-update times an
also help.
-
Add INITRANS - In certain
conditions, increasing INITRANS for the
target tables and indexes(adding slots
to the ITL) can relieve deadlocks.
-
Use smaller blocks with less data
- Since the deadlock contention is at
the block-level, consider moving these
tables and indexes to a super-small
blocksize (create a db2k_cache_size),
and using a high PCTFREE to space-out
the data over MORE blocks.
Inside Oracle deadlock
machinations
The LMD process also handles deadlock
detection Global Enqueue Service (GES)
requests. Remote resource requests are
requests originating from another instance.
Deadlock
Detection
Transaction deadlocks occur when two or more
transactions are attempting to access an
object with incompatible lock modes. The
following script can be used to identify
deadlocks in the database. The query depends
upon objects that are created by the script
$ORACLE_HOME/rdbms/admin/dbmslock.sql. Log
on as SYS or with SYSDBA authority and run
this script in all databases. You may have
to run the deadlock monitoring script below
numerous times before you identify the
transaction that is causing the problem.
The Enqueue Deadlock Per Sec Oracle metric
is the number of times per second that a
process detected a potential deadlock when
exchanging two buffers and raised an
internal, restartable error.
Avoiding Deadlock Conditions
A deadlock can occur whenever multiple users are
in a waiting pattern for data locked by each other. Deadlocks
prevent some transactions from continuing to work. In the event of
deadlock, Oracle writes the message and error in the form of an
ORA-60 error to the Oracle alert.log
file. The following diagram illustrates the perfect storm condition
that causes a deadlock or deadly embrace to occur within
Oracle .
Deadlock problems have a similar root cause as that found with basic
locking issues with Oracle which is the result of poor database
application design. To resolve deadlock conditions with Oracle, the
DBA needs to work together with the developer and software
engineering team to modify or rewrite the database application code
so that such deadlocks do not reoccur.
Lock Contention Issues and Solutions
After the database administrator has exhausted
possibilities to visit the design of the database application with
the development team, the next step is to perform further analysis
to solve lock contention issues.
Oftentimes, the lock issue is the result of a
zombie batch process or hung database session which has placed an
exclusive lock on a specific row or table, thereby blocking access
to the data from other users.
The simple solution to this type of
problem is to identify the particular user and session causing the
blocking condition and then to contact the user so that the session
can be killed using the alter system kill session 'sid,serial#'
immediate command from within SQL*Plus. In the previous section,
numerous locking scenarios and potential solutions were covered.
See my related notes on deadlocks here:
|