Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 15 Feb '11, 01:29

Nick%20Brooks's gravatar image

Nick Brooks
668212537
accept rate: 33%

1

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 :)

(15 Feb '11, 15:47) Breck Carter

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.

permanent link

answered 15 Feb '11, 08:42

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

@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.

(15 Feb '11, 21:18) Nick Brooks

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

permanent link

answered 16 Feb '11, 11:45

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

@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.

(16 Feb '11, 21:46) Nick Brooks

@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.

(17 Feb '11, 11:10) Thomas Dueme...
Comment Text Removed
1

@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.

(17 Feb '11, 12:39) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×143
×79
×41

question asked: 15 Feb '11, 01:29

question was seen: 2,957 times

last updated: 16 Feb '11, 11:45