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.

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.

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;

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.

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

seems informix SPL doesn't suppor CLOSE CURSOR statement! how to do?