Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Testing Postgres race conditions with synchronization barriers

lirbank.com

Post reply on HN
Print view View original

1–10 of 60 posts

Re: Testing Postgres race conditions with synchronization barriers

#2

Post by haliliceylan »

Thats not postgresql problem, thats your code

IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;

Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js

Re: Testing Postgres race conditions with synchronization barriers

#3

Post by lirbank »

haliliceylan wrote: post #2

Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js

Fair point - atomic updates like SET salary = salary + 500 sidestep the race condition entirely for simple cases. The examples are intentionally simplified to isolate the concurrency behavior. The barrier pattern is more relevant when you have read-modify-write operations that involve application logic between the read and the write - those can't always collapse into a single UPDATE.

Re: Testing Postgres race conditions with synchronization barriers

#4

Post by lirbank »

haliliceylan wrote: post #2

Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js

Here'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.

Re: Testing Postgres race conditions with synchronization barriers

#5

Post by erpellan »

lirbank wrote: post #4
haliliceylan wrote: post #2

Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js

Here'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(…

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.

Re: Testing Postgres race conditions with synchronization barriers

#6

Post by lirbank »

erpellan wrote: post #5
lirbank wrote: post #4

Earlier quoted context omitted.

Here'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(…

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.

Good point. The barrier pattern from the article applies to both approaches - whether you're using pessimistic locks or optimistic version checks, it's good to verify that the concurrency handling actually works. Barriers let you test that your version check correctly rejects the stale update, the same way they test that your lock prevents the race.

Re: Testing Postgres race conditions with synchronization barriers

#7

Post by codys »

lirbank wrote: post #4
haliliceylan wrote: post #2

Thats not postgresql problem, thats your code IMHO you should never write code like that, you can either do UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; Or if its more complex just use STORED PROCEDURE, there is no point of using database if you gonna do all transactional things in js

Here'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(…

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...
Does something like this work with postgres's default behavior?

Re: Testing Postgres race conditions with synchronization barriers

#8

Post by scottlamb »

It'd be interesting to see a version of this that tries all the different interleavings of PostgreSQL operations between the two (or N) tasks. https://crates.io/crates/loom does something like this for Rust code that uses synchronization primitives.

Re: Testing Postgres race conditions with synchronization barriers

#10

Post by lirbank »

scottlamb wrote: post #8

It'd be interesting to see a version of this that tries all the different interleavings of PostgreSQL operations between the two (or N) tasks. https://crates.io/crates/loom does something like this for Rust code that uses synchronization primitives.

Interesting! The barrier approach is more targeted: you specify the exact interleaving you want to test rather than exploring all of them. Trade-off is you need to know which interleavings matter, but you get deterministic tests that run against a real database instead of a simulated runtime. Exploring exhaustive interleaving testing against a real Postgres instance could be a fun follow-up - I'd be curious if it's practical.
Post reply on HN