Does "Trigger Event" block the caller until the event has completed?

[former_SQLA_member1694882]

SQL Anywhere 11.0.1.2527

Does "Trigger Event" block the caller until the event has completed?

We're looking to call an event in an AFTER INSERT trigger which could potentially perform an expensive operation. So will the "Trigger Event" call block the insertion at all?

[Breck_Carter]

In addition to what Volker said: Using events to "fire and forget" time-consuming operations is an excellent way to increase concurrency; i.e., it's a good idea 🙂

[VolkerBarth · Accepted solution]

Events run on their own connection (and as a consequence, use separate transactions), so generally a TRIGGER EVENT does just that: It triggers the event and returns immediately.

Therefore, in general, both connections don't will block each other.

However, blocking might occur if the event's action would lead to locks on tables/rows that are accessed by the triggering connection, too. E.g. when the event would insert in the same table or update the same row as the trigger is declared for, then the usual "writers will block writers" problem might arise. But that will be dependent on exact timings (for example, whether the event's code starts before the AFTER TRIGGER is finished).

I guess your planned event is bound to read the row's contents after the insert statement has finished - then you have to make sure the isolation level does allow for that. If the transaction that has inserted is still not committed, the event (running in its own connection) might try to read a non-existing row when using a higher isolation level - or might read an uncommitted and later "rollbacked" change when using isolation level 0.

[former_SQLA_member1694882 · Accepted solution]

@Volker - Thanks for the answer. I was aware events ran in their own connection, but was just double-checking that there wasn't anything else that Trigger Event actually did that might cause the caller trigger to block.

[thomas_duemesnil]

Be aware that you can run into timing Problems if you insert the Rows Faster as your Event can finish his task.

[former_SQLA_member1694882]

@Thomas - Can you expand on this? I was under the impression that TRIGGER EVENT fired off a new Event every time it was called where every Event is running with its own connection. Assuming every Event is self-contained, how would there be timing issues? Of course we'd certainly code against any scenario that might end up firing Events at a rate where we end up with 100's or more running Events.

[thomas_duemesnil]

@Nick What you describe happen to me one time when the trigger had to update 200 rows after the insert of one row in a table. The updates the fired triggers needed to do had overlapping rows. Result was many trigger connection blocking each other.

[VolkerBarth]

@Nick, @Thomas: In case your event just has to notify another application, I would suggest to code the event that only one (or a few) instances of the event can run (i.e. do work) at the same time, and more instances would just start and close. "NumActive" is of help here - cf. sqla.stackexchange.com/questions/958. - Or you might turn to using STATEMENT level triggers, if that helps.