MySQL-在实时服务器上执行密集查询

CREATE DEFINER=`user`@`localhost` PROCEDURE `MyProcedure`(  
  totalLimit  int
)
BEGIN
  SET @totalLimit = totalLimit; 
  /* Prepare new rows to be issued */
  PREPARE STMT FROM \'UPDATE tableA SET `status` = \"Being-Issued\" WHERE `status` = \"Available\" LIMIT ?\';
  EXECUTE STMT USING @totalLimit;
  /* Insert new rows for usage into tableB */
  INSERT INTO tableB (/* my fields */)
    SELECT /* some values from TableA */ 
    FROM tableA
    WHERE `status` = \"Being-Issued\";
  /* Set rows as being issued */
  UPDATE tableB SET `status` = \'Issued\' WHERE `status` = \'Being-Issued\';
END$$

DELIMITER ;

create a temporary table

begin

insert into tmp_table
select your stuff
limit ?
for update

do your update on A using tmp_table

commit

begin
do your insert on B using tmp_table
do your update on A using tmp_table
commit

increment

totalLimit

sleepSec

BEGIN
SET @x = 0;
REPEAT
    SELECT SLEEP(sleepSec);
    SET @x = @x + increment;
    CALL OriginalProcedure( increment );

    UNTIL @x >= totalLimit
END REPEAT;
END$$