One case of dbms_alert.signal deadlock

In this Blog, we present one case of DBMS_ALERT_INFO update deadlock when two sessions calling dbms_alert.signal.

At first, setup test by:

begin
       dbms_alert.register('test_alert_1');
       dbms_alert.register('test_alert_2');
end;
/

Then open 2 Sqlplus Sessions: S1, and S2, run following steps one after another:

---======== S1@T1:  send 'test_alert_1' ========---
exec dbms_alert.signal('test_alert_1', 'alert_msg_1');

---======== S2@T2:  send 'test_alert_2' ========---
exec dbms_alert.signal('test_alert_2', 'alert_msg_2');

---======== S1@T3:  send 'test_alert_2' ========---
exec dbms_alert.signal('test_alert_2', 'alert_msg_2');

---======== S2@T4:  send 'test_alert_1' ========---
exec dbms_alert.signal('test_alert_1', 'alert_msg_1');

After about 3 seconds, S1 (first starting session) hit a deadlock.

  
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 431
ORA-06512: at line 1

The trace file contains the Deadlock graph and Error Stack. It looks like a conventional case of deadlock generated in application of dbms_alert.signal.

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-000F0014-00030A6B-00000000-00000000          8     374     X        16904      47     914           X  60788
TX-00020013-00043AF0-00000000-00000000         47     914     X        60788       8     374           X  16904
 
----- Information for waiting sessions -----
Session 374:
  sid: 374 ser: 16904 audsid: 50061287 user: 0/SYS
    flags: (0x8100041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 8 O/S info: user: oracle, term: UNKNOWN, ospid: 6207
    image: oracle@testdb
  client details:
    O/S info: user: ksun, term: TESTPC, ospid: 20744:22464
    machine: SYS\TESTPC program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
 
Session 914:
  sid: 914 ser: 60788 audsid: 50061288 user: 0/SYS
    flags: (0x8100041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 47 O/S info: user: oracle, term: UNKNOWN, ospid: 6325
    image: oracle@testdb
  client details:
    O/S info: user: ksun, term: TESTPC, ospid: 21236:22040
    machine: SYS\TESTPC program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
 
----- End of information for waiting sessions -----
 
*** 2020-11-08T14:53:34.649821+01:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
 at 0x7ffef6442228 placed updexe.c@2062
 at 0x7ffef6443050 placed updexe.c@4637
----- Current SQL Statement for this session (sql_id=6tmkh8j0d3w0p) -----
UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x9ce97888       431  package body SYS.DBMS_ALERT.SIGNAL
0x7294ec28         1  anonymous block

In AskTOM: dbms_alert_info - Ask TOM - Oracle, there is also one case of dbms_alert deadlock.