and without creating a table to pass the state around (really just an excuse to use the named locks feature).
DELIMITER // DROP FUNCTION IF EXISTS SET_BREAKPOINT// CREATE FUNCTION SET_BREAKPOINT() RETURNS tinyint NO SQL BEGIN -- Acquire lock 1 -- Wait until lock 2 is taken to signal that we may continue DO GET_LOCK(CONCAT('lock_1_', CONNECTION_ID()), -1); REPEAT DO 1; UNTIL IS_USED_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT; DO RELEASE_LOCK(CONCAT('lock_1_', CONNECTION_ID())); -- Acquire lock 3 to acknowledge message to continue. -- Wait for lock 2 to be released as signal of receipt. DO GET_LOCK(CONCAT('lock_3_', CONNECTION_ID()), -1); REPEAT DO 1; UNTIL IS_FREE_LOCK(CONCAT('lock_2_', CONNECTION_ID())) END REPEAT; DO RELEASE_LOCK(CONCAT('lock_3_', CONNECTION_ID())); RETURN 1; END// DROP FUNCTION IF EXISTS NEXT_BREAKPOINT// CREATE FUNCTION NEXT_BREAKPOINT(connection_id int) RETURNS tinyint NO SQL BEGIN -- Acquire lock 2 as a signal to go past the breakpoint -- Wait until lock 3 is taken as signal of receipt. DO GET_LOCK(CONCAT('lock_2_', connection_id), -1); REPEAT DO 1; UNTIL IS_USED_LOCK(CONCAT('lock_3_', connection_id)) END REPEAT; DO RELEASE_LOCK(CONCAT('lock_2_', connection_id)); RETURN 1; END// DROP PROCEDURE IF EXISTS test_the_breakpoints// CREATE PROCEDURE test_the_breakpoints() NO SQL BEGIN SELECT CONCAT('In another session: DO NEXT_BREAKPOINT(', CONNECTION_ID(), ');') as `instructions`; DO SET_BREAKPOINT(); SELECT 'do it again' as `now:`; DO SET_BREAKPOINT(); SELECT 'end' as `the`; END// DELIMITER ; CALL test_the_breakpoints();