Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Brussels
    Posts
    57

    Unanswered: concurrent access

    We want the update thousands of rows in different tables with a stored procedure.
    We've tried but didn't find how to lock the rows within a transaction on the select.
    A "select for update" seems not to be permitted in SPL.
    The sequence :
    begin work;
    set isolation to repatable read;
    select * from xx where yy="date1" into temp table titi" ;
    ... other stuff ..
    delete ...
    insert ...
    -----
    When another process has a lock (select for update ..) on the row, The procedure can still select it, do the stuff ... and it will wait lockout on the delete.
    If the other process has changed data, the procedure will erase it.
    -----
    How should we resolve this concurrent access ?
    (aix4.3, IDS7.3ud6)
    thanks,
    yves

  2. #2
    Join Date
    Nov 2003
    Location
    sweden
    Posts
    17
    Quote Originally Posted by supinformix
    We want the update thousands of rows in different tables with a stored procedure.
    We've tried but didn't find how to lock the rows within a transaction on the select.
    A "select for update" seems not to be permitted in SPL.
    The sequence :
    begin work;
    set isolation to repatable read;
    select * from xx where yy="date1" into temp table titi" ;
    ... other stuff ..
    delete ...
    insert ...
    -----
    When another process has a lock (select for update ..) on the row, The procedure can still select it, do the stuff ... and it will wait lockout on the delete.
    If the other process has changed data, the procedure will erase it.
    -----
    How should we resolve this concurrent access ?
    (aix4.3, IDS7.3ud6)
    thanks,
    yves
    try this one
    begin work ;
    lock table table_name in exclusive mode;
    Linux

  3. #3
    Join Date
    Apr 2004
    Location
    Brussels
    Posts
    57

    Wink

    no
    we want not to lock the table during hours.
    Other users must have update access.
    Concurrent access, you now.

    BTW I' ve found a solution on the IIUG forum :
    I had to do a dummy update in a transaction with an update cursor.
    begin;
    set isolation to repeatable read ;
    foreach with cursor1 for select .....
    update ... -- a dummy update on the row and it will be locked until commit
    end foreach
    ... -- now my stuff with the real update
    commit ; -- this will unlock
    Yves & Willy

Posting Permissions

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