I'm not sure I understand the problem....

If you are logging to one table, when your shift change happens, you will be
doing selects on that same table to generate data for other tables, which
wouldn't cause any locking on your existing table....

Or, if you do have to update the log table, then if you update only for the
previous shift, you wouldn't cause locks on the new data coming in...(One is
an insert, and the other is an update on "old" data)....

Hope you understand.....

-----Original Message-----
From: Tim Ledgerwood [mailto:tim@...]
Sent: 20 Friday June 2003 12:36
To: firebird-support@yahoogroups.com
Cc: raymond@...
Subject: [firebird-support] Design problem

Hi all,

Need some advice. I have a log table in my database, that records every
transaction passed to it from fuel pumps and points of sale at Petrol
Stations.

So far so good - no problem there.

About once every 8 hours, the shift at the petrol station changes, and the
system goes through a "change shift" procedure. During this procedure,
various reports on income, expenditure, stock and fuel levels are drawn,
based heavily on the data in the LOG table.

This is where the problem comes in. I cannot afford any lock conflicts AT
ALL - i.e., the fuel pumps cannot stop pumping to accommodate a shift
change.

How do I do it? I had thought of having a shift data table, which would
essentially hold the same information as the LOG table. Data would be
written to the shift data table when it is written to the LOG table.
However, when a shift change is happening, then data will only be posted to
the LOG table, allowing the shift close procedure to operate independently
of the logging.

Suggestions?

Thanks in advance

Tim



[Non-text portions of this message have been removed]