How To Make Sure Your DatabaseStart Event Finishes First

If you have scheduled events in your database, and those events contain logic that depends on the DatabaseStart event finishing beforehand, it's up to you to make sure your scheduled events don't start running too early.

For example, let's say you have critical initialization logic in your DatabaseStart event, and your scheduled events contain other logic that assumes the initialization process is complete before they run. Since every event runs on its own connection, and connections run asynchronously, there's no guarantee that the DatabaseStart event will finish before your scheduled events start firing.

One strategy is to add more logic to each scheduled event to check if the DatabaseStart event has finished running, and to immediately RETURN if DatabaseStart has not finished.

Here's one way to implement that strategy:

• Add code to the end of the DatabaseStart event to record the CURRENT TIMESTAMP in a single-row table.
  

• Add code to the beginning of each scheduled event to check if the SQL Anywhere server startup timestamp is later than the timestamp recorded by the DatabaseStart event.
  

• If the server startup timestamp is later than the DatabaseStart timestamp, then RETURN from the scheduled event.
  

If the server startup timestamp is later than the DatabaseStart timestamp, then the DatabaseStart timestamp must have been recorded by a previous execution of the DatabaseStart event during a previous server startup... which in turn implies the DatabaseStart event for the current server startup hasn't finished executing.

Eventually, the DatabaseStart event will finish, and the next time the scheduled event fires it won't execute the RETURN.

Here are some excerpts from the Foxhound database monitor; first, a single-row table contains the DatabaseStart timestamp:

CREATE TABLE rroad_database_start_blocker (
   one_row_only  INTEGER NOT NULL DEFAULT 1 
                    CHECK ( one_row_only = 1 )  
                       PRIMARY KEY,
   started_at    TIMESTAMP NOT NULL ); 

CREATE TRIGGER trd_one_row_only BEFORE DELETE ON rroad_database_start_blocker
FOR EACH ROW
BEGIN
   ROLLBACK TRIGGER WITH RAISERROR 99999 'Do not attempt to delete from rroad_database_start_blocker';
END;

INSERT rroad_database_start_blocker VALUES ( DEFAULT, CURRENT TIMESTAMP );
COMMIT;

Second, the DatabaseStart event updates DatabaseStart timestamp after all the initialization logic is complete:

CREATE EVENT rroad_database_start TYPE DatabaseStart HANDLER
BEGIN

... critical initialization code ...

------------------------------------------------------------------------
-- Block other events from proceeding until this event finishes.

UPDATE rroad_database_start_blocker
   SET rroad_database_start_blocker.started_at = CURRENT TIMESTAMP
 WHERE rroad_database_start_blocker.one_row_only = 1;

COMMIT;

END;

Finally, a scheduled event starts by checking the server startup timestamp against the DatabaseStart timestamp:

CREATE EVENT rroad_monitor_auto_restart
SCHEDULE START TIME '00:00' EVERY 10 SECONDS
DISABLE
HANDLER BEGIN

-- Wait for the rroad_database_start event to finish before proceeding.

IF COALESCE ( PROPERTY ( 'StartTime' ), '' ) = '' THEN

   -- The SQL Anywhere engine hasn't yet filled in the StartTime property, so it can't be
   -- compared with rroad_database_start_blocker.started_at, so it's not safe to proceed
   -- with this event yet.

   RETURN; -- try again when this event fires again

END IF;

IF CAST ( PROPERTY ( 'StartTime' ) AS TIMESTAMP )
 > ( SELECT rroad_database_start_blocker.started_at 
       FROM rroad_database_start_blocker  
      WHERE rroad_database_start_blocker.one_row_only = 1 ) THEN

   -- The DatabaseStart event hasn't yet updated rroad_database_start_blocker.started_at so
   -- this event cannot proceed.

   RETURN; -- try again when this event fires again

END IF;

... other logic that depends on the initialization process being complete ...

END;

Note that PROPERTY ( 'StartTime' ) might not even be filled in by the time a scheduled event starts executing so there are two reasons to RETURN early.