Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1).
I have a table
CREATE TABLE j ( id BIGSERIAL PRIMARY KEY NOT NULL, "type" VARCHAR(10) NOT NULL, complete BOOLEAN DEFAULT FALSE NOT NULL, started_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, error TEXT DEFAULT '' NOT NULL ); CREATE INDEX jtype ON j("type"); CREATE UNIQUE INDEX jtype_one_active ON j("type") WHERE complete = false;
to track some jobs
The job is created with
complete=false, and later updated to
complete=true. Errors are logged, so there are multiple rows per "type", but only one job should be active.
First, with a naive approach, I did that:
SELECT j.* FROM j WHERE "type" = ... ORDER BY started_at DESC LIMIT 1 -- some in app business logic INSERT INTO j ("type") VALUES ($1) RETURNING *
It worked ok, but running parallel test I found a race condition on empty table:
- Process A: SELECT ...
- Process B: SELECT ... (both select no rows)
- Process A: INSERT / UPDATE (process A inserts a new row AND proceeds to update it to
jtype_one_activeindex won't fire)
- Process B: INSERT ...
So the job is running twice, even if process A completes with no error.
I wrapped CREATE + INSERT into a single Serializable transaction (also, added
FOR UPDATE to CREATE. And it worked for the most part.
The real question starts here:
But when I added more concurrency to my test I found, that sometimes the job is still running twice.
Here's what I managed to debug, but it might not be 100% true:
The table is empty
- Process A: begins transaction
- Process A: SELECT (no rows)
- Process A: CREATE
- Process B: begins transaction
- Process B: runs SELECT and select is running slow due high concurrency
- Process A: commits transaction
- Process B: SELECT completes, no rows is found
- Process B: INSERT (no serialisable error is fired!!)
- Process B: commits transaction (no error is fired!!)
Does a long running SELECT FOR UPDATE query somehow "escapes" Serializable Isolation? Or there's another flow in my script?
I've managed to get the same behaviour on just 2 parallel processes and record a PostgreSQL log: https://pastebin.com/HgzwC9Wh
Log looks close to my previous guess, though I'm not sure, if
SELECT in process B really takes a lot of time.
For some reason, after the A transaction commits, the B
SELECT doesn't see committed data! It returns no rows.
I tried to replay this from psql shell with no success.