You are trying to update particular rows in a table in Oracle and the statement taking too much time and after some time you get the error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

After getting this error you identified the problem, to follow Oracle best practices for application development, use the NOWAIT clause and minimize the use of LOCK statements unless the application requires it. When a DML statement runs, the NOWAIT clause returns immediately if there is already a lock on the resource you require for the DML statement.

To avoid the wait during the UPDATE attempt, use the NOWAIT clause in a SELECT before the UPDATE statement in all sessions, as follows:

select * from employees e
where employee_id = 101
for update of salary nowait

If another session already has the row locked, you will receive this error message:

Oracle Forms Recipes eBookDownload

How to avoid wait during dml queries execution due to row and table locking in Oracle.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.