The Ultimate Guide to High Availability with MariaDB

Comments - Wait for changes on a table

5 months, 4 weeks ago Markus Mäkelä
Re: Wait for changes on a table [Answer]

Like is mentioned in MDEV-16590, this can be emulated in SQL. Here's a quick little prototype of a pair of stored procedures and a table that can be used to send and wait for signals. Adding signal IDs and versions for notify_one/notify_all should be possible.

delimiter $$ ;
CREATE OR REPLACE PROCEDURE wait_for_signal(IN timeout INT)
BEGIN
    INSERT INTO signal_waiters VALUES (CONNECTION_ID());
    EXECUTE IMMEDIATE CONCAT('SELECT ''wait_for_signal'', SLEEP (', timeout, ')');
    DELETE FROM signal_waiters WHERE id = CONNECTION_ID();
END $$

CREATE OR REPLACE PROCEDURE send_signal()
BEGIN
    START TRANSACTION;
    FOR rec IN (SELECT id FROM signal_waiters FOR UPDATE)
    DO
      EXECUTE IMMEDIATE CONCAT('KILL QUERY ', rec.id);
      DELETE FROM signal_waiters WHERE id = rec.id;
    END FOR;
    COMMIT;
END $$
delimiter ; $$

CREATE OR REPLACE TABLE signal_waiters(id BIGINT PRIMARY KEY);

-- To wait for a signal
CALL wait_for_signal(10);

-- To send a signal
CALL send_signal();
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.