call procedures wait for each other

[Former Member]

When I call the following procedure from 2 different connections in ISQL, one waits for another for completion before returning to isql. This was tried on ASA 10, 11, 12.

Create the following procedure.

create procedure CSEnter() 
begin
  declare i int;

  message 'enter scroll for ' to client;
  set i = 0;

  while i < 3 loop
    message 'begin while ' to client;
    waitfor delay '0:0:10';
    message 'end while ' to client;

    set i = i + 1;
  end loop;

  message 'leave scroll for ' to client;
end;

1. Start ISQL and connect to database;
2. Start another ISQL and connect to datbase;
3. In first ISQL execute call csenter();
4. Wait for about 10s
5. In second ISQL execute call csenter();
6. In first ISQL in message window you'll see the message 'leave scroll for ' and ISQL will wait till second finishes, can someone explain why???

[Breck_Carter]

The delay is shown by the "Execution time: 42.209 seconds" in the first ISQL window, but only 30.126 seconds for the second:

enter scroll for 
begin while 
end while 
begin while 
end while 
begin while 
end while 
leave scroll for 
Execution time: 42.209 seconds
Procedure completed

enter scroll for 
begin while 
end while 
begin while 
end while 
begin while 
end while 
leave scroll for 
Execution time: 30.126 seconds
Procedure completed

I'm guessing it has something to do with the ISQL client program, not the SQL Anywhere server.

[jan24]

I tested (ASA11 and ISQLc) your interesting submission, but the result is almost exactly the same.
I modified the procedure slightly.

if exists (select 1 from sysprocedure where proc_name='CSEnter') then drop procedure CSEnter end if;
create procedure CSEnter()
begin
declare i int;
declare @t varchar(32);
set @t = current timestamp;
message current timestamp || ' Enter scroll for ' to client;
set i = 0;
while i < 3 loop
  message current timestamp || ' Begin waiting ' to client;
  waitfor delay '0:0:10';
  message current timestamp || ' End waiting ' to client;
  set i = i + 1;
end loop;
message current timestamp || ' Leave scroll for ' to client;
message DATEDIFF(millisecond, current timestamp, @t) || ' Execution time' to client;
end;

[VolkerBarth]

Would be interesting if you change "to client" to "to log" (possibly only inside the loop) - it may be that the client side just has to be "ready" to accept messages from the server, and that this may interfere here whereas writing to the servers's console log would be done immediately. Just guessing:)