Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    30

    Unanswered: -244 Could not do a physical-order read to fetch next row

    hi , I got two procedures like below :
    PART A :

    foreach curPhysDel with hold for
    select state into v_state
    from test
    where state = 72
    and lastUpdate < v_dateMin

    update test
    set state = 99,
    lastupdate = v_lastupdate
    where current of curPhysDel;
    end foreach

    PART B :

    foreach curPurge with hold for
    select state into v_state
    from teste
    where state = 99
    and creationDate < v_dateMin
    delete from test where current of curPurge;

    end foreach

    A set lock mode wait to 5 was set before launch theses two procedures.

    I launch the part A one hour early than part B.
    In the table test there are more than millions records.

    No index on state, neither on creationdate.

    As long as I launch part B, i got error like below :
    (SQL exception ) -244 Could not do a physical-order read to fetch next row

    (SQL exception) ISAM ERROR : Lock timeout expired

    I got suggestion from eric that i should create index base on state and creationdate. The problem is, for more than millions records each day (creationdate), the state actually only have 6 possible values, so is it worthy to create index ? will it decrease the performance due to this extra index?

    Could anybody explain to me? Thanks.

  2. #2
    Join Date
    Oct 2005
    Posts
    6
    Has your first transaction (A) done a commit work ?

    If it hasn't and it is still connected to Infomrix then it is still holding locks on any rows/index key values inserted, updated, deleted etc and your second transaction (B) will hit the locks placed by transaction A and time out with error 244 after waiting LOCK MODE WAIT seconds.


    Make sure your transaction A has done a COMMIT.
    Also you might investigate if you table is using ROW or PAGE locks. ROW locks are generally more suitable for Multi user concurrent access.

    ALTER TABLE test LOCK MODE(ROW).

    You can read the transaction in progress changes made by transaction A from Transaction B if you execute SET ISOLATION TO DIRTY READ in transaction B before readng the data. But you will still get an error on the delete if you try to change the same rows which are uncommitted from transaction A.

  3. #3
    Join Date
    Oct 2005
    Posts
    30

    Yes, i have used the 'commit work'

    Acutally here is part of my code , even now I have created index on id fragmented,with set lock mode to wait 5, seems still have lock time out.



    First one :
    begin work;
    foreach curPhysDel with hold for
    select id into v_state
    from prg
    where id=1 -- DEACTIVATED

    update prg
    set id = 2 -- PHYSICAL_DEL,
    where current of curPhysDel;

    let v_counter = v_counter + 1;
    let v_nbrLocks = v_nbrLocks + 1;

    if v_nbrLocks > p_maxLocks then
    let v_nbrLocks = 0;
    commit work;
    begin work;
    end if;

    if v_counter >= p_maxRows then
    commit work;
    return v_counter;
    end if;

    end foreach;
    commit work;


    Second one:
    begin work;

    foreach curPurge with hold for
    select id into v_state
    from prg
    where id = 2
    delete from prg where current of curPurge;

    let v_counter = v_counter + 1;
    let v_nbrLocks = v_nbrLocks + 1;

    if v_nbrLocks > p_maxLocks then
    let v_nbrLocks = 0;
    commit work;
    begin work;
    end if;

    if v_counter > p_maxRows then
    commit work;
    return v_counter;
    end if;

    end foreach;
    commit work;

  4. #4
    Join Date
    Oct 2005
    Posts
    4
    the "with hold" clause there, is probably causing the lock(s) to be retained after the commit work, unless you are closing the cursor as part of the code which you didn't include here.

  5. #5
    Join Date
    Oct 2005
    Posts
    30
    in this way, should i declare the cursor first? how to close it ?
    I use 'close curPhysDel' before commit work, seems doesn't work, syntax error?

    meiwen

  6. #6
    Join Date
    Oct 2005
    Posts
    30
    seems informix SPL doesn't suppor CLOSE CURSOR statement! how to do?

Posting Permissions

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