remote transaction timeout

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

SQL> update [email protected] set sal=sal+1
where ename='SCOTT';
1 row updated.

SQL> update [email protected] set sal=sal+2
where ename='SCOTT';
update [email protected] 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 [email protected]
where ename='SCOTT'
for update wait 2;
select ename, sal from [email protected]
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