Why we need SELECT FOR UPDATE in Oracle SQL?

Question

A database will automatically figure out how to process two or more transactions in parallel (as serializable schedules) by overcoming the read-write conflicts (using locking). By default there will be isolation among them.

So why do we still need to lock any data in the database by using statements like SELECT FOR UPDATE? Why can't we proceed with whatever we want in a transaction block?

Begin transaction
//queries
//commit
End transaction

Answer 1

why we can't proceed with whatever we want in a transaction block?

Because in Oracle, readers don't block writers. If you select from a table you don't lock it and there is no transaction. So, imagine you do:

select col1, col2 into l_col1, l_col2
from table1
where ...;

if (<some logic based on l_col2>) then
  update table1 set col2 = something where col1 = l_col1;
end if;

That's fine; but if two sessions run that code at the same time, one session could update the row between the other session selecting and performing its update - causing a lost update, or incorrect logic as the state wasn't actually as expected.

If instead you do select ... for update:

select col1, col2 into l_col1, l_col2
from table
where ...
for update;  -- or: for update of col2

if (<some logic based on l_col2>) then
  update table1 set col2 = something where col1 = l_col1;
end if;

then the row selected from the table is locked by the first session when it does select for update, so a second session has to wait for that transaction to complete before it can obtain the lock, and it will see the state of the row after the first update has applied. No lost update, no confusion.

You can also lock rows you don't want to update, as the documentation says:

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update, as in Example 9-6.

And you can tweak the behaviour:

By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement. For information about these clauses, see Oracle Database SQL Language Reference.

If the select for update is in a cursor loop then it also allows you to do

  update table1 set col2 = something where current of <cursor>;

When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. (The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor.)

Rows selected by the cursor query are locked as they are fetched. (But the table itself isn't locked.)