Oracle select for update

Oracle select for update
In most cases, the rows checked by the cursor are modified for the processing completed in the extraction loop. PL/SQL provides a syntax for such processing.

This syntax includes two parts: the for update clause in the cursor declaration part and the where current of clause in the update or delete statement.

Generally, the Select operation does not perform any lock settings on the row being processed, which allows other sessions connected to the database to change the selected data.

However, the result set is consistent. After the activity set is determined, Oracle will take a snapshot of the table when the open operation is executed. Any changes submitted before this time point will be reflected in the active dataset. Any changes made after this time point, even if they have been submitted, will not be reflected unless the cursor is re-opened. However, if you use the for update clause, a mutex lock will be added to the corresponding row of the previous active set returned by the open clause. These locks will prevent other sessions from changing the rows in the active set. Until the entire transaction is committed.

Example:
Declare
Cursor c_cur is select * From studends for update of XM;
Begin
Open c_cur;
While c_cur % found Loop

Update studends set XM = 'A' | XM where current of c_cur;

End loop;
Close c_cur;
Commit;
End;

Note: 1. The update statement only updates the columns listed in the for update clause declared by the cursor. If no column is listed, all columns can be updated.

2. In the example, commit is completed after the extraction cycle is completed, because commit releases all the locks held by the session. Because the for update clause acquires locks, commit releases these locks. When the lock is released, the cursor is invalid. Therefore, Oracle errors will be returned for subsequent extraction operations.