I have the need to update a table after a transaction is finished. To achieve this I use a EVENT that is started in a TRIGGER. To allow the transaction to finish I us WAITFOR DELAY inside the EVENT HANDLER. Some times it happened that the transaction in the EVENT caused a (dead) lock to the normal operations. To reduce the risk of a block I would like that the EVENT tasks don't wait forever to get a lock.
When I run
Probably I have missunderstood the concept here. Would be great if somebody can enlighten me. Thanks |
Just in case the thought got lost in the forest of comments... this I tested the behavior today with the latest EBF 16.0.0.2003. Same result. Probably somebody knows if this is a bug or not.
(26 Sep '14, 05:55)
Thomas Dueme...
|
The "blocking" option is set within the event, right?
IMVHO, the code seems reasonable.
Is the error also raised when the value of the "blocking_others_timeout" option is greater than that of "blocking_timeout"? (That's a very wild guess...)
Note, you might also use the "MESSAGE ... FOR CONNECTION x IMMEDIATE" / "WAIT FOR ... AFTER MESSAGE BREAK" statements to use some kind of inter-connection-synchronization... - That may prevent the need for (somewhat error-prone) "wait delay estimates"...
I have tested with 6000ms and got the same result.
The event is triggered from an AFTER UPDATE TRIGGER on a Table. I want the commit to finish before the statements in my trigger are executed. So I don't have the possibility to give a MESSAGE later on.
Hm, I guess I do not understand fully:
You want the event being executed (including a commit) before the trigger is finished (meaning, the statement that fires the trigger cannot be committed at that time)?
What I had thought of was (probably only useable for a statement-level trigger):
What version and build are you using?
Can you duplicate this symptom in a separate empty database?
I can't, with 12.0.1.3994 or 16.0.0.1915I currently use 16.0.0.1691 and i can reproduce this in a empty database. BTW i use a Authenticated License SET OPTION PUBLIC.DATABASE_AUTHENTICATION='...'
Sorry. I want the event to do its stuff a few seconds after the connection has finished the transaction and the changes are committed. The Event does a little housekeeping on the same table where the first connection did some updates on. As this is a longer running task i want the server to do it in the back.
Ah, I see - well, then there's apparently no need/sense in synchronizing the event's finishing with the trigger's progress...
I take it back, I wasn't looking in the console log for the error message. Even a cow knows to look there :)
It's not you, it's SQL Anywhere; here's a vestigial repro in 16.0.0.1915...
FWIW, this seems only invalid in events, the same code block does not raise an error when run as a normal code block or within a stored procedure (tested with 16.0.0.1915, too).
Setting the option back to its default seems also to be allowed in events (which does not help Thomas, obviously), i.e. the following runs ok, too:
leads to the following console output: