Prevent race condition in Postgres-CodePudding

let's suppose that I have an endpoint /seen/{bigint_number}, which is about 10K concurrently visits that with a random bigint number.

the logic is simple. if the number is already stored in the database, it returns true, if the number has not been stored yet, it got stored and returns false.

the logic is first "select * from myTable where number = bigint_number" if found return true, else insert into the table.

the race condition is here when the same concurrent user has the same number.

how we can avoid this?

CodePudding user response：

I believe what you're looking for is UPSERT operation: https://wiki.postgresql.org/wiki/UPSERT

Why you're afraid of race condition? Databases have set of tools like transactions and locks to help you with these problems.

CodePudding user response：

You need a unique constraint on the number column.

Then you can proceed like this:

WITH x AS (
   INSERT INTO mytable (number) VALUES (12346)
   ON CONFLICT (number) DO NOTHING
   RETURNING number
)
SELECT count(number) = 0
FROM x;

The INSERT statement will return a row if a row was inserted, so the query will return FALSE in that case.

This is free from race conditions, because INSERT ... ON CONFLICT is.