[ic] Database values changing during a job

[Grant]

> > I have a job that allocates available inventory to orders based on a
> > bunch of different factors.  It takes about 20 minutes to run and it's
> > run every hour.  The problem is, if certain values change during the
> > job's execution, it will be thrown off.
> >
> > I thought of setting up a cache of certain values before the job is
> > run and comparing them when the job is complete so the job can be
> > re-run if anything has changed.  Alternatively, is there anything
> > built into mysql that I could use to straighten this out?
> >
> Perhaps you could set up a couple of staging tables.  Copy working data
> into the staging table(s) and run your job against those.  I'm sure you
> don't want to lock the tables for 20 minutes at a time, every hour.

A total rewrite of my inventory allocation system has the job
executing in 40 seconds instead of 20 minutes.  Kind of embarrassing
really.  Anyway, how does table locking work in the context of IC?  If
I lock a table for 40 seconds, how are read and write requests handles
during the locked period?  Will they fail, or will they be queued
until the table is unlocked?

> On the other hand, wouldn't it be better to allocate inventory to orders
> as soon as the orders are placed, and/or as soon as new stock comes in,
> rather than running a job every hour.  It sounds a lot less error-prone
> to me.

There are certain conditions under which I don't want my inventory
being allocated to certain orders.  If one item in the order is out of
stock, for example, I don't want to reserve the inventory for the rest
of the items in the order when they could be shipped to someone else.
Or if someone chooses PayPal as their payment method, I don't want to
reserve inventory for them until they've made their payment.
Basically, it's useful to be able to re-allocate inventory
periodically based on different conditions.

- Grant