Freegle / Iznik / 23548

72%

Build Type

push

circleci

Committed by invalid-email-address

Commit Message

ripple: split lock-hogging messages_groups INSERT...SELECT + hard single-instance lock

The scoped ripple:expand experiment caused a prod-wide 1205 lock-wait storm and a
background_tasks backlog on 2026-06-26. Two root causes, both fixed here.

1) rippleIntoNewGroups ran one `INSERT IGNORE INTO messages_groups ... SELECT ...
   ST_Intersects(...) ... NOT EXISTS(logs ...)`. Under REPEATABLE READ an INSERT...SELECT
   takes shared next-key locks on EVERY source row it reads - the groups scan, the
   messages_groups dup check and the triple-nested `logs` "rippled-then-left" scan
   (100k-2.6M rows observed) - and holds them for the whole 5-430s statement. Run
   concurrently those locks collided on the messages_groups msgid index and on `logs`,
   starving the serial background worker's audit inserts (Sentry 1205 on INSERT INTO logs).
   Now resolve the target groups with a plain, non-locking snapshot SELECT, then INSERT each
   row on its own (Galera-safe, locks only the row written, briefly) - mirroring the existing
   addPosterMembershipToRippledGroups idiom. Behaviour is unchanged (same filters, collection,
   rippled_in); EXPLAIN confirms the existing logs(user)/(group) indexes are sufficient, so no
   new composite index is needed.

2) The scheduler's withoutOverlapping() is unreliable for runInBackground() jobs: the overlap
   mutex is freed as soon as the foreground tick forks, so a fresh run launched every minute
   even while the previous was still going (~90 piled up). ExpandCommand now takes a DB-backed,
   auto-expiring Cache lock ('ripple:expand:run') for the whole run and exits cleanly if another
   holds it, so at most one bulk run executes at a time. --msgid / --dry-run one-offs are exempt.

Verified live against prod (--within-group --limit 1/3): rippled_in inserts succeed with zero
lock waits and sub-second per-row inserts; new lock tests cover the single-instance guard.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.... (continued)

Coverage Stats

10741 of 12323 branches covered (87.16%)

Branch coverage included in aggregate %.

21 of 22 new or added lines in 2 files covered. (95.45%)

1384 existing lines in 43 files now uncovered.

123865 of 175661 relevant lines covered (70.51%)

36.9 hits per line