latency in dropping event connection

[Baron]

Could someone explain why I get the error (even in my second approach):

Approach 1:

create or replace event myevent schedule start time '08:30' every 24 hours handler
begin
waitfor delay ('00:00:30');
end;
trigger event myevent;
begin
    execute immediate (' ' ||(select ' drop connection ' || Number from sa_conn_info() where "Name" = 'myevent'));
    --directly here is the connection still alive
    --the next line causes an error (Event 'myevent' in use)
    alter event myevent schedule START TIME '09:00' EVERY 24 HOURS enable;
end;

It is clear that dropping a connection could take some time, so I should wait for it.

Approach 2:

create or replace event myevent schedule start time '08:30' every 24 hours handler
begin
waitfor delay ('00:00:30');
end;
trigger event myevent;
begin
    for curs as mycursor1 cursor for
    select "Number" as EventConnID from sa_conn_info() where "Name" = 'myevent'
    do
        execute immediate ('drop connection ' || EventConnID);
    end for;
    --apparantly the connection is still alive, so I will check it again and go in delay until it permanently disappers
    for curs as mycursor2 cursor for
        select 1 from sa_conn_info() where "Name" = 'myevent'
        do
            waitfor delay ('00:00:02');
    end for;
        --even here I get sometimes the same error (Event 'myevent' in use)
    alter event myevent schedule START TIME '09:00' EVERY 24 HOURS enable;
end;

[VolkerBarth · Accepted solution]

Independent of the exact timing, I would never use an (infinite) loop with a query without a small delay aka WAITFOR between each run because otherwise you increase the database server's load instead of helping it to get things done... So I would suggest to add a WAITFOR within the inner loop, too.

2- Second delay between removing the connection from sa_conn_info() and real dropping of the connection (this is somehow confusing, and may be a small and fix latency. For this second delay I dont need apparently to go inside loop.

I would assume there's some housekeeping necessary within the database server to free resources used by the dropped connection, and if this is done within only one second (as your 4th approach suggests), I would not even bother...

[Baron · Accepted solution]

@Volker Barth.

Independent of the exact timing, I would never use an (infinite) loop with a query without a small delay aka WAITFOR between each run because otherwise you increase the database server's load instead of helping it to get things done... So I would suggest to add a WAITFOR within the inner loop, too.

With my second approach should this be correct?

I would assume there's some housekeeping necessary within the database server to free resources used by the dropped connection, and if this is done within only one second (as your 4th approach suggests), I would not even bother...

As long as the housekeeping is not queryable, I can only go with a fix delay, or have you another idea?

[VolkerBarth · Accepted solution]

The WHILE loop within your 4th approach will continuously repeat to query sa_conn_info() and to message without interruption until the connection isn't listed anymore in the sa_conn_info(). I.e. you keep the server busy while it tries to do the housekeeping. (If that is a real problem or not, I cannot tell, but I guess you get a lot of messages before the connection is really dropped. If there are only a few, then neverminde.)

As long as the housekeeping is not queryable, I can only go with a fix delay, or have you another idea?

I do think the fixed delay is your best bet here.

[chris_keating]

You will likely need to check that the connection has in fact gone away before ALTERing the event. I would also consider DISABLEing the event while this is occurring. Here is a modified approach:

begin
    declare connid integer;

    alter event myevent disable;  -- make sure that the event is not able to run

    -- get event conn and drop if running
    select Number into connid from sa_conn_info() where name = 'myevent';
    if( connid is not null ) then -- cannot assume event connection
        execute immediate ('drop connection ' || connid);
    end if;

    while 1=1 loop -- until event connection is stopped
       if not exists( select 1 from sa_conn_info() where name = 'myevent' ) then
            message 'Event connection is not active'; 
            leave;
        else
            message 'Event connection is still active';
        end if;
    end loop;

    alter event myevent schedule start time '09:00' every 24 hours enable;
end;

[Baron]

yes, I should disable the event, but in this exact case the event will not be triggered from itself.

Could you please explain the difference between my "mycursor2" loop and your 1=1 loop, I dont see actually any difference.

As mentioned, the error in my second approach happens not always.

[Baron]

@Chris Keating, BTW I managed to get the same error using your block.

[chris_keating]

I ran a stress on the snippet and was able to reproduce the same error. As you noted, there appears to be some delay or timing behaviour. I am not sure what to suggest other than retrying the ALTER until it succeeds.

[Baron]

I think there are 2 different delays:

1- First delay until the engine manages to drop the connection (this is logic and for this one I have to wait inside a loop). This first delay should be relative big and depends on how much is the event busy.

2- Second delay between removing the connection from sa_conn_info() and real dropping of the connection (this is somehow confusing, and may be a small and fix latency. For this second delay I dont need apparently to go inside loop.

The proof for my assumption is that my third approach does not work (always) but my fourth approach works (always).

Approach 3:

create or replace event myevent schedule start time '08:30' every 24 hours handler
begin
waitfor delay ('00:00:30');
end;
while 1=1 loop -- stress test
trigger event myevent;
begin
    declare connid integer;
    alter event myevent disable;
    select Number into connid from sa_conn_info() where name = 'myevent';
    if( connid is not null ) then
        execute immediate ('drop connection ' || connid);
    end if;
    while 1=1 loop
       if not exists( select 1 from sa_conn_info() where name = 'myevent' ) then
            message 'Event connection is not active'; 
            leave;
        else
            message 'Event connection is still active';
        end if;
    end loop;
    alter event myevent schedule start time '09:00' every 24 hours enable;
end;
end loop;

Approach 4:

create or replace event myevent schedule start time '08:30' every 24 hours handler
begin
waitfor delay ('00:00:30');
end;
while 1=1 loop -- stress test
trigger event myevent;
begin
    declare connid integer;
    alter event myevent disable;
    select Number into connid from sa_conn_info() where name = 'myevent';
    if( connid is not null ) then
        execute immediate ('drop connection ' || connid);
    end if;
    while 1=1 loop
       if not exists( select 1 from sa_conn_info() where name = 'myevent' ) then
            message 'Event connection is not active'; 
            leave;
        else
            message 'Event connection is still active';
        end if;
    end loop;
    --the next line makes difference
    waitfor delay('00:00:01');--delay between sa_conn_info() and actually dropping of the connection
    alter event myevent schedule start time '09:00' every 24 hours enable;
end;
end loop;

[awitter]

I don't think it is possible to stop the connection while it is waiting in a waitfor. To test this, you could better make a loop or something that prints to the console every second.

[Baron]

sure I can drop the connection, regardless what is currently doing. You can try it yourself.

My problem is that the connection seems to be gone (according to sa_conn_info()), but it is acutually still present.