Freegle / Iznik / 25129 / 25129

72%

master: 72%

push

circleci

fix(microvolunteering): bound mark-seen UPDATE to a recent window (stop users_notifications lock-wait storm)

markStaleReviewedNotificationsSeen ran `UPDATE users_notifications ... WHERE
type='Exhort' AND seen=0` (joined to microactions) with NO time bound, every 5
minutes (microvolunteering:notify). users_notifications has ~2.57M unseen
type='Exhort' rows and no index on `type`, so the statement scanned/locked a
huge range and held gap/next-key locks for its whole duration — blocking
concurrent Exhort inserts with 1205 lock-wait timeouts (~21 lost microvolunteering
notifications overnight 2026-07-06, via DeadlockRetryConnection's final re-throw)
and risking Galera on the large write.

Bound both the UPDATE and its dry-run count to the last 7 days. Reviews happen
within days of the notification, and 98%+ of the unseen backlog is >30 days old
(only ~11k in 7d, ~46k in 30d, vs 2.57M total). EXPLAIN now shows a
touser_2 (timestamp,seen,mailed) range scan (~30k rows) instead of a full scan —
short-held locks, Galera-safe write, contention gone. No new index needed on the
huge table.

Not addressed here: the 2.57M-row unseen-Exhort backlog itself (data hygiene,
separate + Galera-sensitive to purge).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01VqZianMqz6i4FKMdPgnvWM

575 of 2822 branches covered (20.38%)

Branch coverage included in aggregate %.

355 of 1300 relevant lines covered (27.31%)

13.17 hits per line