deadlock in delete_from_account about forget

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[codl]

hello. i think i've figured this out. it looks like postgres does not lock all the rows in a SELECT .. FOR UPDATE at once atomically. since we order rows by random(), this means one transaction might lock post A and then post B, while another locks post B and then post A

one way to fix this, since we only need a random sample of posts and don't necessarily need all 100 posts that were picked, would be to SELECT ... FOR UPDATE SKIP LOCKED which would just skip rows that were already locked, and avoid a deadlock

however i think there is a larger problem here. it seems unwise to lock a hundred rows and then go off making potentially slow http requests, up to 101 of them in mastodon's case

[codl]

btw the reason why we lock those rows FOR UPDATE is because of an other similar deadlock that would happen without that explicit lock. not sure why that one would happen but it is likely that it was because of locking in random order as well

06bf189