alt Hacker NewsHere's a real-world example where atomic updates aren't an option - an order status transition that reads the current status from one table, validates the transition, and inserts into another:
await db().transaction(async (tx) => { await hooks?.onTxBegin?.();
const [order] = await tx.select().from(orders)
.where(eq(orders.id, input.id))
.for("update");
const [status] = await tx.select().from(orderStatuses)
.where(eq(orderStatuses.orderId, input.id))
.orderBy(desc(orderStatuses.createdAt))
.limit(1);
if (input.status === status.code)
throw new Error("Status already set");
await tx.insert(orderStatuses).values({ ... });You need the transaction + SELECT FOR UPDATE because the validation depends on current state, and two concurrent requests could both pass the duplicate check. The hooks parameter is the barrier injection point from the article - that's how you test that the lock actually prevents the race.
Replies
Seems you could use a single SQL statement for that particular formulation. Something like this, using CTEs is possible, but alternately one can reformat them as subqueries. (note: not sure how the select of orders is intended to be used, so the below doesn't use it, but it does obtain it as an expression to be used)
WITH
o AS (
SELECT FROM orders
WHERE orders.id = $1
),
os AS (
SELECT FROM orderStatuses
WHERE orderStatuses.orderId = $1
ORDER BY DESC orderStatuses.createdAt
LIMIT 1
)
INSERT INTO orderStatuses ...
WHERE EXISTS (SELECT 1 FROM os WHERE os.code != $2)
RETURNING ...something including the status differ check...
The standard pattern to avoid select for update (which can cause poor performance under load) is to use optimistic concurrency control.
Add a numeric version column to the table being updated, read and increment it in the application layer and use the value you saw as part of the where clause in the update statement. If you see ‘0 rows updated’ it means you were beaten in a race and should replay the operation.