SELECT FOR UPDATE, SUM, UNION ALL, and deadlocks

Question

Question

My understanding is that a SELECT FOR UPDATE query should try to define a specific order for the rows to be locked so as to prevent a deadlock.

How do I do that properly when my query uses SUM and UNION ALL?

SELECT SUM(availabilities.qty) AS qty FROM (
        -- inventory snapshots
        SELECT SUM(qty) AS qty FROM myschema.inventorysnapshot
        WHERE (product='Product1' AND site='Store1')
    UNION ALL
        -- reservation
        SELECT SUM(qty) * -1 AS qty FROM myschema.reservationitem
        WHERE (product='Product1' AND site='Store1')
    UNION ALL
        -- sales transaction
        SELECT SUM(qty) * -1 AS qty FROM myschema.salestransaction
        WHERE (product='Product1' AND site='Store1')
) availabilities;

Context

My query relates to IMS (Inventory Management System) in the context of calculating the availability of a given item at a given site by finding out how many items are currently available. This then gets used to figure out if the given reservation of the item via a webshop is to be allowed and acted upon.

There are multiple tables involved. To simplify the whole thing, assume we only have InventorySnapshot, SalesTransaction and Reservation.

Obviously, multiple concurrent requests for multiple different product/site combinations may happen (so in fact the SQL presented above should actually be able to handle a list of tuples of product/site, but I have not yet reached that point in our development). When a request figures out that a collection of items is available, it may then create a new reservation for that item within the same transaction as the one which did that SELECT FOR UPDATE statement.

Finally, I'm not entirely certain how other concurrent requests might handle the fact that while they were waiting to acquire some locks, another transaction might have added an extra reservation for the product/site tuple the current request is calculating. Those new reservations should obviously be taken into account by the request which would subsequently be able to acquire the locks.

This is all to prevent over-reservation scenarios, so consistency is extremely important. But of course, the goal is also to keep this "Create a Reservation" action (which needs to make an availability request prior to reserving) as fast as possible.