Under heavy load, we are experiencing deadlocks and lock wait timeouts within radioactivity_cron:

warning: Deadlock found when trying to get lock; try restarting transaction#012query: UPDATE radioactivity SET energy=energy * pow(2, (last_emission_timestamp*1.0-1349251314)/21600), last_emission_timestamp=1349251314 WHERE decay_profile=1 AND last_emission_timestamp<1349251314 in _db_query() (line 169 of .../www/includes/database.mysqli.inc).

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

pdrake’s picture

This patch adds the option to process the update query in a batch format where a limited number of rows are processed per update hook. This is important because the query requires a full table scan which will ultimately lock every record in the table and the update query can take an appreciable amount of time on a large table. Processing in batches, combined with the new index, requires a lock on only a few records at a time. On busy sites, the time taken while locking the table can result in deadlocks or lock wait timeouts. By default, this query will behave as it did before, unless radioactivity_decay_batch_size is set. Additionally, I have wrapped the entire function in a lock_acquire to ensure this function is not called multiple times concurrently as that is a sure-fire way to cause deadlocks.

bropp’s picture

Version: 6.x-1.x-dev » 7.x-2.x-dev
Issue summary: View changes
Status: Active » Needs work

Was getting this error in the 7.x branch as well, so rolled a patch based off of the one in #1.

It's not perfect and uses db_query because D7 doesn't support LIMIT in update queries. I haven't tested it on redis/memcache installs either. Otherwise, appears to be working.

bropp’s picture

Patch attached.