Is usage of Option blocking_others_timeout restricted ?

2

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

asked 18 Sep '14, 03:52

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k●28●38●64
accept rate: 17%

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

(18 Sep '14, 04:08) Volker Barth
Replies hidden

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.

(18 Sep '14, 04:44) Thomas Dueme...

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.

(18 Sep '14, 05:13) Volker Barth

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

(18 Sep '14, 08:33) Breck Carter
Replies hidden
1

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

(18 Sep '14, 08:55) Thomas Dueme...

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.

(18 Sep '14, 09:00) Thomas Dueme...

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

(18 Sep '14, 09:03) Volker Barth

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'
(18 Sep '14, 14:06) Breck Carter

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
(19 Sep '14, 03:57) Volker Barth
showing 2 of 9 show all flat view
One Answer:
active answersoldest answersnewest answerspopular answers
1

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

permanent link

answered 22 Sep '14, 16:04

Breck%20Carter's gravatar image

Breck Carter
32.5k●539●724●1050
accept rate: 20%

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

learn more about Markdown

Question tags:

×258
×41
×1

question asked: 18 Sep '14, 03:52

question was seen: 3,131 times

last updated: 26 Sep '14, 05:55

Related questions

Scheduled Event at last day of month skips 28. Feb. 2017

sa_make_object( 'event' ... ) drops the existing event

Allow SADIAGDIR to be set by DB server start-up parameters

dbvalid error -300 / index not match with table / rowcount

Spatial Data and ST_Transform

What can stop a database from being opened in read-only mode?

Is predicate push-down prevented for columns of a view used within a view using WINDOW functions?

Unable to obtain a SQL Anywhere 16 developer registration key

Unable to transform data from SRID 4326 to SRID 3347

LOOP - variable not found