At 08:16 AM 23/06/2003 +0200, you wrote:

> >
> >No!!! Firebird doesn't support dirty read! The order of isolation (from
> >lowest to highest, in Delphi-talk) is ReadCommitted, Concurrency and
> >Consistency. You never need Consistency except when you want to "freeze"
> >the state of the database for your entire operation.
> >
> >All you want to do here is insert new records while your reporting
> >procedure looks at some *other* records. Use two transactions - both
> >Concurrency or ReadCommitted, it won't matter much, since you are not doing
> >any updating. There is no potential for conflict here.
> >
> >By using Consistency, you are making it so that your transaction blocks any
> >DML, even inserts, to preserve a frozen view of the whole database. In
> >fact the only thing you want to freeze is the batch of transactions from
> >the last shift, which the new shift doesn't even touch.
> >
> >heLen
>
>
>Helen,
>
>I have a situation where different POSs (like the pumps, for example) can
>start a new record or update an existing record while the shift change is
>going on. So, for example, a new transaction at a petrol pump will create a
>record in a table, and mark it as being in use by that particular pump.
>When the financial transaction is complete (i.e., the pump has stopped
>pumping, and the client pays the money), the transaction is completed and
>moved to the LOG table.
>
>This may happen while the shift change is happening.
>
>I can figure out what to do in the Delphi code - but how would I handle
>this situation with respect to transaction control?

It's not a problem, because the shift change processing (should be) running
in its own transaction. The log rows are being *inserted* as a result of
the activities in the next shift, right? You are not updating anything in
that table...? that the shift change procedure is operating on?

I'm not really sure what your question means. You would have one
transaction object that's only used for the shift change operation. It can
just keep chuntering away until it's done.

Each unit of business at the pump would operate inside its own
transaction. I think that one pump can only dispense to one customer at a
time, right? If the money part is done first, e.g. a credit card routine,
with the POS counting off litres until the credit is used up, then that
part can be the first thing that happens in the (database) transaction,
since the transaction can see everything else that goes on within its own
boundaries. If the money part has to be done apart from the
pumping-and-counting bit, e.g. at the pay station, then that's fine
too. By that time, the pump has committed its transaction.

Even if someone comes through and fills up his moped from the same pump
while the other customer is waiting in the queue, the pay station just
starts a transaction, queries for the oldest unpaid record for that pump,
charges the customer, flags the sales record as paid and writes out to the
log file. All done, commit the transaction, ready to go on the next one.

Still, I don't know if that's what you meant...

heLen