How to optimize a stored procedure?

Question

MySQL

When updating the online store, a temporary table is created, after entering the data into which, the stored procedure is executed:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sync_fl`()
BEGIN
  DROP TABLE IF EXISTS `sh_prod_im__`;
  ALTER TABLE `sh_prod_im` RENAME TO `sh_prod_im__`;
  ALTER TABLE `sh_prod_im_` RENAME TO `sh_prod_im`;
END$$
DELIMITER ;

The online store has a lot of visits per day and it looks like someone catches the moment of renaming the table and an error occurs (Table 'sh_prod_im' doesn't exist.), how can I optimize this process so that such problems do not arise? I read that the process of changing the name of the table takes 0.10 sec and this does not work.

Answer 1

Transactions won't help here, because DROP TABLE/ALTER TABLE causes an immediate COMMIT.
It remains only to change the table update procedure itself. For example like this:

BEGIN TRANSACTION;
UPDATE `sh_prod_im` SET `updated` = 0;

Then insert/update rows:

INSERT 
  INTO `sh_prod_im` (`article`, ..., `updated`) 
  VALUES (:article, ..., 1) 
  ON DUPLICATE KEY UPDATE ..., `updated` = 1;

And remove the non-updated ones:

DELETE FROM `sh_prod_im` WHERE `updated` = 0;
COMMIT;