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…