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