Subject RE: [firebird-support] Read uncommitted
Author Jarrod Hollingworth
I believe that Gustavo has this scenario:

(a) Salesperson must check and reserve quantity of stock at time of order
and it is infeasible to physically inspect and retrieve the quantity first.
e.g. Large warehouse.
(b) Order process takes some time (30 minutes).
(c) Other salespeople must be able to check and order from remaining stock
for their orders without waiting for pending orders to be completed.

The "dummy update" method is not practical because it does not cater well
for (b) & (c). ie. You can't update stock quantity until the order is
completed but that takes some time and other people need to be able to see
and reserve stock during that period.

I think that something like the following would work:

1. Add a Stock_Reserve table with columns for order id, stock id, quantity,
reserve date-time, salesperson id, terminal id.
2. When checking stock levels subtract the SUM of Stock_Reserve quantity for
all rows where matching stock id and reserve date-time less than 1 hour,
from the actual Stock quantity.
3. When adding a line item for the order insert and commit a row to
Stock_Reserve with the quantity required.
4. When the order is complete i] update Stock with adjusted quantity where
current quantity = expected original quantity. If update affects zero rows
you need to re-read Stock and re-calculate and try again, ii] delete
Stock_Reserve rows for the order.
5. If order is cancelled delete Stock_Reserve rows for the order.
6. Pending orders time-out and are automatically cancelled after 1 hour.
7. On application startup delete Stock_Reserve rows (all, or only older than
X mins, or just for the terminal or salesperson, depending on whether
"application" is the central system or a client instance and how you want to
clear them in the event of a crash/system restart/outage).


Jarrod Hollingworth
Complete Time Tracking