Remove whole table locks on push rule add/delete #16051

Fizzadar · 2023-08-02T13:05:28Z

The statements are already executed within a transaction thus a table level lock is unnecessary.

See: #16053

Signed-off-by: Nick @ Beeper (@Fizzadar)

Pull Request Checklist

Pull request is based on the develop branch
Pull request includes a changelog file.
Pull request includes a sign off
Code style is correct
(run the linters)

The statements are already executed within a transaction thus a table level lock is unnecessary.

clokep · 2023-08-03T12:57:47Z

For reference, these were added in #578.

clokep · 2023-08-03T13:16:08Z

synapse/storage/databases/main/push_rule.py

-        # Lock the table since otherwise we'll have annoying races between the
-        # SELECT here and the UPSERT below.
-        self.database_engine.lock_table(txn, "push_rules")


I wanted to describe the race to ensure we have a shared understanding. Given two rules rule_A and rule_B with priorities 0 and 1, respectively. If you make two requests:

Add a new push rule (rule_X) after rule_A, this should make rule_B a priority of 2; rule_X priority 1; and leave rule_A at 0.

Add a new push rule (rule_Y) after rule_A, this should make rule_B a priority of 3; rule_X priority 2; rule_Y priority 1; and leave rule_A at 0.

This is in a transaction (I assume running at READ COMMITTED), so what happens if these race?

I believe the winner will be applied and the second transaction will be replayed using the new updated data, per (added some breaks to make it easier to read, my brain hurts!):

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress).

If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits .... it will attempt to apply its operation to the updated version of the row.

The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

If my understanding is correct READ COMMITTED will effectively correct the issue by the replay.

That said! Synapse's default is actually one better, REPEATABLE READ, in which case things are much simpler:

UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit ... if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update

Which synapse automatically retries, which would replay the transaction as expected.

Final note - there is an issue somewhere about switching to READ COMMITTED as the default, but it seems that would also suffice here in terms of the potential race conditions.

Thanks! I believe that you're correct (but my brain also hurts)!

I'm not sure I agree here. As things stand I don't see why we'd necessarily replay the transactions, as we may not have updated/deleted/locked any of the rows we SELECT against (and inserting a new row that would have been picked up by a SELECT isn't picked up by postgres except in SERIALIZABLE isolation AIUI).

I think what you want here is to run the selects with a FOR SHARE so that they do conflict with each other?

I guess most of the time the UPDATE will conflict, but if we have two requests to add a rule to the top of the push rules those transactions should conflict but won't?

Yeah that makes sense, will add FOR SHARE in 👍

Wait, we probably need a FOR UPDATE instead as we need the SELECT statements to conflict with each other and FOR SHARE won't do that https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS

erikjohnston

I think this needs a FOR SHARE adding to the selects?

Fizzadar · 2023-08-11T17:24:45Z

I think this is now correct; couple of sytest fails but they look unrelated?

erikjohnston · 2023-08-15T09:24:24Z

synapse/storage/databases/main/push_rule.py

+                SELECT * FROM push_rules
+                WHERE user_name = ? and priority_class = ?
+                FOR SHARE
+            """


Might be worth doing a txn.execute for this SQL? 😆

But also, you probably want to do this as part of the COUNT(*) query below?

Yep good spot - 2ec17da

Still missing a tx.execute here?

Gah, missed after rewriting it again in 2ec17da; fix: 376313e

erikjohnston · 2023-08-15T09:28:27Z

synapse/storage/databases/main/push_rule.py

-        # Lock the table since otherwise we'll have annoying races between the
-        # SELECT here and the UPSERT below.
-        self.database_engine.lock_table(txn, "push_rules")


Wait, we probably need a FOR UPDATE instead as we need the SELECT statements to conflict with each other and FOR SHARE won't do that https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS

Fizzadar · 2023-11-05T11:12:37Z

Took a while, finally got round to fixing this up!

…ery" This reverts commit 2ec17da. # Conflicts: # synapse/storage/databases/main/push_rule.py

erikjohnston

Thanks!

@Fizzadar can you sign off please?

Fizzadar · 2023-11-13T13:32:44Z

@erikjohnston updated PR comment, I think I had the wrong format for the signoff check!

erikjohnston · 2023-11-13T13:39:16Z

@erikjohnston updated PR comment, I think I had the wrong format for the signoff check!

Oh, heh, I checked the comment and just didn't see it!

erikjohnston · 2023-11-13T13:41:40Z

@Fizzadar Oh, there appears to be merge conflicts somehow? Can you merge in develop?

# Conflicts: # synapse/storage/databases/main/push_rule.py

Fizzadar · 2023-11-13T13:57:24Z

@erikjohnston merged!

Remove whole table locks on push rule add/delete

32e0bd3

The statements are already executed within a transaction thus a table level lock is unnecessary.

Fizzadar mentioned this pull request Aug 2, 2023

Parallel push rule deletes causing deadlock on main process #16053

Open

Add changelog

0938542

Fizzadar marked this pull request as ready for review August 2, 2023 14:27

Fizzadar requested a review from a team as a code owner August 2, 2023 14:27

clokep reviewed Aug 3, 2023

View reviewed changes

clokep requested a review from a team August 3, 2023 17:42

erikjohnston reviewed Aug 11, 2023

View reviewed changes

Fizzadar added 2 commits August 11, 2023 14:16

Use FOR SHARE to lock selected push rule rows on relative update

ab88d3f

Fix FOR SHARE handling of push rule transactions

d90cef1

Fizzadar requested a review from erikjohnston August 11, 2023 17:24

erikjohnston reviewed Aug 15, 2023

View reviewed changes

clokep added the X-Awaiting-Changes A contributed PR which needs changes and re-review before it can be merged label Sep 25, 2023

Fizzadar added 2 commits November 5, 2023 11:11

Use FOR UPDATE to ensure selects conflict

4dbee68

Rewrite highest priority rule txn to use FOR UPDATE in one query

2ec17da

Fizzadar requested review from clokep and erikjohnston November 5, 2023 11:12

Fizzadar added 4 commits November 5, 2023 11:13

Merge branch 'develop' into remove-push-rule-table-locks

3282065

Remove leftover quote

7d10544

Revert "Rewrite highest priority rule txn to use FOR UPDATE in one qu…

576605e

…ery" This reverts commit 2ec17da. # Conflicts: # synapse/storage/databases/main/push_rule.py

Add missing txn.execute on FOR UPDATE statement

376313e

erikjohnston approved these changes Nov 13, 2023

View reviewed changes

clokep removed their request for review November 13, 2023 13:54

Merge branch 'develop' into remove-push-rule-table-locks

7937ac0

# Conflicts: # synapse/storage/databases/main/push_rule.py

erikjohnston merged commit 0e36a57 into matrix-org:develop Nov 13, 2023
38 checks passed

Fizzadar deleted the remove-push-rule-table-locks branch November 13, 2023 18:21

matrixbot mentioned this pull request Dec 22, 2023

Parallel push rule deletes causing deadlock on main process element-hq/synapse#16053

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Remove whole table locks on push rule add/delete #16051

Remove whole table locks on push rule add/delete #16051

Fizzadar commented Aug 2, 2023 •

edited

Loading

clokep commented Aug 3, 2023

clokep Aug 3, 2023

Fizzadar Aug 3, 2023

Fizzadar Aug 3, 2023 •

edited

Loading

Fizzadar Aug 3, 2023

clokep Aug 3, 2023

erikjohnston Aug 11, 2023

erikjohnston Aug 11, 2023

Fizzadar Aug 11, 2023

erikjohnston Aug 15, 2023

erikjohnston left a comment

Fizzadar commented Aug 11, 2023

erikjohnston Aug 15, 2023

erikjohnston Aug 15, 2023

Fizzadar Nov 5, 2023

erikjohnston Nov 8, 2023

Fizzadar Nov 8, 2023

erikjohnston Aug 15, 2023

Fizzadar commented Nov 5, 2023

erikjohnston left a comment

Fizzadar commented Nov 13, 2023

erikjohnston commented Nov 13, 2023

erikjohnston commented Nov 13, 2023

Fizzadar commented Nov 13, 2023

Remove whole table locks on push rule add/delete #16051

Remove whole table locks on push rule add/delete #16051

Conversation

Fizzadar commented Aug 2, 2023 • edited Loading

Pull Request Checklist

clokep commented Aug 3, 2023

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Fizzadar Aug 3, 2023 • edited Loading

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

erikjohnston left a comment

Choose a reason for hiding this comment

Fizzadar commented Aug 11, 2023

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Fizzadar commented Nov 5, 2023

erikjohnston left a comment

Choose a reason for hiding this comment

Fizzadar commented Nov 13, 2023

erikjohnston commented Nov 13, 2023

erikjohnston commented Nov 13, 2023

Fizzadar commented Nov 13, 2023

Fizzadar commented Aug 2, 2023 •

edited

Loading

Fizzadar Aug 3, 2023 •

edited

Loading