[Bug]: Optimize Deletion of Objects for Calendar Subscriptions

[kesselb]

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

[kuhball]

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.

[Olen]

Related but different root cause: DTSTAMP changes on every fetch

While investigating a bloated oc_calendarchanges table (2.8M rows, 200+ MB) on Nextcloud 32, I found a variant of this problem that affects stable-UID calendar subscriptions from major providers like Google Calendar, Outlook 365, and itslearning.

The problem: Per RFC 5545, the DTSTAMP property is set to the current UTC time when the iCal data is generated. This means it changes on every fetch, even for events that haven't been modified in years. Since the NC 30 delta sync in RefreshWebcalService compares fetched event data against the cached version, every event appears "modified" on every refresh — defeating the delta sync entirely.

Evidence from a Google Calendar subscription (2,621 events, Nextcloud 32.0.4):

-- All changes are operation=2 (modify), not add/delete — UIDs are stable
SELECT operation, count(*) FROM oc_calendarchanges
WHERE calendarid = 9 AND calendartype = 1 GROUP BY operation;
 operation |  cnt
-----------+--------
         1 |      1
         2 | 388796

-- Every event is "modified" ~149 times in 2 days (once per refresh)
SELECT uri, count(*) AS appearances FROM oc_calendarchanges
WHERE calendarid = 9 AND calendartype = 1 GROUP BY uri ORDER BY appearances DESC LIMIT 5;
                   uri                    | appearances
------------------------------------------+-------------
 a33d3d8c-9970-475a-888a-069910cdaae7.ics |         149
 5a380041-03ed-467a-a042-dbab316cc791.ics |         149
 ...

Actual event data showing the volatile DTSTAMP (event from 2013, unmodified):

BEGIN:VCALENDAR
BEGIN:VEVENT
DTSTART:20131208T090000Z
DTEND:20131208T113000Z
DTSTAMP:20260209T075647Z      <-- changes on every fetch
UID:p8fnnb6t47c5dbhd5mi3mf1ls4@google.com
CREATED:20131203T104125Z
LAST-MODIFIED:20131209T074642Z
...

Growth rate: ~2,621 events × ~72 refreshes/day = ~189K new rows/day in oc_calendarchanges, purely from DTSTAMP changes.

Affected providers (confirmed on this instance):

• Google Calendar (iCal feed) — 2,621 events, all marked modified every refresh
• Outlook 365 (iCal feed) — 101 events, same behavior
• itslearning (iCal feed) — 105 events, same behavior

Suggested fix: The comparison in RefreshWebcalService should strip or ignore DTSTAMP (and possibly other volatile-by-spec fields) before comparing fetched data against cached data. This would make the NC 30 delta sync actually effective for the vast majority of real-world iCal subscriptions.

This is a different codepath than the unstable-UID issue described in this ticket (no mass deletes/inserts, just unnecessary modify entries), but the impact on oc_calendarchanges bloat is the same.