Q:

Cached reads, immediate writes with SQL Alchemy

The Beaker test system includes a central scheduler that assigns queued recipes to available systems based on various criteria defined in the recipe.
This is handled by continually looping over the current queue of recipes and looking for available systems that can handle that recipe. As this approach queries retrieves updated system status info from the database for each recipe, it suffers from a race condition  where tasks further back in the queue are sometimes incorrectly given first shot at systems that become available while a scheduling pass is running.
For example, suppose we have 2 systems A and B, and recipes 1 (which needs System A), 2 (which can run on either), 3 (which can also run on either). When the scheduling pass starts, system A is already busy, so recipe 1 has no available systems and we continue on to look at recipe 2 and assign it to system B. In the background, system A finishes what it was doing and flags itself as available in the database. The scheduler now moves on to considering recipe 3, sees system A is available and assigns recipe 3 to system A. Recipe 3 has managed to jump the queue and claim system A, even though recipe 1 should have been given it.
I'm trying to come up with a near term fix for this that doesn't involve completely redesigning the scheduling logic (although we're also exploring some longer term ideas on that front).
The best solution I currently have is to have a separate SQL Alchemy session open purely as a cache of the state of the database at the start of the scheduling pass, as well as any system availability state changes the scheduler has made. This transaction would be aborted at the end of the scheduling pass. During the scheduling pass, all state changes would be written to the database as normal, but would also need to be written into the cache connection.
This seems really ugly though, so I'm wondering if anyone has any better ideas for how to handle this with SQL Alchemy.

A:

After reflecting on this for a while, I realised the core consistency problem lies in the fact that when a system finishes a task, it actually gets marked as idle in the database, even if there are currently queued recipes that could execute on that system.
What we need to do instead is ensure that if there are recipes that could execute on that system, the system is immediately reassigned to the first such recipe in the queue, rather than ever being placed back in the idle pool. Similarly, if a new system is added or placed back into automated mode, then we immediately assign it some work, rather than letting it enter the idle pool.
So the main scheduling loop would end up dealing solely with the task of assigning new recipes that come in to systems that are currently idle, while the assignment of queued recipes to systems would be more event driven.
At that point it's just a typical problem of managing concurrent access to the database to ensure two systems don't start trying to handle the same recipe.

