search

progressivetech / net.ourpowerbase.sumfields

The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.
Other
8 stars 29 forks source link

Fix database locking during cron updates #106

Closed MegaphoneJon closed 5 months ago

MegaphoneJon commented 5 months ago

Hey Jamie,

I think we've discussed how I use cron-based Summary Field updates to avoid missed triggers, but that this prevents contributions from being made while calculations are happening. Since this can take a couple minutes, it breaks online donations during that time.

I've found a solution though - we can set the transaction isolation level to "READ UNCOMMITTED" while the SQL is creating the temp table. This reliably allows me to both update and create contributions.

Trying to think through edge cases - I suppose if someone registers for a paid event in the period where the SQL is running, then contribution and event totals may be mismatched until the next run. But that seems like a small price to pay for being able to register at all.

MegaphoneJon commented 5 months ago

Helpful context: https://stackoverflow.com/a/918092/2832108

jmcclelland commented 5 months ago

Thanks @MegaphoneJon - this is great. There's a small chance that someone configured to use triggers will get inaccurate results when they first initialize sumfields and they won't be corrected because they are using triggers. But, on the other hand, there's a small chance someone will experience a failed contribution when initializing sumfields without this patch, which seems worse than an inaccurate total.

I'm so tired of missed triggers that I'm ready to switch to cron as well - I'm curious what you have settled on for the timing of your cron job. Hourly? Daily? Do you still have sumfields to run on triggers, but re-generate regularly via a manual cron job? Maybe that's the best compromise.

MegaphoneJon commented 5 months ago

I've kept hourly updates on cron for smaller sites, with larger sites I've moved to daily.

I hadn't tested triggers + cron - if that works, then I'll probably move everyone over to daily cron updates.