SELECT FOR UPDATE
The correct way to update records in Oracle is by first locking the rows for update and then issuing the update command.
SELECT FOR UPDATE command will select the specific records from the table and lock those records. This prevents other users from updating the records as the lock on those records is held by the current session.
SELECT FOR UPDATE command allows you to get records locked first. In the same session you can proceed with updating the records and proceed further by issuing commit.
SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE;
Let us assume that some other session is holding lock on the records returned by – SELECT * FROM EMP WHERE DEPTNO=10. The above query will wait until the lock is released.
If you do not want to wait (due to lock by some other session on the records you want to select for update) then use NOWAIT clause
SELECT * FROM EMP WHERE DEPTNO=10 FOR UPDATE NOWAIT;
The above command will check if there is any existing lock on the records. If records are not locked, you will hold the lock. If records are locked, the above command will not wait until the lock is released. It will return an error.
The correct way to update records is by always using SELECT FOR UPDATE.