for update of
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: for update of

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width