Add locking to more safely delete state groups: Part 1 (#18107)

Currently we don't really have anything that stops us from deleting
state groups when an in-flight event references it. This is a fairly
rare race currently, but we want to be able to more aggressively delete
state groups so it is important to address this to ensure that the
database remains valid.

This implements the locking, but doesn't actually use it.

See the class docstring of the new data store for an explanation for how
this works.

---------

Co-authored-by: Devon Hudson <devon.dmytro@gmail.com>

2 files changed, 43 insertions, 1 deletions

diff --git a/synapse/storage/schema/__init__.py b/synapse/storage/schema/__init__.py

index 934e1ccced..49e648a92f 100644
--- a/synapse/storage/schema/__init__.py
+++ b/synapse/storage/schema/__init__.py
@@ -19,7 +19,7 @@
 #
 #
 
-SCHEMA_VERSION = 88  # remember to update the list below when updating
+SCHEMA_VERSION = 89  # remember to update the list below when updating
 """Represents the expectations made by the codebase about the database schema
 
 This should be incremented whenever the codebase changes its requirements on the
@@ -155,6 +155,9 @@ Changes in SCHEMA_VERSION = 88
       be posted in response to a resettable timeout or an on-demand action.
     - Add background update to fix data integrity issue in the
       `sliding_sync_membership_snapshots` -> `forgotten` column
+
+Changes in SCHEMA_VERSION = 89
+    - Add `state_groups_pending_deletion` and `state_groups_persisting` tables.
 """
 
 
diff --git a/synapse/storage/schema/state/delta/89/01_state_groups_deletion.sql b/synapse/storage/schema/state/delta/89/01_state_groups_deletion.sql
new file mode 100644

index 0000000000..d4cb27a3a2
--- /dev/null
+++ b/synapse/storage/schema/state/delta/89/01_state_groups_deletion.sql
@@ -0,0 +1,39 @@
+--
+-- This file is licensed under the Affero General Public License (AGPL) version 3.
+--
+-- Copyright (C) 2025 New Vector, Ltd
+--
+-- This program is free software: you can redistribute it and/or modify
+-- it under the terms of the GNU Affero General Public License as
+-- published by the Free Software Foundation, either version 3 of the
+-- License, or (at your option) any later version.
+--
+-- See the GNU Affero General Public License for more details:
+-- <https://www.gnu.org/licenses/agpl-3.0.html>.
+
+-- See the `StateDeletionDataStore` for details of these tables.
+
+-- We add state groups to this table when we want to later delete them. The
+-- `insertion_ts` column indicates when the state group was proposed for
+-- deletion (rather than when it should be deleted).
+CREATE TABLE IF NOT EXISTS state_groups_pending_deletion (
+    sequence_number $%AUTO_INCREMENT_PRIMARY_KEY%$,
+    state_group BIGINT NOT NULL,
+    insertion_ts BIGINT NOT NULL
+);
+
+CREATE UNIQUE INDEX state_groups_pending_deletion_state_group ON state_groups_pending_deletion(state_group);
+CREATE INDEX state_groups_pending_deletion_insertion_ts ON state_groups_pending_deletion(insertion_ts);
+
+
+-- Holds the state groups the worker is currently persisting.
+--
+-- The `sequence_number` column of the `state_groups_pending_deletion` table
+-- *must* be updated whenever a state group may have become referenced.
+CREATE TABLE IF NOT EXISTS state_groups_persisting (
+    state_group BIGINT NOT NULL,
+    instance_name TEXT NOT NULL,
+    PRIMARY KEY (state_group, instance_name)
+);
+
+CREATE INDEX state_groups_persisting_instance_name ON state_groups_persisting(instance_name);