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

  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:

            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 complete=true, so jtype_one_active index 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.

  • The only logical explanation would be that the two transactions use using different types, so that they don't interfere with each other. Can you provide a repeatable test case with complete statements? Also, I don't understand the logic of your table. Why is the race condition a problem for you? – Laurenz Albe Jul 5 at 15:36
  • I'm not sure how to debug that from the postgres side. And it breaks business logic (completed job should not be restarted, no parallel jobs of same type) – dmzkrsk Jul 5 at 15:46
  • You can turn on statement logging. I still think that cannot happen unless the types differ. – Laurenz Albe Jul 5 at 16:10
  • I recorded a PostgreSQL log. types are the same. Now, the second transaction makes SELECT 5ms after the first transaction committed, but it looks like, it sees no committed data. Weird – dmzkrsk Jul 5 at 22:07
  • 1
    For some reason, after the A transaction commits, the B SELECT doesn't see committed data! It returns no rows: that's the expected behavior under the serializable isolation level. Process A: CREATE: create what? – Daniel Vérité Jul 7 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.