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();
via Planet MySQL
Breakpoints for stored procedures and functions