Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Santu.

Asked: October 05, 2018 - 11:03 am UTC

Last updated: October 06, 2018 - 6:19 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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.

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 06, 2018 - 5:08 am UTC

We are running DML using hibernate. We are not running any query manually. So how we can apply for "update wait <time> " here. We can not explicitly mention that in our query as query is being generated by our application during runtime. Is there any other way to do same. Mysql and ms SQL is having that lock wait timeout facilities available to be set globally for each query. Why oracle did not give that. This is definitely a lackage which is creating unnecessary blocked sessions and application performance impact.
Connor McDonald
October 06, 2018 - 6:19 am UTC

If Hibernate does not support a timeout, this strikes me more as a shortcoming of the Hibernate LockMode rather than a shortcoming of the database, because we *do* support a timeout - its just that Hibernate didn't give you a way to access it.

Perhaps tackle this from the other direction.

Check out MOS Note 1557657.1

It shows an example of using Resource Manager to detect/kill sessions that are holding blocking locks.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.