Is usage of Option blocking_others_timeout restricted ?

[thomas_duemesnil]

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.

CREATE EVENT "DONT_BLOCK_EVENT" ENABLE AT ALL
HANDLER
BEGIN
    DECLARE @sqlcode        INTEGER;
    DECLARE @sqlstate       VARCHAR ( 5 );
    DECLARE @errormsg       VARCHAR ( 32767 );

    -- If we can't get a Lock wait maximum 1000ms
    SET TEMPORARY OPTION blocking_timeout = 1000;
    -- If this event blocks other connections we give up after 500ms
    SET TEMPORARY OPTION blocking_others_timeout = 500;

    if CAST( EVENT_PARAMETER('NumActive') as INTEGER ) = 1 then
        -- Hold the processing to give the calling worker the possibility to finish its task
        WAITFOR DELAY '00:00:05';

        // Do some Stuff here

    end if;

    EXCEPTION
        WHEN OTHERS THEN
             select string ( 'EXCEPTION: SQLCODE = ', SQLCODE,
                             ', SQLSTATE = ', SQLSTATE,
                             ', ERRORMSG() = ', ERRORMSG() ) into @errormsg;
            message @errormsg type info to console;
END;


When I run trigger event DONT_BLOCK_EVENT from iSQL the console I get 
EXCEPTION: SQLCODE = -201, SQLSTATE = 42W17, ERRORMSG() = Invalid setting for option 'blocking_others_timeout'


Probably I have missunderstood the concept here. Would be great if somebody can enlighten me.
Thanks 

[VolkerBarth]

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

[thomas_duemesnil]

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.

[VolkerBarth]

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

• Call the event from within the trigger and include the ConnId of the connection that runs the trigger as an event parameter.
• Within the trigger, use a loop with the WAITFOR DELAY ... AFTER MESSAGE BREAK statement.
• Within the event, "do the stuff" (including a commit) and then send a MESSAGE ... FOR CONNECTION <triggerconnid> IMMEDIATE to wake up the trigger connection.

[Breck_Carter]

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

[thomas_duemesnil]

I 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='...'

[thomas_duemesnil]

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.

[VolkerBarth]

Ah, I see - well, then there's apparently no need/sense in synchronizing the event's finishing with the trigger's progress...

[Breck_Carter]

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

CREATE EVENT e
HANDLER
BEGIN
    MESSAGE STRING ( 'blocking_others_timeout = ', 
       CONNECTION_PROPERTY ( 'blocking_others_timeout' ) ) TO CONSOLE;
    SET TEMPORARY OPTION blocking_others_timeout = 500;
END;
TRIGGER EVENT e;

blocking_others_timeout = 0
Handler for event 'e' caused SQLSTATE '42W17'
Invalid setting for option 'blocking_others_timeout'

[VolkerBarth]

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:

ALTER EVENT e
HANDLER
BEGIN
    SET TEMPORARY OPTION blocking_timeout = 500;
    SET TEMPORARY OPTION blocking_others_timeout = ;
    MESSAGE STRING ( 'blocking = ', 
       CONNECTION_PROPERTY ( 'blocking' ) ) TO CONSOLE;
    MESSAGE STRING ( 'blocking_timeout = ', 
       CONNECTION_PROPERTY ( 'blocking_timeout' ) ) TO CONSOLE;
    MESSAGE STRING ( 'blocking_others_timeout = ', 
       CONNECTION_PROPERTY ( 'blocking_others_timeout' ) ) TO CONSOLE;
END;
TRIGGER EVENT e;

leads to the following console output:

blocking = On
blocking_timeout = 500
blocking_others_timeout = 0

[Breck_Carter]

Just in case the thought got lost in the forest of comments... this is has gotta be a bug 🙂

[thomas_duemesnil]

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.