remidian.com

Code below will save the information of the ‘blocking session’ in an ORA-00060 situation.

Since both sessions are equally guilty of the deadlock Oracle picks one session at random to get the ORA-00060, the update will fail for this session and a trace file is written to the user_dump_dest. In this trace file you can find the current SQL statement of the session that got the ORA-00060 and the os pids of the sessions on the server, however there is not as much information as to the session that ’caused the deadlock’ as you might want to see.

The ’servererror’ triggers (around since 8i), enable you to write a trigger and storing some usefull information for later analysis.

Please note the triggers firing on the ’servererror’ start a separate transaction and commits it after firing the trigger. No need to start an autonomous transaction, since it already is so to say, see Database Application Developer’s Guide – Fundamentals – 9 Coding Triggers – Table 9-3 System Manager Events.

tables

Create some tables to hold the information I’m interested in.

[REMI@DB01.REMIDIAN.COM]
SQL> create table log$event
  2     ( event#     number         constraint pk_log$event primary key
  3     , event      varchar2(512)
  4     , eventtime  date
  5     )
  6  /
 
Table created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create table log$session
  2  as
  3  select 1 event#
  4  ,      s.*
  5  from   v$session    s
  6  where  1 = 2
  7  /
 
Table created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create table log$session_longops
  2  as
  3  select 1 event#
  4  ,      s.*
  5  from   v$session_longops s
  6  where  1 = 2
  7  /
 
Table created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create table log$sqltext_with_newlines
  2  as
  3  select 1 event#
  4  ,      s.*
  5  from   v$sqltext_with_newlines    s
  6  where  1 = 2
  7  /
 
Table created.
 

Some constraints;

[REMI@DB01.REMIDIAN.COM]
SQL> alter table log$session 
  2     add constraint fk_log$session foreign key (event#) references log$event;
 
Table altered.
 
[REMI@DB01.REMIDIAN.COM]
SQL> alter table log$session_longops
  2     add constraint fk_log$session_longops foreign key (event#) references log$event;
 
Table altered.
 
[REMI@DB01.REMIDIAN.COM]
SQL> alter table log$sqltext_with_newlines
  2     add constraint fk_log$sqltext_with_newlines foreign key (event#) references log$event;
 
Table altered.
 

servererror trigger

The servererror trigger, firing after every servererror;

[REMI@DB01.REMIDIAN.COM]
SQL> grant administer database trigger to remi
  2  /
 
Grant succeeded.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create or replace trigger log$ora00060
  2     after servererror on database
  3     
  4  declare
  5  
  6     n_event#  pls_integer;
  7     d_date    date;
  8        
  9     n_blocked pls_integer;
 10     n_blocker pls_integer;
 11     
 12  begin
 13     
 14     n_event# := sys.dbms_utility.get_time;
 15     d_date   := sysdate;
 16     
 17     if is_servererror(60) then
 18        
 19        insert into log$event 
 20           ( event#
 21           , event
 22           , eventtime 
 23           )
 24           values
 25           ( n_event#
 26           , 'ORA-00060: deadlock detected while waiting for resource'
 27           , d_date
 28           );
 29           
 30        
 31        /*
 32        * retieve sid of blocked/blocking session
 33        */
 34        select sid
 35        into   n_blocked
 36        from   v$mystat
 37        where  rownum = 1;
 38        
 39        select sid
 40        into   n_blocker
 41        from   v$lock
 42        where  request > 0
 43        and    (id1, id2) in (
 44                             select id1
 45                             ,      id2
 46                             from   v$lock
 47                             where  block = 1
 48                             and    sid = n_blocked
 49                             );
 50        
 51        /*
 52        * store information
 53        */      
 54        insert into log$session
 55        select n_event#
 56        ,      s.*
 57        from   v$session s
 58        where  sid in ( n_blocked, n_blocker);
 59        
 60        insert into log$sqltext_with_newlines
 61        select n_event#
 62        ,      v.*
 63        from   v$sqltext_with_newlines v
 64        where  address in (
 65                          select sql_address
 66                          from   v$session
 67                          where  sid in ( n_blocker)
 68                          union
 69                          select prev_sql_addr
 70                          from   v$session
 71                          where  sid in ( n_blocker)
 72        );
 73        
 74        insert into log$session_longops
 75        select n_event#
 76        ,      v.*
 77        from   v$session_longops v
 78        where  sid = n_blocker;
 79        
 80     end if; -- if is_servererror(60) then
 81  
 82  exception 
 83        when others then
 84           null;
 85  end;
 86  /
 
Trigger created.
 

Live example

The scenario below demostrates the behaviour the trigger above.

Session one

[REMI@DB01.REMIDIAN.COM]
SQL> select * from demo;
 
         K
----------
         1
         2
         
[REMI@DB01.REMIDIAN.COM]
SQL> update demo set k = 1 where k = 1;
 
1 row updated.

Session two

[REMI@DB01.REMIDIAN.COM]
SQL> update demo set k = 2 where k = 2;
 
1 row updated.

Back to session one

[REMI@DB01.REMIDIAN.COM]
SQL> update demo set k = 2 where k = 2;
 
1 row updated.
 

And finally in session two

[REMI@DB01.REMIDIAN.COM]
SQL>  update demo set k = 1 where k = 1; 
 update demo set k = 1 where k = 1
        *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
 

Retrieve the logged results containing information about the deadlock event and the involved sessions.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from log$event;
 
    EVENT# EVENT                                                        EVENTTIME
---------- ------------------------------------------------------------ ---------
-821696669 ORA-00060: deadlock detected while waiting for resource      27-JUN-06
 
[REMI@DB01.REMIDIAN.COM]
SQL> select event#, sid, osuser, lockwait, last_call_et, status from log$session;
 
    EVENT#     SID OSUSER               LOCKWAIT LAST_CALL_ET STATUS
---------- ------- -------------------- -------- ------------ --------
-821696669      15 REMIVISSER           801E9EFC            0 ACTIVE
-821696669      86 REMIVISSER                               4 ACTIVE
 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from log$sqltext_with_newlines;
 
    EVENT# ADDRESS  HASH_VALUE COMMAND_TYPE      PIECE SQL_TEXT
---------- -------- ---------- ------------ ---------- --------------------------------------------
-821696669 80B70B44 3948528108            6          0  update demo set k = 1 where k = 1
 

10g NOTE

Code will NOT work in Oracle 10G (creation of log$session table as select * from v$session fails because in 10G the v$session view also has a column event#). But in 10G the tracefile generated upon ORA-00060 errors has a lot more informatin about the ‘blocking’ session in an ORA-00060 situation. If you still want this to work in 10G simply change the ‘event#’ column to have another name.