CODEWITHSUNDEEP
databasepostgresqlconcurrencyrace-condition
socket_var
socket_var

Reputation: 1093

Resolve write after read race condition

Here is a race condition scenario that I need help with:

We are trying to create Item A and item B in the DB and we want to persist that item B is a "childOf" item A. Let's assume the request to create item B along with the "childOf" relationship data arrives before request to create item A.

here are the steps assuming a single job process these requests one at a time

If there are multiple jobs running concurrently, here is a race condition that can occur:

Process A Process B
req to create item B
check db for item A and item A is not found
req to create item A arrives and item A is created
since item A doesn't exist when it was checked last time, create a pending record in db with the relationship details
the pending record stays in the DB forever since item A is already created by process A while the pending record hasn't been inserted in the DB yet

How do I resolve this race condition? Sorry if this is too abstract. let me know if I should elaborate further.

Upvotes: 0

Views: 305

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246433

I would create a “pending” object not as a separate, different object, but in the very place where a “real” object would be, just with a flag to indicate its state.

CREATE TABLE object (
   name varchar(100)
      CONSTRAINT object_pkey PRIMATY KEY,
   pending boolean DEFAULT FALSE NOT NULL
);

Now I would add a regular object like this:

INSERT INTO object (name)
VALUES ('A')
ON CONFLICT ON CONSTRAINT object_pkey
DO UPDATE SET pending = FALSE;

A pending object is inserted like this:

INSERT INTO onject (name, pending)
VALUES ('A', TRUE)
ON CONFLICT ON CONSTRAINT object_pkey
DO NOTHING;

Then you don't need to check for existence before you add an object, and the race condition is gone. INSERT ... ON CONFLICT is designed to be atomic and free from race conditions.

Upvotes: 1

Related Questions