DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;

USE `test`;

CREATE TABLE `tbl` Engine=innodb AS SELECT 1 AS `id`;

DELIMITER $$

CREATE PROCEDURE `sp_test` ()
BEGIN
	
    START TRANSACTION;

        DROP TEMPORARY TABLE IF EXISTS `tmp`;
        CREATE TEMPORARY TABLE `tmp` (
              `id`       INT UNSIGNED NOT NULL
        ) Engine=innodb AS 
		    SELECT `tbl`.`id` FROM `tbl` FOR UPDATE;
         
		  -- do some complex task
        SELECT SLEEP (1);
          
        SELECT `tbl`.`id`
        FROM `tmp`
        INNER JOIN `tbl` ON `tmp`.`id` = `tbl`.`id`;

    COMMIT;

END $$

DELIMITER ;