3 ms·
No 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
by mgsouth 3y ago
No 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;