call procedures wait for each other

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;

Start ISQL and connect to database;
Start another ISQL and connect to datbase;
In first ISQL execute call csenter();
Wait for about 10s
In second ISQL execute call csenter();
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???

message isql procedure

asked 09 Jan '13, 07:59

bomark
1●1●1●2
accept rate: 0%

edited 09 Jan '13, 08:06

Volker Barth
40.2k●361●550●822

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.

(09 Jan '13, 12:40) Breck Carter

flat view

One Answer:

active answers oldest answers newest answers popular answers

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;

permanent link

answered 10 Jan '13, 05:52

Jan24
75●3●4●8
accept rate: 0%

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

(10 Jan '13, 06:09) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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:

isql ×46
procedure ×34
message ×10

question asked: 09 Jan '13, 07:59

question was seen: 2,336 times

last updated: 10 Jan '13, 06:09

SAP SQL Anywhere

call procedures wait for each other

Follow this question

Related questions