5 ms·
> What happens if you have, say, two rapid-fire transactions updating the order, with another transaction updating one of the parts sandwiched between the two o
by hypirion 3y ago
> What happens if you have, say, two rapid-fire transactions updating the order, with another transaction updating one of the parts sandwiched between the two order updates?
You have a race condition here, regardless of whether you use history tables or not, no? It'll probably still be wrong for the user if you update the part first, then do the two transactions, as you now refer to the wrong (a more recent) part version.
I mean, that's the entire reason why Stripe have `products` with multiple `prices` that are "immutable": The price, tax details and so on can't be changed, but they can be archived or set as default. A line item will refer to the price itself, not the product.
If you somehow need to do multiple transactions and your parts aren't immutable, I think you must refer to some immutable history table -- regardless of whether you use the triggers, make an append only table or do something else. If you use the triggers mentioned in the post, you could save the time you looked at the part and do an insert like so:
INSERT INTO line_items (order, part_id, part_history_id)
SELECT ${order_id}, ${part_id}, ph.history_id
FROM parts_history ph
WHERE ph.part_id = ${part_id}
AND ph.systime @> ${as_of_time};
But to be fair, it seems unnecessarily risky. I'd just query the history as of now and use the history ID in the subsequent transaction, or make an append-only part version table and refer to that.
- mgsouth 3y agoNo race condition. As long as the transaction is properly constructed (let the database retrieve the unit prices and calculate the extended cost) it will abort and roll back if there's a conflict, including if a source row (in this case, product) changes. So your transaction could look like BEGIN; WITH x_lineitems AS SELECT li.id, pt.id pt_id, li.qty, (li.qty * pt.cost) ext_cost FROM (VALUES (${lineitem1}, ${qty1}), (${lineitem2}, ${qty2}) AS li (id, qty) x_order AS INSERT INTO orders (id, total_cost) SELECT ${order_id}, uuid_generate_v7(), (SELECT sum(ext_cost) FROM x_lineitems) RETURNING id, version_id -- Line items get same version ID as the order. INSERT INTO line_items (id, version_id, order_id, part_id, qty) (SELECT xli.id, xord.version_id, xord.id, xli.part_id, xli.qty} FROM x_lineitems xli, x_order xord); -- Triggers on orders and line_items fire, still as part of -- the transaction. INSERT INTO order_history (id, version_id, total_cost) SELECT o.id, o.version_id, o.total_cost FROM orders o WHERE o.id = ${order_id}; INSERT INTO line_items_history (id, version_id, order_id, order_version_id, part_id, part_version_id, qty) SELECT l.id, l.version_id, o.id, o.version_id, p.id, p.version_id, l.qty FROM line_items l JOIN orders o JOIN parts p WHERE l.order_id = ${order_id} AND o.id = ${order_id} AND p.id = l.part_id; -- And commit everything. If any of the parts rows we referenced -- change mid-transaction then everything gets rolled back. COMMIT;