-
for update of
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
-
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
Code:
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;