The following example uses two SQL scripts to show how to use events.
Script 1 waits on an event and Script 2 posts the event. After the event is posted, the Script 1 moves on to the next statement.
To execute this example code, you need two consoles so that you can start the WaitOnEvent.sql script on one console and then run the PostEvent.sql script on the other console while the WaitOnEvent.sql script is waiting.
In this example, the stored procedure that waits does not actually do anything after the event has posted; the script merely finishes the wait and returns to the caller. The caller can then proceed, which in this case is to SELECT the record that was inserted while the stored procedure was waiting.
This example waits for only a single event, which is called record_was_inserted.
============================= SCRIPT 1============================= -- SCRIPT NAME: WaitOnEvent.sql -- PURPOSE: -- This is one of a set of scripts that demonstrates posting events -- and waiting on events. The following pseudocode shows the -- sequence of steps: -- -- THIS SCRIPT (WaitOnEvent.sql) PostEvent.sql script -- -- CREATE EVENT. -- CREATE TABLE. -- WAIT ON EVENT. -- Insert a record into table. -- Post event. -- SELECT * FROM TABLE. -- -- To perform these steps in the proper order, start running this -- script FIRST, but remember that this script does not finish running -- until after the post_event script runs and posts the event. -- Therefore, you will need two open consoles so that you can leave -- this running/waiting in one window while you run the other script -- post_event) in the other window. -- Create a simple event that has no parameters. -- Note that this event (like any event) does not have any -- commands or data; the event is just a label that allows both the -- posting process and the waiting process to identify which event has -- been posted (more than one event can be registered at a time). -- As part of our demonstration of events, this particular event -- will be posted by the other user after they inserted a record. CREATE EVENT record_was_inserted; -- Create a table that the other script will insert into. CREATE TABLE table1 (int_col INTEGER); -- Create a procedure that will wait on an event -- named "record_was_inserted". -- The other script (PostEvent.sql) will post this event. "CREATE PROCEDURE wait_for_event BEGIN -- If possible, avoid holding open a transaction. Note that in most -- cases it's better to do the COMMIT WORK before the procedure, -- not inside it. See "Waiting on Events" at the end of this example. EXEC SQL COMMIT WORK; -- Now wait for the event to be posted. WAIT EVENT WHEN record_was_inserted BEGIN -- In this demo, we simply fall through and return from the -- procedure call, and then we continue on to the next -- statement after the procedure call. END EVENT END WAIT; END"; -- Call the procedure to wait. Note that this script will not -- continue on to the next step (the SELECT) until after the -- event is posted. CALL wait_for_event(); COMMIT WORK; -- Display the record inserted by the other script. SELECT * FROM table1;
Guidelines for committing transaction in Script 1 (WaitOnEvent.sql)
Whenever possible, complete any current transaction before waiting on an event. If you execute a WAIT inside a transaction, then the transaction is held open until the event occurs and the next COMMIT or ROLLBACK statement is executed. This means that during the wait, the server holds locks, which can lead to excessive Bonsai tree growth. For details on the Bonsai tree and preventing its growth, see Reducing Bonsai tree size by committing transactions.
In this example, the COMMIT WORK statement is inside the procedure immediately before the WAIT EVENT clause. However, this is not usually a good solution; putting the COMMIT or ROLLBACK statement inside the “wait” procedure means that if the procedure is called as part of another transaction, then the COMMIT or ROLLBACK statement will terminate that enclosing transaction and start a new transaction, which is probably not what you want. If, for example, you were entering data into a "child" table with a referential constraint and you are waiting for the referenced data to be entered into the "parent" table, then breaking the transaction into two transactions would simply cause the insert of the "child" record to fail because the parent was not inserted yet.
The best strategy is to design your program so that you do not need to have a WAIT EVENT clause inside a transaction; instead, your "wait" procedure should be called between transactions if that is possible. By using events and waits, you have some control over the order in which things are done and you can use this to help ensure that dependencies are met without actually putting everything into a single transaction. For example, in an "asynchronous" situation you might be waiting for both a child and a parent record to be inserted, and if your database server did not have the "events" feature, then you might require both records to be inserted in the same transaction so that you could ensure referential integrity.
By using events and waits, you can ensure that the insertion of the parent record is done first; you can then put the insertion of the child record in a second transaction because you can guarantee that the parent will always be present when the child is inserted. To be more precise, you can ALMOST guarantee that the parent will be present when the child is inserted. If you break up the insertions into two different transactions then, even if you ensure that the parent is inserted before the child, there is a slight chance that the parent would be deleted before the program tried to insert the child record.
============================= SCRIPT 2============================= -- SCRIPT NAME: PostEvent.sql -- PURPOSE: -- This script is one of a set of scripts that demonstrates posting -- events and waiting on events. The following pseudocide shows -- the sequence of steps: -- -- WaitOnEvent.sql THIS SCRIPT (PostEvent.sql) -- -- Create event. -- Create table. -- Wait on event. -- INSERT A RECORD INTO TABLE. -- POST THE EVENT. -- Select * from table. -- Insert a record into the table. INSERT INTO table1 (int_col) VALUES (99); COMMIT WORK; -- Create a stored procedure to post the event. "CREATE PROCEDURE post_event BEGIN -- Post the event. POST EVENT record_was_inserted; END"; -- Call the procedure that posts the event. CALL post_event(); DROP PROCEDURE post_event; COMMIT WORK;