Sunday, June 21, 2026

Blog List

Posted by at

Oracle single session "library cache lock" self deadlock: ORA-04020

Following previous Blog Oracle 12c single session "library cache lock (cycle)" deadlock, we will look one more case:
     ORA-04020: deadlock inside one single session.

Note: Tested in Oracle 19c


1. Test Setup 


drop table test_tab;

drop table test_tab_v1;

drop table test_tab_v2;

create table test_tab as select 'ABC123' name, level c0 from dual connect by level <= 10; 

create table test_tab_v1 as select level c0 from dual connect by level <= 5; 

create table test_tab_v2 as select level c0 from dual connect by level <= 10;

create or replace function test_fun (p_x number) return number is
  l_x number;
begin
  select c0 into l_x from test_tab where name = 'ABC123' and  c0 = p_x;
  return l_x;
end;
/

create or replace package test_pkg is
  procedure p1 (p_x number);
end;
/

create or replace package body test_pkg is
  procedure p1 (p_x number) is
    l_x number;
  begin
    l_x := test_fun(p_x);
  end;
end;
/

create or replace force view test_view as
  with sq1 as (select distinct c0 vc0, test_fun(c0) vc1 from test_tab_v1)
  select * from test_tab_v2 v2, sq1 v1 where v2.c0 = v1.vc0;

create or replace procedure test_ddl_table(p_cnt number) as
begin
  for i in 1..p_cnt loop
    execute immediate 'alter table test_tab add(ks_c'||i||' number)';
    dbms_session.sleep(0.1);
  end loop;
end;
/  

create or replace procedure test_ddl_view(p_cnt number) as
begin
  for i in 1..p_cnt loop
    execute immediate 'alter view test_view compile';
  end loop;
end;
/   

create or replace procedure test_exec_call(p_cnt number) as
begin
  for i in 1..p_cnt loop
    test_pkg.p1(mod(i, 10) +1);
    dbms_session.sleep(0.1);
  end loop;
end;
/


2. Test Run


Open two Sqlplus sessions: SESS-1 and SESS-2.


2.1 In SESS-1, run: 


exec test_ddl_table(300);


2.2 In SESS-2, run: 


exec test_ddl_view(10000);
After a few seconds, SESS-2 throws error: 

ERROR:
ORA-24344: success with compilation error
ORA-06512: at "K.TEST_DDL_VIEW", line 4
ORA-06512: at line 1
One can check object status by: 

select object_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status from dba_objects 
 where object_name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL') 
 order by status, object_name;

OBJECT_NAME     OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE  CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS
-------------- ---------- -------------- ------------ -------------------- -------------------- ------------------- ------------
TEST_FUN          6236412                FUNCTION     2026-JUN-18 11:13:56 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_PKG          6236411                PACKAGE BODY 2026-JUN-18 11:11:44 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_VIEW         6236423                VIEW         2026-JUN-18 11:28:39 2026-JUN-21 08:38:29 2026-06-21:08:38:29 INVALID
TEST_DDL_TABLE    6236774                PROCEDURE    2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_DDL_VIEW     6236775                PROCEDURE    2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_PKG          6236410                PACKAGE      2026-JUN-18 11:09:46 2026-JUN-20 10:12:43 2026-06-18:11:20:14 VALID
TEST_TAB          6236945        6236945 TABLE        2026-JUN-21 08:37:45 2026-JUN-21 08:38:39 2026-06-21:08:38:39 VALID
TEST_TAB_V1       6236946        6236946 TABLE        2026-JUN-21 08:37:45 2026-JUN-21 08:37:45 2026-06-21:08:37:45 VALID
TEST_TAB_V2       6236947        6236947 TABLE        2026-JUN-21 08:37:46 2026-JUN-21 08:37:46 2026-06-21:08:37:46 VALID


select name, type, '0X'||trim('0' from addr) addr, to_number(addr, 'XXXXXXXXXXXXXXXXXX') addr_num, hash_value, type 
  from v$db_object_cache v 
 where name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL')
 order by v.name;

NAME           TYPE         ADDR           ADDR_NUM HASH_VALUE TYPE
-------------- ------------ ------------ ---------- ---------- ------------
TEST_DDL_TABLE PROCEDURE    0X96A00E48   2527071816 2793044217 PROCEDURE
TEST_DDL_VIEW  PROCEDURE    0X6B5A398    1801075072 2396118748 PROCEDURE
TEST_FUN       FUNCTION     0X80CDF188   2160980360 2391147328 FUNCTION
TEST_PKG       PACKAGE      0X88C948C8   2294892744 3673762546 PACKAGE
TEST_PKG       PACKAGE BODY 0X737B9C48   1937480776 2937683311 PACKAGE BODY
TEST_TAB       TABLE        0X70F1F1B    1894904240 1905210899 TABLE
TEST_TAB_V1    TABLE        0X71FAA968   1912252776 2228492516 TABLE
TEST_TAB_V2    TABLE        0X97BBD6A8   2545669800  580752620 TABLE
TEST_VIEW      VIEW         0X70BF69B8   1891592632 1492527927 VIEW
To repeat the test, re-execute Test Setup at first, then make the test.


3. trc file


Following trc file shows: 

   ORA-04020: deadlock detected while trying to lock object K.TEST_FUN
with object handle: 0x80cdf188, which is TEST_FUN (ADDR = 0X80CDF188 in above v$db_object_cache output).

It is a self session deadlock because waiting session and blocking session are the same (0xb89008b8 17).

Blog Blog Oracle 12c single session "library cache lock (cycle)" deadlock contains more detail analysis on this behaviour,
for example, 

	waiting session (0xb89008b8   17) = blocking session (0xb89008b8   17)
	waiting mode (X)                 != blocking mode (s)
	waiting lock (0x62d127d0)        != blocking lock (0x83c3f830)
	waiting Flags=[0000]             != blocking Flags=CNB/PNC/[0003]
	waiting SavepointNum=d56d7       > blocking SavepointNum=d56ab 
    waiting/blocking HandleIvd=6236945=TEST_TAB


Trace file /orabin/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_ora_323537.trc
Version 19.30.0.0.0
Oracle process number: 48
Unix process pid: 323537, image: oracle@testdb

*** SESSION ID:(17.58059) 2026-06-21T08:38:29.493117+02:00
 
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object K.TEST_FUN
Short stack dump of current process:
----- Abridged Call Stack Trace -----
ksedsts()+426<-kgllkde()+1182<-kglLockWait()+2503<-kgllkal()+2030<-kglLock()+1426<-kglget()+297<-kqlvld()+851<-kglgob()+3504<-kgldpo0()+726<-qcdlgpo()+772<-qcsRslvPLSQLInvoc1()+979<-qcsRslvPLSQLInvoc()+96<-qcsRslvName()+676<-qcsridn()+115<-qcsraic()+431<-qcspqbDescendents()+546
<-qcspqb()+283<-qcspqbDescendents()+4286<-qcspqb()+283<-kkmdrv()+180<-opiSem()+2167<-opiprs()+528<-kksParseChildCursor()+524<-rpiswu2()+2004<-kksLoadChild()+5339<-kxsGetRuntimeLock()+2335<-kksfbc()+19863<-opiexe()+2982<-opiosq0()+4482<-opipls()+21173<-opiodr()+1264
<-rpidrus()+198<-skgmstack()+65<-rpidru()+151<-rpiswu2()+543<-rpidrv()+1281<-psddr0()+467<-psdnal()+624<-pevm_EXIM()+282<-pfrinstr_EXIM()+43<-pfrrun_no_tool()+60<-pfrrun()+904<-plsql_run()+752<-peicnt()+279<-kkxexe()+720<-opiexe()+31613<-kpoal8()+2301<-opiodr()+1264
<-ttcpip()+1232<-opitsk()+1908<-opiino()+935<-opiodr()+1264<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46 
----- End of Abridged Call Stack Trace -----
Partial short call stack signature: 0xce99135a9a693f97
--------------------------------------------------------
 object handle   waiting session   sid  waiting lock    mode blocking session  sid  blocking lock   mode
---------------- ---------------- ----- --------------- ---- ---------------- ----- --------------- ----
 0x80cdf188      0xb89008b8        17   0x62d127d0      X    0xb89008b8        17   0x83c3f830      S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x66147068, type: LIBRARY OBJECT LOCK (118), map: 0x62d127d0
    state: LIVE (0x4532), flags: 0x1
    owner: 0xaefc0968, proc: 0xb8eebc90
    link: 0x66147088[0xa5f638e0, 0xaefc09d8]
    child list count: 0, link: 0x661470d8[0x661470d8, 0x661470d8]
    pg: 0
SOC: 0x62d127d0, type: LIBRARY OBJECT LOCK (118), map: 0x66147068
     state: LIVE (0x99fc), flags: INIT (0x1)

LibraryObjectLock:  Address=0x62d127d0 Handle=0x80cdf188 RequestMode=X 
  CanBeBrokenCount=708 Incarnation=708 ExecutionCount=0 
  
  User=0xb89008b8 Session=0xb89008b8 ReferenceCount=0 
  Flags=[0000] SavepointNum=d56d7 
LibraryHandle:  Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL 
  ObjectName:  Name=K.TEST_FUN   
    FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49 
  Statistics:  InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874 
  Counters:  BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0 
  Concurrency:  DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0) 
  Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945 
  WaitersLists:  
    Lock=0x80cdf218[0x62d12800,0x62d12800] 
    Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8] 
    LoadLock=0x80cdf270[0x80cdf270,0x80cdf270] 
  Timestamp:  Current=06-21-2026 08:37:46 
  HandleReference:  Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200] 
  LibraryObject:  Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001] 
    DataBlocks:  
      Block:  #='0' name=KGLH0^8e860340 pins=0 Change=NONE   
        Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20 
      Block:  #='2' name=PLDIA^8e860340 pins=1 Change=NONE   
        Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20 
      Block:  #='4' name=PLMCD^8e860340 pins=1 Change=NONE   
        Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20 
------------- WAITING SESSION -------------
  sid: 17 ser: 58059 audsid: 102774880 user: 49/K
    flags: (0x8000041) USR/- flags2: (0x48009) -/DDLT2/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 48 O/S info: user: oracle, term: UNKNOWN, ospid: 323537
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 6132:51788
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  alter view test_view compile
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x87edd848, type: LIBRARY OBJECT LOCK (118), map: 0x83c3f830
    state: LIVE (0x4532), flags: 0x1
    owner: 0xa5f51dd0, proc: 0xb8eebc90
    link: 0x87edd868[0xb8f018c0, 0x87e13a98]
    child list count: 0, link: 0x87edd8b8[0x87edd8b8, 0x87edd8b8]
    pg: 0
SOC: 0x83c3f830, type: LIBRARY OBJECT LOCK (118), map: 0x87edd848
     state: LIVE (0x99fc), flags: INIT (0x1)

LibraryObjectLock:  Address=0x83c3f830 Handle=0x80cdf188 Mode=S 
  CallPin=0x90ab9830 CanBeBrokenCount=708 Incarnation=707 ExecutionCount=0 
  
  User=0xb89008b8 Session=0xb89008b8 ReferenceCount=3 
  Flags=CNB/PNC/[0003] SavepointNum=d56ab 
LibraryHandle:  Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL 
  ObjectName:  Name=K.TEST_FUN   
    FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49 
  Statistics:  InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874 
  Counters:  BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0 
  Concurrency:  DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0) 
  Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945 
  WaitersLists:  
    Lock=0x80cdf218[0x62d12800,0x62d12800] 
    Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8] 
    LoadLock=0x80cdf270[0x80cdf270,0x80cdf270] 
  Timestamp:  Current=06-21-2026 08:37:46 
  HandleReference:  Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200] 
  LibraryObject:  Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001] 
    DataBlocks:  
      Block:  #='0' name=KGLH0^8e860340 pins=0 Change=NONE   
        Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20 
      Block:  #='2' name=PLDIA^8e860340 pins=1 Change=NONE   
        Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20 
      Block:  #='4' name=PLMCD^8e860340 pins=1 Change=NONE   
        Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20 
--------------------------------------------------------
This lock request was aborted.
Posted by at

Tuesday, June 9, 2026

One More Case of DBMS_ALERT deadlock

Following the previous Blog One case of dbms_alert.signal deadlock
< in this Blog, we will demonstrate one more case of DBMS_ALERT deadlock.

Note: Tested in Oracle 19c

Open two Sqplus sessions: SESS-1 and SESS-2.


1. At T1, login to SESS-1, register two alert interests: alert_1 and alert_2, then exit the session: 


exec dbms_alert.register('alert_1');

exec dbms_alert.register('alert_2');

exit;


2. At T2, re-login to SESS-1 (sid: 552), check two registered names: 



-- watch two registered names, whose session is not alive.

select * from sys.dbms_alert_info where name like 'ALERT%';

NAME            SID             CHANGED    MESSAGE
--------------- --------------- ---------- ---------------
ALERT_1         0228CD530001    N
ALERT_2         0228CD530001    N

-- determines if the specified session is active.
declare
  l_unique_sid varchar2(30) := '0228CD530001';
begin
  if not dbms_session.is_session_alive(l_unique_sid) then
    dbms_output.put_line('Not Alive: (sid: ' ||to_number(substr(l_unique_sid, 1, 4), 'XXXX')||
                                ', serial#: '||to_number(substr(l_unique_sid, 5, 4), 'XXXX')||
                                   ', inst: '||to_number(substr(l_unique_sid, 9, 4), 'XXXX')||')');
  end if;
end;
/

Not Alive: (sid: 552, serial#: 52563, inst: 1)

3. At T3, continue in SESS-1, send signal: alert_2 


SQL (sid:552) > exec dbms_alert.signal('alert_2', 'alert_msg_2');

4. At T4, login to SESS-2 (sid: 16), register one alert interests: alert_3 


SQL (sid:16) > exec dbms_alert.register('alert_3');

--Note that this call is with default cleanup = true to perform cleanup of any extant orphaned pipes.

5. At T5, in SESS-1, check TX lock, SESS-2 (sid:16) is blocked by SESS-1 (sid: 552) 


SQL (sid:552) > select * from  v$lock where type = 'TX';

ADDR             KADDR             SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00000000B4310970 00000000B43109A0   16 TX    5505024     782554          0          6        132          0          0
00000000ADB30E70 00000000ADB30EA8  552 TX    5505024     782554          6          0        169          1          0
00000000ADBCFAB8 00000000ADBCFAF0   16 TX    6291482     236621          6          0        132          0          0

3 rows selected.

6. At T6, in SESS-1, send signal: alert_1 


SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');

7. Both sessions throw ORA-00060 


SQL (sid:16) > exec dbms_alert.register('alert_3');
BEGIN dbms_alert.register('alert_3'); END;

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


SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');
BEGIN dbms_alert.signal('alert_1', 'alert_msg_1'); END;

*
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

8. Both Deadlock trc files: 


=================== testdb_ora_1851008.trc ===================

Unix process pid: 1851008, image: oracle@testdb

*** SESSION ID:(16.33150) 2026-06-09T15:35:47.444358+02:00
 
Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-0060001A-00039C4D-00000000-00000000         66      16     X        33150      63     552           X  57779
TX-00540000-000BF0DA-00000000-00000000         63     552     X        57779      66      16           X  33150
 
----- Information for waiting sessions -----
Session 16:
Holds resource TX-0060001A-00039C4D-00000000-00000000 acquired 204 seconds ago.
  sid: 16 ser: 33150 audsid: 131915016 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 66 O/S info: user: oracle, term: UNKNOWN, ospid: 1851008
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 42636:42136
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  DELETE DBMS_ALERT_INFO WHERE SID = :B1 
 
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 241 seconds ago.
  sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
    machine: SYS\WCLINBDOXKKDUGS 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 16: DID 0001-0042-0000011E	session 552: DID 0001-003F-000005CF 
session 552: DID 0001-003F-000005CF	session 16: DID 0001-0042-0000011E 
 
Rows waited on:
  Session 16: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 2)
  Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 1)


=================== testdb_ora_1851016.trc ===================

Unix process pid: 1851016, image: oracle@testdb

*** SESSION ID:(552.57779) 2026-06-09T15:35:50.836313+02:00

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00540000-000BF0DA-00000000-00000000         63     552     X        57779      61     196           X   9237
TX-005B0001-000BE24C-00000000-00000000         61     196     X         9237      63     552           X  57779
 
----- Information for waiting sessions -----
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 245 seconds ago.
  sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
    machine: SYS\WCLINBDOXKKDUGS 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 196:
Holds resource TX-005B0001-000BE24C-00000000-00000000 acquired 3 seconds ago.
  sid: 196 ser: 9237 audsid: 131915018 user: 0/SYS
    flags: (0x10041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 61 O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
    image: oracle@testdb (J006)
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
    machine: testdb program: oracle@testdb (J006)
    application name: task-911-2 hash value=1117638710
    action name: bgp: loop: init, hash value=2828460256
  current SQL:
  DELETE DBMS_ALERT_INFO WHERE SID = :B1 
 

session 552: DID 0001-003F-000005CF	session 196: DID 0001-003D-000004C1 
session 196: DID 0001-003D-000004C1	session 552: DID 0001-003F-000005CF 
 
Rows waited on:
  Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 1)
  Session 196: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 2)

9. Repeat Test


To repeat test, exit both SESS-1 and SESS-2, open a third SESS-3, and register a new alert to cleanup alert_1, alert_2, alert_3. Then repeat above test steps. 

exec dbms_alert.register('alert_other');

10. Reasoning


Two alert interests are inserted (registered) into sys.dbms_alert_info in the order of alert_1 and alert_2, both with the same unique_session_id: 0228CD530001.

When we register alert interest: alert_3, DBMS_ALERT first performs cleanup of not alive sessions by delete in default order: alert_1 and alert_2.

Whereas we frist send (update) signal: alert_2, then alert_1.

In this case, Updates vs Delete are interfered in crossing row order, hence Deadlock.
Posted by at
Subscribe to: Posts (Atom)