Freegle / Iznik / 25129

72%

Build Type

push

circleci

Committed by invalid-email-address

Commit Message

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

Coverage Stats

12392 of 16406 branches covered (75.53%)

Branch coverage included in aggregate %.

2 of 2 new or added lines in 1 file covered. (100.0%)

2 existing lines in 1 file now uncovered.

131631 of 184061 relevant lines covered (71.51%)

38.05 hits per line