for update of

[zulu99]

Hi All

In a cursor using pessimistic locking i would like to know what is
the essential difference when

cursor c is select * from emp for update;

and

cursor c is select * from emp for update of sal;

If there are no column level locks in Oracle . How are the 2
statements different. To ellaborate what different things can
i carry out after i open the cursor in one case which i cannot
in another.

even if i say for update of sal i can update other column values inside the loop of the cursor.
So whats the difference.

Kindly Respond

Regards
Sushant

[slimdave]

In your example there is no difference -- all the rows of emp get an exlcusive lock placed upon them.

The difference between the two usages comes when you apply them to a multitable query, as in this example taken from Oracle 9iR2 SQL Reference

The following statement locks rows in the employees table with purchasing clerks located in Oxford (location_id 2500) and locks rows in the departments table with departments in Oxford that have purchasing clerks:

SELECT e.employee_id, e.salary, e.commission_pct
   FROM employees e, departments d
   WHERE job_id = 'SA_REP'
   AND e.department_id = d.department_id
   AND location_id = 2500
   FOR UPDATE;

The following statement locks only those rows in the employees table with purchasing clerks located in Oxford (location_id 2500). No rows are locked in the departments table:

SELECT e.employee_id, e.salary, e.commission_pct
   FROM employees e, departments d
   WHERE job_id = 'SA_REP'
   AND e.department_id = d.department_id
   AND location_id = 2500
   FOR UPDATE OF e.salary;