remote transaction timeout

If you access one table via database link and the row is locked, you may get a timeout


SQL> update emp@l set sal=sal+1
where ename='SCOTT';
1 row updated.

          SQL> update emp@l set sal=sal+2 
          where ename='SCOTT';
          update emp@l set sal=sal+2 
          *
          ERROR at line 1:
          ORA-02049: timeout: distributed 
          transaction waiting for lock
          ORA-02063: preceding line from L

          Elapsed: 00:01:00.00

SQL> sho parameter distr
NAME                        VALUE
--------------------------- -------
distributed_lock_timeout    60

This timeout (default 60 seconds) could be tuned, maybe to 300 seconds, if you are doing huge remote transactions

But what if you don’t want to wait one minute to get an exception? Lock the row before update then !


          SQL> select ename, sal from emp@l
          where ename='SCOTT' 
          for update wait 2;
          select ename, sal from emp@l
          *
          ERROR at line 1:
          ORA-30006: resource busy; 
          acquire with WAIT timeout expired
          ORA-02063: preceding line from L
          Elapsed: 00:00:02.01

If you want to wait only 2 seconds and not one minute, or even NOWAIT (0 second), then lock the row first. Depending on your application, waiting one minute to get an exception may be unacceptable

Leave a Reply