Tuesday, July 01, 2025

Deadlock within DML statements

Oracle maintain a very detailed note Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.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-00000005

Set Processing SQL Demonstration

This is a simplified example of producing a deadlock using the same SQL statement, executing in different sessions with different indexes, and therefore updating the rows in a different order.

Setup

I have created a PL/SQL function that calls DBMS_SESSION.SLEEP to pause the session for a specified number of seconds before returning the current system timestamp.  The purpose of this is simply to slow down the update so that it is easier to demonstrate the deadlock without creating a much larger table.
CREATE OR REPLACE FUNCTION my_slow(p_seconds NUMBER) RETURN timestamp IS 
BEGIN
  dbms_session.sleep(p_seconds);
  RETURN systimestamp; 
END;
/
I create a small table with just 10 rows and three columns.  
  • 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

I will update the table T in each of the two sessions with an almost identical update statement.  The only difference between the statements is the column referenced in the where clause, and it is that which dictates the index used.
UPDATE t
SET c = my_slow(1)
WHERE a > 0
/
If I run each of these statements in isolation, they are successful.  I can obtain the execution plan and see when the rows were updated.  The statement updates at the rate of 1 row per second, because the MY_SLOW() function includes a 1-second pause.
In the first statement, the statement uses the primary key index on column A, and we can see that the rows are updated in the order of values in column A because Oracle has range-scanned the primary key index T_PK.
A full scan would have produced the same order in this test, but I want to emphasise that the problem occurs with a change of index.

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:13

Statement 2

In the second statement, the where clause has a condition on column B.
UPDATE t
SET c = my_slow(1)
WHERE b > 0
/
Now the update statement range scans index T_B on column B, and we can see that the rows were updated in the order of the values in column B.

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:24

Deadlock

If I run the two update statements simultaneously in different sessions, then one of them succeeds, and the other fails with a deadlock error.  
UPDATE t
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
After numerous tests, it appears to be arbitrary which statement succeeds and which fails.  It is just a matter of which statement gets slightly ahead and which detects the deadlock first.

QED

Thus, it is possible to produce a deadlock solely through SQL set-based processing, without any procedural code.  Two similar DML statements differing only in the index they use, and therefore the order in which they process rows, produced a deadlock.  
Like other deadlocks, it is the difference in the order of processing that is the root cause of all deadlocks.

Posted by at No comments :
Labels:
Subscribe to: Posts ( Atom )