funny select cursor behavior
Hi, everybody,
We encountered interesting problem:
when SELECT cursor is running using two-field index,
and there is an update of the second field inside the cursor,
the cursor actually retrieves twice more records then
it should actually (logically) return
This is a small code to reproduce the problem:
-----------------------------------
create procedure test_update()
returning int;define iCount, iId int;
create table tab1 ( id serial, status char(1));
create index idx1 on tab1(id, status);for iCount in(1 to 1000)
insert into tab1(id, status) values(0,'A');end for;
update statistics for table tab1;let iCount = 0;
foreach select --+INDEX(tab1 idx1)
id into iId from tab1 where id between 1 and 100 order by id
update tab1 set status = 'B' where id = iId; let iCount = iCount + 1;
end foreach;
drop table tab1;return iCount;
end procedure;
execute procedure test_update();
drop procedure test_update();
-----------------------------
-- Expected result is 100. The real result is 200
-----------------------------
It is quite clear why this is happening:
The server is reading horizontally-linked index pages,
and, because of the update, the list is rebuilding
while the cursor is running...
I know why this is happening.
I don't know how to explain this to our developers,
who are not supposed to keep in mind all the aspects
of index behaviour when they develop their application code..
If this is a feature (not a bug),
is it described in the 'Manual'???
----------------
Alexey Sonkin
sending to informix-list
Change the field type to integer not null primary key
and the result is correct.
So I guess this is a bug with serial field.
Ravi
"Alexey Sonkin" <alexeis@grandvirtual.com> wrote in message news:cci8jq$avn$1@news.xmission.com...
>
> Hi, everybody,
>
> We encountered interesting problem:
> when SELECT cursor is running using two-field index,
> and there is an update of the second field inside the cursor,
> the cursor actually retrieves twice more records then
> it should actually (logically) return
>
> This is a small code to reproduce the problem:
>
> -----------------------------------
> create procedure test_update()
> returning int;> define iCount, iId int;
> create table tab1 ( id serial, status char(1));
> create index idx1 on tab1(id, status);> for iCount in(1 to 1000)
> insert into tab1(id, status) values(0,'A');> end for;
> update statistics for table tab1;> let iCount = 0;
> foreach select --+INDEX(tab1 idx1)
> id into iId from tab1 where id between 1 and 100 order by id
> update tab1 set status = 'B' where id = iId;> let iCount = iCount + 1;
> end foreach;
> drop table tab1;> return iCount;
> end procedure;
>
> execute procedure test_update();
> drop procedure test_update();>
> -----------------------------
> -- Expected result is 100. The real result is 200
> -----------------------------
>
> It is quite clear why this is happening:
> The server is reading horizontally-linked index pages,
> and, because of the update, the list is rebuilding
> while the cursor is running...
>
> I know why this is happening.
> I don't know how to explain this to our developers,
> who are not supposed to keep in mind all the aspects
> of index behaviour when they develop their application code..
>
> If this is a feature (not a bug),
> is it described in the 'Manual'???
>
> ----------------
> Alexey Sonkin
> sending to informix-list