Oracle maintain a very detailed note Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1).
- This document includes an example that generates a deadlock. It uses procedural code involving data modification language (DML) statements that each only update a single row.
- There is also a link to another document that explains how you can produce a deadlock in an autonomous transaction. ORA-00060 Single-Resource Deadlock Occurs on Autonomous Transaction (Doc ID 1511700.1).
This blog demonstrates that it is also possible to produce a deadlock with a single DML statement that updates multiple rows in a different order than another, potentially identical, statement.
What is a Deadlock?
Oracle’s note explains that “A deadlock occurs when a session (A) wants a resource held by another session (B), but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved, but the idea is the same.”
The key point is that two or more sessions demand the same resources in a different order.
It is not a Database Error
“NOTE: Deadlock errors are usually not the underlying root cause of a problem, rather they are more likely to be an indicator of another issue in the application or elsewhere. Once the resultant trace file has been examined … to determine the objects involved, it is then worth thinking about what could be causing such a deadlock - for example a batch program being run more than once by mistake or in the wrong order, or by not following programming standards in an application.
Identification and Resolution of the underlying issue then makes the error redundant.”
Diagnostic information produced by a Deadlock
"ORA-00060 error normally writes the error message in the alert.log, together with the name of the trace file created. The exact format of this varies between Oracle releases. The trace file will be written to the directory indicated by the USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the type of process that creates the trace file.
The trace file will contain a deadlock graph and additional information similar to that shown below. This is the trace output from the above example, which signalled an ORA-00060..."
The trace file always contains this reminder:
“DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock]
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"
This is followed by a deadlock graph that shows the sessions and database locks involved, and hence the object being locked. The SQL statements involved are also in the trace.
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00050018-000004fa 22 132 X 19 191 X
TX-00070008-00000461 19 191 X 22 132 X
session 132: DID 0001-0016-00000005 session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C session 132: DID 0001-0016-00000005Set Processing SQL Demonstration
Setup
CREATE OR REPLACE FUNCTION my_slow(p_seconds NUMBER) RETURN timestamp IS
BEGIN
dbms_session.sleep(p_seconds);
RETURN systimestamp;
END;
/- Column A has a sequence of integer values, 1 to 10. This column is the primary key.
- Column B has random numerical values in the range 1 to 100, but when the rows are sorted by this column, they will come out in a different order. This column is the subject of another index.
- Column C is a timestamp that will be updated with the system timestamp during the test so that we can see what order the rows are updated in.
CREATE TABLE t
(a number, b number, c timestamp
,CONSTRAINT t_pk PRIMARY KEY (a))
/
CREATE INDEX t_b ON t (b)
/
INSERT INTO t (a, b)
SELECT level, dbms_random.value(1,100) FROM DUAL
CONNECT BY level <= 10
/
COMMIT;
EXEC dbms_stats.gather_table_stats(user,'T');Statement 1
UPDATE t
SET c = my_slow(1)
WHERE a > 0
/Plan hash value: 2174628095
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10 | 40 | 2 (0)| 00:00:01 |
| 1 | UPDATE | T | | | | |
|* 2 | INDEX RANGE SCAN| T_PK | 10 | 40 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT * FROM t ORDER BY c;
A B C
---------- ---------- -------------------
1 43.6037759 25/06/2025 17:08:03
2 75.8443964 25/06/2025 17:08:04
3 32.7061872 25/06/2025 17:08:05
4 92.3717375 25/06/2025 17:08:07
5 99.2611075 25/06/2025 17:08:08
6 18.9198972 25/06/2025 17:08:09
7 21.8558534 25/06/2025 17:08:10
8 15.9224485 25/06/2025 17:08:11
9 94.3695186 25/06/2025 17:08:12
10 38.7300478 25/06/2025 17:08:13Statement 2
UPDATE t
SET c = my_slow(1)
WHERE b > 0
/Plan hash value: 2569189006
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 10 | 230 | 2 (0)| 00:00:01 |
| 1 | UPDATE | T | | | | |
|* 2 | INDEX RANGE SCAN| T_B | 10 | 230 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
SELECT * FROM t ORDER BY c; A B C
---------- ---------- -------------------
8 15.9224485 25/06/2025 17:08:15
6 18.9198972 25/06/2025 17:08:16
7 21.8558534 25/06/2025 17:08:17
3 32.7061872 25/06/2025 17:08:18
10 38.7300478 25/06/2025 17:08:19
1 43.6037759 25/06/2025 17:08:20
2 75.8443964 25/06/2025 17:08:21
4 92.3717375 25/06/2025 17:08:22
9 94.3695186 25/06/2025 17:08:23
5 99.2611075 25/06/2025 17:08:24Deadlock
UPDATE t
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
@go-faster.co.uk on BlueSky
Posts
