nextcloud
[Bug]: Optimize Deletion of Objects for Calendar Subscriptions
Description:
The RefreshWebcalService is responsible for updating calendar subscriptions. Since Nextcloud 30, the updated source is diffed against the local state, and only changes are written back.
Recently, we investigated a system with an unusually large oc_calendarobjects table and recurring deadlocks when removing calendar objects that no longer exist in the source.
Findings:
It turned out that the user had subscribed to a calendar from a Redmine instance. This subscription changed the UID on every request and contained a rather large set of events.
As a result, every time the subscription was refreshed, around 15,000 objects needed to be removed. This process affects the following tables:
- oc_calendarobjects_props
- oc_calendarobjects
- oc_calendarchanges (twice)
Currently, these deletions are handled within a single transaction, significantly increasing the risk of deadlocks.
Suggested Improvement:
To reduce the likelihood of deadlocks, we should consider optimizing how deletions are handled—potentially by breaking them into smaller transactions or batching the deletions more efficiently.
Additional information
Script to simulate such a calendar feed: https://github.com/kesselb/weird-calendars/blob/main/calendar_subscription_with_unstable_uid.php
Hi, just ran into the same issue. The oc_calendarobjects_props table is currently 81GB large. There are 53 subscriptions of calendars with changing UIDs. Deletion of a subscribed calendar results in the following log message:
DbalException An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
Only found this by accident while trying to dump the database. Not sure how to delete the subscriptions properly. There is only a occ command for creating subscriptions.
