Concurrent update on two different tables fail

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:

• The first query execute. The plan_slug row is locked.

• The second query execute. It stops its execution and wait for the row to unlock.

• The first query finishes its update.

• The second query restart and re-evaluate the changes.

• 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 ?

Related question

• Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE

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:

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

-
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.

-
The first query COMMIT.

-
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.

-
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

• 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.

• 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.

• 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

• https://stackoverflow.com/a/11568880 - for the breakdown of the steps (not 100% applicable but enough to get an idea)

• https://stackoverflow.com/a/57865346 - mentions PERFORM 1 ...

StackExchange Database Administrators Q#318088, answer score: 5