Updating a whole table without locking in MySQL

Question

Running this update statement will lock every row in the table for 5 seconds.

UPDATE `audio_clips` SET activity = activity * 0.95;

Is there a way to do it in batches (internally to mysql), or to perform the statement without locking?

This is a field used to show what is currently popular on the site (like Reddit, Hacker News, etc). Whenever an audio_clip is played, activity is bumped by one. On a regular basis, we 'decay' the activity of each clip. I'm not bothered about updating atomically, as long as every row gets decayed.

Answer 1

I would definitely take a different approach.

Wouldn't it be possible to set a timestamp as the clip is played, and while bumping calculate the decay since that timestamp? When reading out for statistics, you should take the activity minus the decay since the last timestamp.

This way you still need only one update per clip played.

To make it concrete:

UPDATE `audio_clips` SET `lastview`=UNIX_TIMESTAMP(),
                   `activity`=1+`activity`*POW(0.9,(UNIX_TIMESTAMP()-`lastview`)/3600)
               WHERE `clipid`=$clipid

For a decay of 10% per hour and a bump of 1 per view.

To view current stats:

SELECT *,`activity`*POW(0.9,(UNIX_TIMESTAMP()-`lastview`)/3600) AS `current_activity`
       FROM `audio_clips`

Answer 2

Ultimately, I ended up doing it in batches. It may not be perfectly scalable but in batches of 1000, each record is tied up for about 35ms. The whole process only takes 10 seconds of each hour.

Good enough.