Subject Re: [firebird-support] Odd problem - inconsistent result set
Author Vlad Khorsun
> I'm facing a strange problem for the first time with FB
> I have a customer which deliveres about 150 invoices per day, and it
> uses Firebird for about 5 years to generate the packing list from the
> invoices. The generation process is based on a single query which get
> the invoice items and values inside a Delphi + IBO program, and then
> the program inserts the items in the packing list.
> Recently the customer started to report a problem where sometimes the
> number of items in the packing list was different from the number of
> items in the invoice. It is happening about 2 or 3 times per day, not
> every day.
> After many tests, i wasn't able to find out the reason, so i asked the
> employee who generates the packing list to turn IBObjects sql monitor
> on all the time, and i did wait to see the results.
> Today the problem happened. Now i have the SQL history in hand and i
> can see the SQL instruction (it has no parameters), i see the lines
> returned and it is missing two rows in the result set, compared to the
> same query executed again later in IBExpert.
> I also use IBLogManager in this database, and i can say for sure
> nobody inserted, deleted or modified any item in this invoice after
> the packing list was generated. (i see no changes in log records, and
> also the generated pk values are sequential for all items of this
> invoice and keep in the sequence for the next invoice, so no records
> was inserted or removed)

Is it possible that transaction(s) which created that missed rows (invoice
items) was not committed when transaction which generated packing list
was started ?

> The only change i did recently was a FB upgrade from 2.0.3 to 2.0.4
> The plans used in IBO and in IBExpert are exact the same.
> Do someone have a clue where else should i search to find out what is
> happening in this database?

Is it possible that index used to filter invoice items was broken at packing
list generation time and rebuilt later ?