Hello Jarrod, I'm using a similar system in one of my applications, but
with minor modifications:

Jarrod Hollingworth wrote:

> 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.

No need for 1-hour limit, just read everything. I'm using client
attachment ID as a field in the table, and I check if the client is
still connected via MON$ATTACHMENTS. Rows for clients that "lost
connection" without cleaning up their rows (delete) are ignored. If
order is canceled, rows are deleted anyway (as you note in steps 5 and 7).

> 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,

I just do UPDATE stock SET quantity = quantity - :order_quantity.

> 5. If order is cancelled delete Stock_Reserve rows for the order.
> 6. Pending orders time-out and are automatically cancelled after 1 hour.

Same comment as with step 2.

> 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).

All I can say that this system works rather well. I'm using NO_WAIT
transactions for all the inserts and try to repeat from application in
random short intervals.

HTH

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================