install/update-07.sql

   1 DELIMITER $$
   2 DROP PROCEDURE IF EXISTS update_toplist $$
   3 CREATE PROCEDURE update_toplist()
   4 BEGIN
   5     DECLARE deadlock INT DEFAULT 0;
   6         DECLARE attempts INT DEFAULT 0;
   7
   8     DROP TABLE IF EXISTS toplist_temp;
   9     CREATE TABLE toplist_temp (
  10             podcast_id INT PRIMARY KEY REFERENCES podcast (id),
  11             subscription_count INT NOT NULL DEFAULT 0,
  12             INDEX(podcast_id)
  13     );
  14
  15     try_loop:WHILE (attempts<3) DO
  16     BEGIN
  17         DECLARE deadlock_detected CONDITION FOR 1213;
  18             DECLARE EXIT HANDLER FOR deadlock_detected
  19                 BEGIN
  20                     ROLLBACK;
  21                     SET deadlock=1;
  22                 END;
  23             SET deadlock=0;
  24
  25             START TRANSACTION;
  26             DELETE FROM toplist_temp;
  27             INSERT INTO toplist_temp (SELECT a.podcast_id, COUNT(*) AS count_subscription
  28                         FROM (SELECT DISTINCT podcast_id, user_id
  29                             FROM public_subscription) a
  30                         GROUP BY podcast_id);
  31             DELETE FROM toplist;
  32             INSERT INTO toplist (podcast_id, subscription_count, id) (SELECT podcast_id, subscription_count, NULL FROM toplist_temp
  33                         ORDER BY subscription_count DESC LIMIT 100);
  34
  35             COMMIT;
  36         END;
  37         IF deadlock=0 THEN
  38                 LEAVE try_loop;
  39             ELSE
  40                 SET attempts=attempts+1;
  41             END IF;
  42             END WHILE try_loop;
  43
  44         IF deadlock=1 THEN
  45             call FAIL('Toplist is not updated!');
  46         END IF;
  47         DROP TABLE IF EXISTS toplist_temp;
  48
  49 END $$
  50 DELIMITER ;
  51