Using autonomous transactions for sleeping

There was a question in a recent Oracle-L thread about various uses of autonomous transactions.

Autonomous transactions can be very useful for a PL/SQL application logging, but sometimes they are also abused to cope with bad application design (like avoiding mutating table errors in triggers etc).

I’m not going to start on that topic here though, but instead presenting another case where autonomous transactions have helped me to work around a problem. It’s more a hack than a real solution though, but may be useful for someone else too.

My Snapper tool requires execute rights on DBMS_LOCK in order to sleep between snapshots. Sometimes when troubleshooting an urgent performance issue, I have had access only to some kind of application support account, without permissions to execute DBMS_LOCK.SLEEP. And sometimes it takes too long to get those rights granted by corporate DBAs.

So one workaround I’ve used so far is creating a fake DBMS_LOCK.SLEEP proc in the local support schema along with one dummy table and use a combination of SELECT FOR UPDATE and autonomous transactions to sleep for short time.

The code is very simple:

create table t(a) as select 1 from dual;

create or replace procedure sleep(seconds in number default 1) is

   resource_busy exception;
   deadlock exception;
   pragma exception_init (resource_busy, -30006);
   pragma exception_init (deadlock, -60);

   tmp number;

   procedure sleep_on_rowlock (p in number) is
           pragma autonomous_transaction;
           tmp number;
   begin
           execute immediate 'select a from t for update wait '||to_char(p) into tmp;
   end;

begin

   savepoint sleeper_savepoint;
   select a into tmp from t for update;
   sleep_on_rowlock(seconds);

   rollback to savepoint sleeper_savepoint;

exception
   when resource_busy then rollback to savepoint sleeper_savepoint;
--   when deadlock then rollback to savepoint sleeper_savepoint;
end;
/

All I do here is select one row FOR UPDATE, and then call a procedure with PRAGMA AUTONOMOUS_TRANSACTION which in turn uses “SELECT FOR UPDATE WAIT n” syntax to lock the same row. But as the autonomous transaction pragma causes my second update to run in a different transaction context, the session will sleep and wait – for itself!

Let’s see how it works (Oracle 9.2.0.8):

SQL> set timing on
SQL>
SQL> exec sleep(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.03
SQL>
SQL> exec sleep(2);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.00
SQL>
SQL> exec sleep(3);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.00
SQL>
SQL> exec sleep(4);
BEGIN sleep(4); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.SLEEP", line 14
ORA-06512: at "SYS.SLEEP", line 21
ORA-06512: at line 1

So it works! But only up to 3 seconds. Why? This is because a session waiting for itself inevitably results in a deadlock (a self-deadlock).

In Oracle normally the deadlock detection mechanism kicks in after a session has been sleeping for 3 seconds, so if the SELECT FOR UPDATE WAIT n operation takes more than 3 seconds, the deadlock will be detected and terminated.

There are few special cases and improvements in Oracle 10g+ though, but about this I’ll write in a future post…

This entry was posted in Cool stuff, Oracle and tagged . Bookmark the permalink.

9 Responses to Using autonomous transactions for sleeping

  1. Gabe says:

    Well, one may end up sleeping for more than ‘seconds’.

    In a multi-user environment, one sleep call will block another, since ‘rollback to savepoint’ does not close the transaction.

  2. tanelp says:

    Gabe, yep this piece of code was only for demonstrating a hack what I’ve used in case where I needed to sleep in PL/SQL when having no access to DBMS_LOCK.

    Even though it would is possible to add global session specific rows in that locking table, I would always prefer using DBMS_LOCK or Radino’s Java approach for real application sleeping.

    Radino, very nice example by the way, but in that particular case I didn’t have Java installed in DB.

  3. radino says:

    I think java.lang.Thread.sleep is better than dbms_lock and the approach you just described.
    Code is very simle..

    SQL> create or replace procedure sleep(x_millis in number) as language java
      2  name 'java.lang.Thread.sleep(int)';
      3  /
    
    Procedure created.
    
    SQL> set timing on
    SQL> exec sleep(10000);
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:10.02
    
  4. Franco says:

    Well done, great example of creative thinking! :-)

  5. gp says:

    You can also use sleep procedure from user_lock package which by default is granted to public.
    Note though that you must express the time granularity in tens of millisecs.

  6. tanelp says:

    I’m not aware of any user_lock packages in recent Oracle versions. Are you sure this is a built-in Oracle package?

  7. gp says:

    Sorry, I forgot to say it is not created by default.
    See $OH/rdbms/admin/userlock.sql

  8. tanelp says:

    Ok, nice proc, didn’t know about that.

    However for my purpose (having sleep ability for scripts – without needing to change anything) this probably won’t work as this would require creation of objects into SYS schema…

  9. nice idea !

    I created an improved version of sleep which supports all natural numbers as input

    create table t(a) as select 1 from dual;
    
    create or replace procedure sleep(seconds in number default 1) is
    
       resource_busy exception;
       pragma exception_init (resource_busy, -30006);
    
       dummy number;
    
       procedure sleep_on_rowlock (p in number) is
               pragma autonomous_transaction;
               dummy number;
       begin
          execute immediate 'select a from t for update wait '||to_char(p) into dummy;
       exception when resource_busy then return;
       end;
    
    begin
       if seconds <= 0 then return; end if;
    
       savepoint sleeper_savepoint;
       select a into dummy from t for update;
       
       for i in 1 .. floor( seconds / 3) loop
          sleep_on_rowlock(3);
       end loop;
       if trunc(mod(seconds, 3)) > 0 then 
          sleep_on_rowlock(trunc(mod(seconds, 3)));
       end if;
    
       rollback to savepoint sleeper_savepoint;
    end;
    /
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>