[BE] [Bug]: User deleting their account causes database locking #3649

Requirements

• [X] Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
• [X] Did you check to see if this issue already exists?
• [X] Is this only a single bug? Do not put multiple bugs in one issue.
• [X] Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Summary

We have a user with a few hundred comments who has crashed the site twice today while trying to delete his account.

This query ends up running for a long time and locks subsequent updates to comment:

UPDATE "comment" SET "content" = $1, "deleted" = $2, "updated" = $3 WHERE ("comment"."creator_id" = $4) RETURNING "comment"."id", "comment"."creator_id", "comment"."post_id", "comment"."content", "comment"."removed", "comment"."published", "comment"."updated", "comment"."deleted", "comment"."ap_id", "comment"."local", "comment"."path", "comment"."distinguished", "comment"."language_id"

This was running for 8 minutes before I killed it. The user in question has 352 comments and 3073 entries in comment_like. This doesn't seem like such a large amount that there should be significant impact from a user deletion.

Steps to Reproduce

I haven't been able to reproduce this with a test user, so far only this one external user keeps causing it on our site.

I've had to disable the /api/v3/user/delete_account URL for now.

Technical Details

Logs are too noisy but this is triggered by a post to /api/v3/user/delete_account from Jerboa

Version

0.18.2

Lemmy Instance URL

lemmy.ca