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
--------------------------- -------
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

Categorized as sql Tagged

By Laurent Schneider

Oracle Certified Master

Leave a comment

Your email address will not be published.