Deferred procedures in Advanced Replication configurations

The UNIQUE keyword before a statement defines that the statement is executed only if there is no identical statement being executed or pending for execution. Statements are compared with a simple string compare. So for example CALL foo(1) is different from CALL foo(2). Replicas are also taken into account in the comparison; in other words, the UNIQUE keyword does not prevent the server from executing the same trigger call on different replica databases. Note that the UNIQUE keyword only blocks the execution of overlapping statements; it does not prevent the same statement from being executed again if it is called after the current execution has completed.

-- To run this demo properly, you need two users/connections.
-- This demo contains 5 separate "scripts", which must be executed
-- in the following order:
-- User1 executes SCRIPT #1.
-- User2 executes SCRIPT #2.
-- User1 executes SCRIPT #3.
-- User2 executes SCRIPT #4.
-- User1 executes SCRIPT #5.
-- You might notice that there are some COMMIT WORK statements
-- in surprising places. These are to ensure that each user sees the
-- most recent changes of the other user. Without the COMMIT WORK
-- statements, in some cases one user would see an out-of-date
-- snapshot of the database.
--
-- Set autocommit off for both users/connections!

-- SCRIPT #1 (USER 1)
CREATE EVENT e (i int); CREATE TABLE table1 (a int);
-- This inserts a row into table1. The value inserted into the is copied
-- from the parameter to the procedure.
"CREATE PROCEDURE inserter(i integer)
BEGIN
  EXEC SQL PREPARE c_inserter INSERT INTO table1 (a) VALUES (?);
  EXEC SQL EXECUTE c_inserter USING (i);
  EXEC SQL CLOSE c_inserter;
  EXEC SQL DROP c_inserter;
END";
-- This posts the event named "e".
"CREATE PROCEDURE post_event(i integer)
BEGIN
  POST EVENT e(i);
END";
-- This demonstrates the use of START AFTER COMMIT inside a
-- stored procedure. After you call this procedure and
-- call COMMIT WORK, the server will post the event.
"CREATE PROCEDURE sac_demo
BEGIN
  DECLARE MyVar INT;
  MyVar := 97;
  EXEC SQL PREPARE c_sacdemo START AFTER COMMIT NONUNIQUE CALL
    post_event(?);
  EXEC SQL EXECUTE c_sacdemo USING (MyVar);
  EXEC SQL CLOSE c_sacdemo; EXEC SQL DROP c_sacdemo;
END";
-- When user2 calls this procedure, the procedure will wait until
-- the event named "e" is posted, and then it will call the
-- stored procedure that inserts a record into table1.
"CREATE PROCEDURE wait_on_event_e
BEGIN
-- Declare the variable that will be used to hold the event parameter.
-- Although the parameter was declared when the event was created, you
-- still need to declare it as a variable in the procedure that receives
-- that event.
DECLARE i INT;
WAIT EVENT
WHEN e (i) BEGIN
-- After we receive the event, insert a row into the table.
EXEC SQL PREPARE c_call_inserter CALL inserter(?);
EXEC SQL EXECUTE c_call_inserter USING (i);
EXEC SQL CLOSE c_call_inserter;
EXEC SQL DROP c_call_inserter;
END EVENT
END WAIT
END";
COMMIT WORK;

-- SCRIPT #2 (USER 2)
-- Make sure that User2 sees the changes that User1 made.
COMMIT WORK;
-- Wait until user1 posts the event.
CALL wait_on_event_e;
-- Do not commit work again (yet).

-- SCRIPT #3 (USER 1)
COMMIT WORK;
-- User2 should be waiting on event e, and should see the event after
-- we execute the stored procedure named sac_demo and then commit work.
-- Note that since START AFTER COMMIT statements are executed
-- asynchronously, there might be a slight delay between the COMMIT WORK
-- and the associated POST EVENT.
CALL sac_demo;
COMMIT WORK;

-- SCRIPT #4 (USER 2)
-- Commit the INSERT that User1 did when they called inserter() in SCRIPT #1
-- after receiving the event
COMMIT WORK;

-- SCRIPT #5 (USER 1)
-- Ensure that User1 sees the data that User2 inserted.
COMMIT WORK;
-- Show the record that User2 inserted.
SELECT * FROM table1;
COMMIT WORK;