Potential for conflicting cascading deletes
Another day, another support post from erquhart 😅
I haven't tried this at scale, but wanted to see if you'd anticipate a problem:
I have reusable delete functions set up for my tables, and those functions handle any cascading deletes as well.
If I delete a row that cascades to delete all related rows from two other tables, and those two tables cascade deletes to a shared join table, can convex sort that out or is there potential for an endless loop due to transactionality?
Example:
- Rows from table A can have many of B and C
- B and C are many-to-many with one another
- D joins B and C
If I delete a row from A:
- All related B and C are scheduled for recursively paginated deletion
- Each B and C deletion also deletes all related D in the same transaction
- At some point, and probably often, a page of B and a page of C will attempt to delete a subset of shared D at the same time in two concurrent transactions
Is this pattern okay or do I need to conditionally cascade to avoid this? Hoping not to and that Convex just "keeps pulling the world forward", but wanted to see what you all think.
2 Replies
One transaction will succeed in deleting the D row, and the other transaction will not retrieve the D row, so won't have a pointer to the other table. As long as you're fetching D and handle there potentially not being any rows there for a given record in B / C you're fine. If you run into too much contention, you might restructure to have one mutation deleting at a time, scheduling the next batch (or next table to check if a batch is done)
Okay that makes sense. I'll see if an issue arises.