When should I use 'for update nowait' in cursors?

Question

In which case do we need to use for update nowait in cursors.

Answer 1

Using for update nowait will cause the rows to be busy and acquires a lock until a commit or rollback is executed. Any other session that tries to acquire a lock will get an Oracle error message like ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired instead of waiting the lock to release.

Session1:

CURSOR abc_cur 
IS 
select * from dept where deptno =10 for update nowait;

Here the rows are locked until the cursor is closed or a commit/rollback gets executed. If, meanwhile, another user from session 2 tries to access the same records then this will throw an error as shown below:

Session2:

select * from dept where deptno =10 for update nowait;

This user cannot even update or delete the same records that have been locked by the first session.

ERROR at line 1:
`ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired`

Usage: Now if you want to do some manipulation on certain sets of records and you don't want another user from another session to override your data then you must first lock the records(using for update nowait) and then do your manipulation. After you're done with your manipulation, close the cursor and commit.

EDIT Suppose there are 10 rows in temp and I execute the following script in my session 1 :

declare
  cursor abc is select * from temp for update nowait;
  temp abc%rowtype;
begin
  open abc;
  -- do slow stuff here
  close abc;
  commit; 
end;

In session 2, I execute the following while the script in session 1 is still running

select * from temp;

10 rows found 

If I execute the same script, in session 2, while the script in session 1 is still running

declare
  cursor abc is select * from temp for update nowait;
  temp abc%rowtype;
begin
  open abc;
  -- do slow stuff here
  close abc;
  commit; 
end;

Then I get ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired instead of waiting the lock to release.