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