Helen, I think your about right on this. If the procedure call that
notified the event_alerter was the last command in the transaction
that wrote the data, before commit, (which it was) then it would be
possible for the office to be notified before the commit of all the
inserts and updates. After I got to thinking about it, I questioned
them about the last one they were having problem with, and thats
pretty much what happened. The office was waiting on the
notification that the picklist was completed, upon receiving it, she
quickly generated the auto invoice, but since not all the data had
been written, it missed one.

I committed the first transaction, put the procedure for
event_alerter in a second transaction and will implement your
suggestion for a flag or pesimistic locking.

Thanks
Dixon Epperson
--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 09:03 PM 7/01/2004 +0000, you wrote:
> >I'm having a problem with Firebird 1.5 RC5 that seems to have no
> >predictable cause, but I am wondering if its a cache flushing

issue.

> >I'm using SuperServer, and its on XPProfessional.
> >
> >Here is the big picture. The warehouse receives a picklist, prints
> >it then fills it. When the mark it filled on the terminal in the
> >warehouse, within a transaction it updates or inserts on 4 tables,
> >then calls the procedure that calls the event_alerter, then commits
> >the transaction. If there is an error, it rolls it back without
> >calling the event_alerter.
> >
> >The office is monitoring the event_alerter through a user interface
> >built with Delphi7. When the office receives notice from the
> >event_alerter that an order has been filled, they have to start the
> >invoicing form, if its not already running, and have it generate an
> >invoice based on what the warehouse reported as having filled.
> >
> >The warehouse might fill 50 picklist a day. One day,49 of them

will

> >be accurate, the next two or three days, all of them will be
> >accurate, but every so often, one invoice will fail to have one of
> >the items the warehouse marked as filled, which means they don't
> >invoice for everything they ship out.
> >
> >I've moved the procedure that calls the event_alerter to outside

the

> >transaction. And I am actually thinking of putting it in a

separate

> >thread with about a 5 second delay because I'm suspecting the

servers

> >isn't getting all the data written to the tables in a timely

manner.

> >
> >Later, when I go back and make another invoice off this same
> >picklist, it will generate it correctly.
> >
> >Does anyone have any ideas on this, as to what it might be and how

to

> >prevent it.
>
> What it looks like to me is that the warehouse staff might be going

back

> later and adding another item. The time gap might be tiny but it's

enough

> to create a concurrency blip. The invoicing app is picking up the

original

> data and doing its stuff in one transaction, while the warehouse

has

> meanwhile done its bit of tweaking in another. From outside of

both

> transactions, you see the committed work of the warehouse's second
> bite; while in the Invoice app's transaction, the original view,

captured

> from the initial insert, is still seen.
>
> As long as it is possible to update a picklist, regardless of

what's

> happening elsewhere, you'll have the potential for this kind

of "race

> condition". I can't offer a detailed solution but, in principle,

your apps

> need some combination of status-flagging, transaction isolation and
> pessimistic locking to ensure that warehouse can't go back and

double-dip

> if invoicing has received the data; and invoicing can't get the

data if

> warehouse is in the process of double-dipping.
>
> /heLen