Long running user deactivations via admin API are not deduplicated and can starve DB connections

[Half-Shot]

https://matrix-org.github.io/synapse/latest/admin_api/user_admin_api.html#deactivate-account can be used to deactivate a users session. However, because it does (potentially) expensive things like deleting devices it can take a long time to run. For serious device hoarders, this can cause the request to time out.

The HTTP request will time out but the DB query does not, leading to the case where it's an easy footgun to retry a "failed" user deactivation and end up having multiple concurreent slow queries jamming up the database connection pool. Do it enough times, or to enough users and the main process will be starved out of connections.

Separately, it's probably not good that deactivations are taking minutes to run.

Example output:

SELECT left(query, 90) as query, state FROM pg_stat_activity WHERE state IS NOT NULL AND query LIKE 'DELETE%';

                                           query                                            | state
--------------------------------------------------------------------------------------------+--------
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM device_inbox WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3 | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM device_inbox WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9 | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM device_inbox WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6 | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE1','DEVICE2','DEVICE3','YI | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE44','DEVICE5','DEVICE6','IA | active
 DELETE FROM devices WHERE device_id = ANY(ARRAY['DEVICE7','DEVICE8','DEVICE9','OD | active

[clokep]

We should likely treat these as the v2 room purge API which are done in the background with a progress exposed.

[Half-Shot]

(I've put this as a S-Minor because I believe you have to be an admin to really trigger this one, and the workaround is "don't do it")

[erikjohnston]

I think the correct fix here is to fix #16098, which should make these queries cheap.

[reivilibre]

Hard to argue with 'just don't do it to yourself', but not all invocations of the API will be done manually with curl, some might be using a tool that uses e.g. the Rust SDK, which will automatically retry timed out requests (ask me how I know ;-)).