How to set lock wait timeout in Oracle. We are executing insert/update/delete from java applications. Sometimes due to long running transactions or slowness lock acquired by one transaction on particular row gets hit by another transaction and blocking sessions happens. In that situations we want to apply lock wait time out so that waiting sessions does not wait for the lock for long time and gets timeout after some time. Is there any way to do. Please advise. I have heard about distributed_lock_timeout. How that can be applied here.
The parameter distributed_lock_timeout relates to statements over database links. So it's probably not what you want.
For blocking DDL statements you can control the timeout with ddl_lock_timeout. This gives the time to wait for DDL in seconds.
You can see this with, in session 1:
create table t (
c1 int, c2 int
);
insert into t values (1, 1);
Then in session 2 run:
alter session set ddl_lock_timeout = 10;
alter table t drop ( c2 );
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Not that non-blocking DDL (such as alter table ... add column) will still wait for session 1 to commit/rollback.
When it comes to DML, you can run select ... for update wait N before your update/delete. This will bail out if it can't get the lock within N seconds.
Back in session 1 run:
commit;
update t
set c1 = 0, c2 = 0;
Then in session 2:
declare
cur sys_refcursor;
begin
open cur for
select * from t
for update wait 10;
delete t;
end;
/
ORA-30006: resource busy; acquire with WAIT timeout expired
ORA-06512: at line 4
select * from t;
C1 C2
1 1