Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

Reduce DB contention on worker locks #16160

Merged
merged 3 commits into from
Aug 23, 2023
Merged

Conversation

erikjohnston
Copy link
Member

@erikjohnston erikjohnston commented Aug 22, 2023

We used to upsert the token every time we took out the lock. Instead, we only update an existing value when we have a conflicting write_lock value (which will then fail the foreign key constraint).

@erikjohnston erikjohnston marked this pull request as ready for review August 22, 2023 16:37
@erikjohnston erikjohnston requested a review from a team as a code owner August 22, 2023 16:37
VALUES (NEW.lock_name, NEW.lock_key, NEW.write_lock, NEW.token)
ON CONFLICT (lock_name, lock_key)
DO UPDATE SET write_lock = NEW.write_lock
WHERE OLD.write_lock != NEW.write_lock;
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I thought it worthwhile to check the semantics of the WHERE here to ensure they still create the row lock:

Only rows for which this expression returns true will be updated, although all rows will be locked when the ON CONFLICT DO UPDATE action is taken.
— Postgres manual

Seems fine then

@erikjohnston erikjohnston merged commit 7cd79ce into develop Aug 23, 2023
37 checks passed
@erikjohnston erikjohnston deleted the erikj/reduce_db_contention branch August 23, 2023 12:45
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

2 participants