oracle - Difference between FOR UPDATE OF and FOR UPDATE -

what makes difference, when use for update of sal or write for update.

according o'reilly

the of list of update clause not restrict changing columns listed. locks still placed on rows; of list gives way document more intend change. if state update in query , not include 1 or more columns after of keyword, database lock identified rows across tables listed in clause.

which means, when specify column name for update of sal, other user can make change sal column only. but, practically, not case. i'm still getting lock in other session. can explain difference.

update

 ----- session 1  declare  emp_info emp.ename%type;  cursor emp_cur select ename emp join dept using(deptno) deptno=&no update of sal;  begin  open emp_cur;  loop  fetch emp_cur 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 deptno=10;   ---- hanged/waiting in session 2 

from oracle documentation:

use of ... column clause lock select rows particular table or view in join. columns in of clause indicate table or view rows locked. specific columns specify not significant. however, must specify actual column name, not column alias. if omit clause, database locks selected rows tables in query.

if query references single table there no difference between for update , for update of ..., latter may still useful self-documentation indicate columns intend update. doesn't restrict can update though. if have:

cursor cur select * emp update of sal; 

then can still do:

update emp set comm = comm * 1.1 current of cur; 

but if there more 1 table for update of ... lock rows in tables contain columns specify in of clause.

contrary think you're saying in question. specifying for update of sal not lock sal column; can never lock single column, minimum lock @ row level. (read more locks). locks rows in table contains sal column, selected query.

---

in update question, cursor query joining emp , dept, of clause has sal, column in emp table. rows in emp table locked when cursor opened, , locks won't released until commit or rollback session. within cursor loop can do:

update emp set ... current of emp_cur; 

... update row in emp table relates iteration of loop. cannot do:

update dept set ... current of emp_cur; 

... because rows in dept table not locked, because no columns in of. means in second session dept rows can updated freely, not locked first session.