I read about the FOR UPDATE clause in Select statement recenty. It acquires the row level locks for the dataset selected by select statement unless commit or rollback is issued.
I jus want to ask in which conditions we should use this feature. In1 of the procedure , it is used and the transaction of which the select is part is very long. (About 40 seconds) ,. Is it right to use this here?
Use FOR UPDATE to provide pessimistic locking whenever you select records in a cursor that you intend to update. This ensures that the record you update is the same record you selected earlier (be it 40 seconds or 40 milliseconds earlier).
The alternative is optimistic locking, where you do not select with FOR UPDATE, but instead explicitly verify that the record you update is the same one you selected by inspection - e.g.
SET sal = sal*1.1
WHERE empno = cursor_rec.empno
AND sal = cursor_rec.sal; -- This line ensures no one else changed the salary already
If you don't use either of these approaches then you will end up with "lost updates", where 2 users work on the same record simultaneously and one trashes the others work unintentionally.