4

I'm using PostgreSQL with the following database schema:

CREATE TABLE plans (
    slug VARCHAR(500) PRIMARY KEY
);

CREATE TABLE users (
    id VARCHAR(16) PRIMARY KEY,
    org_id VARCHAR(16) NOT NULL
);

CREATE TABLE orgs (
    id VARCHAR(16) PRIMARY KEY,
    plan_slug VARCHAR(500) NOT NULL,
    last_write_at DOUBLE PRECISION
);

In my case, I want to write a query to update some org's plan_slug and protect it from other possible concurrent update. To do this, I use a SELECT in a subquery and a FOR UPDATE to lock the rows in a specific order to avoid deadlocks. Like the following query:

UPDATE orgs
SET plan_slug = 'plan_1'
WHERE id = ANY(
    SELECT subquery_orgs.id
    FROM orgs AS subquery_orgs
    JOIN users ON users.org_id = subquery_orgs.id
    WHERE users.id = ANY('{user_1, user_2, user_3}')
    ORDER BY subquery_orgs.id
    FOR UPDATE
);

I noticed that if this request takes a long time to run, there is a high chance of collision with another query that is trying to update the last_write_at of an org (an org that is already being updated by the first query).

As follows:

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1';

The query will succeed. But if I add the plan_slug in the WHERE clause of the query, it will always fail to update. Postgre return UPDATE 0.

The query is the following:

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1' 
AND plans.slug = orgs.plan_slug;

From what I understand the process should be:

  1. The first query execute. The plan_slug row is locked.
  2. The second query execute. It stops its execution and wait for the row to unlock.
  3. The first query finishes its update.
  4. The second query restart and re-evaluate the changes.
  5. The plan_slug has changed but the new one exist in the plans table, the second query should succeed.

Then why the query is failing to update when I add the plan_slug to the WHERE clause ?

3
  • 1
    Being there is no foreign key constraint from orgs.plan_slug to plans.slug, there is no guarantee that plans.slug = orgs.plan_slug matches anything. In fact, query #1 may have broken that relationship, thus why query #3 finds nothing to update.
    – dwhitemv
    Commented Oct 11, 2022 at 17:45
  • In fact in my case the plans.slug always match orgs.plan_slug. The slug is checked in the code to be an existing row in the plans table. Also I tried your solution by adding a foreign key constraint but unfortunately it did not solve the problem. Commented Oct 12, 2022 at 10:08
  • I can reproduce the behavior you've described by hand with two psql sessions. Now to figure out what exactly is happening.
    – dwhitemv
    Commented Oct 13, 2022 at 3:06

1 Answer 1

5

Related question

Problem

Your summation of the process is correct, until step 4. The query does not restart after the locks are taken; the modified row versions are read and replace the old row versions. Before the locks were taken, a table join had occurred, and the new row versions render the join nonsensical and the rows are discarded.

Here is my revised version of events:

  1. The first query starts execution. The rows in orgs are locked.

  2. The second query starts execution.

    a. UPDATE reads the old row versions from orgs and plans (ignoring #1, as it has not COMMIT yet).

    b. UPDATE performs the join on orgs and plans and qualifies orgs.id to build the target row list.

    c. UPDATE tries to lock the targeted rows FOR NO KEY SHARE and blocks on the first query's FOR UPDATE lock.

  3. The first query COMMIT.

  4. The second query unblocks and notices the rows it had targeted have changed.

    a. The new row versions are read and replace the existing row versions in the join.

    b. The conditions are re-evaluated, and now the join condition fails as orgs.plan_slug has changed and no longer matches the plans row it previously joined to. The rows are discarded.

  5. The second query finds no rows to target with UPDATE.

To show it graphically:

  • The second query starts with the join looking like:

    id plan_slug last_write_at slug
    user_1 plan_1 888 plan_1
  • First query completes, orgs.plan_slug changes from plan_1 to plan_2.

  • The second query resumes, reads the orgs row versions, and changes its join state to:

    id plan_slug last_write_at slug
    user_1 plan_2 888 plan_1
  • Second query now re-evaluates its WHERE conditions. Uh oh, plan_2 != plan_1, rows are discarded.

Solutions

  1. Don't use FROM plans in your UPDATE last_write_at.... To be honest, I don't understand what its purpose is in your query; the column in plans is not referenced in any meaningful way. I assume there was more going on there that was deleted for the purposes of simplifying the question. Perhaps you can update the question to elucidate why it is that way.
  2. Use isolation level REPEATABLE READ with your UPDATE last_write_at... transaction. At that level, instead of updating no rows, you get the error ERROR: could not serialize access due to concurrent update. You can abort and retry the transaction knowing it is getting fresh data.
  3. Use a previous SELECT .. FOR UPDATE in the transaction to lock rows before UPDATE. Then UPDATE never has to contend with rows changing out from under it. In PL/pgSQL, you can use PERFORM 1 ... FOR UPDATE instead to avoid outputting the rows. As an example:
    BEGIN;
    SELECT id FROM orgs WHERE id='org_1' FOR UPDATE;
    UPDATE orgs SET last_write_at = 999 FROM plans 
      WHERE orgs.id = 'org_1' AND plans.slug = orgs.plan_slug;
    COMMIT;
    

Credits

2
  • Thank you for this very clear explanation. I decided to go for the first solution. Actually, I already knew that this solution would work but I wanted to understand why. However, I couldn't get the third solution to work. Commented Oct 13, 2022 at 13:35
  • 1
    Added an example of the transaction in solution #3.
    – dwhitemv
    Commented Oct 15, 2022 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.