Database Reference
In-Depth Information
UPDATE hr.employees
SET phone_number='650.507.2876'
WHERE employee_id=193;
UPDATE hr.employees
SET phone_number='650.507.9878'
WHERE employee_id=182;
2
UPDATE hr.employees
SET salary=5000
WHERE employee_id=182;
UPDATE hr.employees
SET salary=5500
WHERE employee_id=193;
ORA-00060: deadlock
detected while waiting for
resource
1
t
1
t
2
t
3
t
4
t
5
t
Figure 5-1.
Deadlock detection
To avoid deadlocks, rows need to be locked in the same order by all database sessions.
If this is not feasible, deadlocks and the overhead associated with writing trace files may be
avoided by executing
SELECT
FOR
UPDATE
NOWAIT
prior to an
UPDATE
. If this returns ORA-00054,
then the session needs to roll back and reattempt the entire transaction. The
ROLLBACK
will
allow other transactions to complete. The downside of this approach is the additional processing.
Following is an example that is tailored to the previous scenario:
SQL> SELECT rowid FROM hr.employees WHERE employee_id=182 FOR UPDATE NOWAIT;
SELECT rowid FROM hr.employees WHERE employee_id=182 FOR UPDATE NOWAIT
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT rowid FROM hr.employees WHERE employee_id=182 FOR UPDATE NOWAIT;
ROWID
------------------
AAADNLAAEAAAEi1ABb
SQL> UPDATE hr.employees SET phone_number='650.507.9878'
WHERE rowid='AAADNLAAEAAAEi1ABb';
1 row updated.
Event 10027
Event 10027 gives the DBA control over the amount and type of diagnostic information generated
in response to ORA-00060. At default settings, an ORA-00060 trace file contains cached cursors,
a deadlock graph, process state, current SQL statements of the sessions involved, and session
wait history (in Oracle10
g
and subsequent releases). Except for the current SQL statements and
the deadlock graph, all the information pertains merely to the session that received ORA-
00060. Event 10027 may be used to achieve the following oppositional goals:
