RE: funny select cursor behavior
Posted in 2004
Topics: Performance & Tuning, Stored Procedures & SPL
When ID becomes the primary key, the result is correct
most probably because the SELECT cursor is using
different (primary key) index.
And again: Index behavior should be hidden
from 'business logic' developers (part from performance).
This is a fundamental concept of SQL servers!!!
-----------------
Alexey
> -----Original Message-----
> From: rkusenet [mailto:rkusenet@sympatico.ca]
>
> 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
sending to informix-list