Difference between FOR UPDATE OF and FOR UPDATE

Question

What makes difference, when I use FOR UPDATE OF SAL or simply write FOR UPDATE.

According to O'Reilly

The OF list of the FOR UPDATE clause does not restrict you to changing only those columns listed. Locks are still placed on all rows; the OF list just gives you a way to document more clearly what you intend to change. If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, then the database will then lock all identified rows across all tables listed in the FROM clause.

Which means, when I specify column name with FOR UPDATE OF SAL, other user can make change with SAL column only. But, practically, this is not the case. I'm still getting lock in other session. Can anyone explain the difference.

UPDATE

 ----- SESSION 1

declare
 emp_info emp.ename%type;
 cursor emp_cur is select ename from emp join dept using(deptno) where deptno=&no for update of sal;
 begin
 open emp_cur;
 loop
 fetch emp_cur into emp_info;
 exit when emp_cur%notfound;
 dbms_output.put_line(emp_info);
 end loop;
 close emp_cur;
 end;

  ----- SESSION 2

  update emp set comm=5 where deptno=10;
  ---- hanged/waiting in session 2

Solution

From the Oracle documentation:

Use the OF ... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.

If your query references a single table then there is no difference between FOR UPDATE and FOR UPDATE OF ..., but the latter may still be useful as self-documentation to indicate which columns you intend to update. It doesn't restrict what you can update though. If you have:

CURSOR cur IS SELECT * FROM emp FOR UPDATE OF sal;

then you can still do:

UPDATE emp SET comm = comm * 1.1 WHERE CURRENT OF cur;

But if there is more than one table then FOR UPDATE OF ... will only lock the rows in the tables that contain the columns you specify in the OF clause.

Contrary to what I think you're saying in the question. specifying FOR UPDATE OF sal does not only lock the sal column; you can never lock a single column, the minimum lock is at row level. (Read more about locks). It locks all rows in the table that contains the SAL column, which are selected by the query.

---

In the update to your question, your cursor query is joining emp and dept, but the OF clause only has sal, a column in the emp table. The rows in the emp table will be locked when the cursor is opened, and those locks won't be released until you commit or rollback that session. Within your cursor loop you can do:

UPDATE emp SET ... WHERE CURRENT OF emp_cur;

... to update the row in the emp table that relates to this iteration of the loop. You cannot do:

UPDATE dept SET ... WHERE CURRENT OF emp_cur;

... because rows in the dept table are not locked, because no columns were in the OF. That also means that in your second session the dept rows can be updated freely, as they are not locked by the first session.