Geek Answers Handbook

Updating an entire table without locking in MySQL

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

UPDATE `audio_clips` SET activity = activity * 0.95;

Is there a way to do this in packages (inside mysql) or execute a statement without blocking?

This field is used to show what is currently popular on the site (e.g. Reddit, Hacker News, etc.). Whenever an audio clip is played, the activity is clicked alone. On a regular basis, we "break up" the activity of each clip. I am not worried about updating atomically until each line breaks up.

+7
source share
2 answers

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

Good.

+1
source

I would definitely take a different approach.

It is not possible to set the timestamp when the clip will be played, and during rollover to calculate the decay from this timestamp? When you read statistics, you should accept activity minus decay since the last timestamp.

That way, you still only need one update per click.

To make it specific:

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

For decay of 10% per hour and relief 1 per view.

To view current statistics:

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

More articles:


All Articles