how to enforce a cursor to work really "sensitive"?

[Baron]

Can someone tell why myproc2 is more effecient than myproc1?

create or replace table mytable(
username varchar(100),
connectionstatus bit
);
------------
create or replace procedure myproc1()
--myproc1 sets all connectionstatus = 0
begin
for mycurs as curs sensitive cursor for
select username as current_username from mytable where connectionstatus = 1
--time1
do
    update mytable set connectionstatus = 0 where username = current_username;
    waitfor delay '00:00:30';
--time2     
end for;
end;
------------
create or replace procedure myproc2()
--myproc2 sets all connectionstatus = 0
begin
--time3
declare repeatloop bit;
declare current_username varchar(100);
set repeatloop = 1;
while repeatloop = 1 loop
    select top 1 username into current_username from mytable
        where connectionstatus = 1 order by 1;
    if current_username is null then
        break;
    end if;
    update mytable set connectionstatus = 0 where username = current_username;
    if not exists (select 1 from mytable 
        where connectionstatus = 1) then
        set repeatloop = 0;
    end if;
    waitfor delay '00:00:30';
end loop;
--time4
end;

There is also another concurrent process (not shown in code above) which connects sporadically to the database (with a separate connection than myproc1 / myproc2) and sets the connectionstatus to 1.

The problem with myproc1 is that it can't set all connectionstatus's to 0;

For me it looks like that the building of the cursor (scanning mytable) happens at the begining (at time1) (despite the cursor is declared with sensitive clause). How can I change the declaration of the cursor so that it scans mytable again at the end (at time2)?

In other words, if at the begining of iteration n-1 there was only one user (say user1) with connectionstatus = 1, and during the execution of this iteration the connectionstatus for another user (say user2) was set to 1, then I am missing the iteration n intended to set connctionstatus = 0 for user2.

Unfortunately it is difficult to post the code for the other process to reproduce the error, but I tested it several times and at the end the solution came only by using myproc2.

[VolkerBarth]

In other words, if at the begining of iteration n-1 there was only one user (say user1) with connectionstatus = 1, and during the execution of this iteration the connectionstatus for another user (say user2) was set to 1, then I am missing the iteration n intended to set connctionstatus = 0 for user2.

In my understanding, a sensitive cursor decides what new row is returned during the fetch to the next row - respecting the current state of rows as to membership, values and ordering. However, it does NOT prevent that a row that has not fulfilled the condition during its test (say, based on its order) will be added lateron when it is changed and does now fulfill the condition. That would be a "phantom row" and can only be prevented by running the cursor loop with isolation level 3 (serializable) - but it would then block the other transaction (user2) who tries to set the connection status to 1.

[Baron]

In my understanding, a sensitive cursor decides what new row is returned during the fetch to the next row

Does this happen at the begining of the loop (time1) or at the end of the loop (time2)?

[VolkerBarth]

That depends on the various cursor type, see the docs on that topic...

For a sensitive cursor, membership, values and order are evaluated during each fetch (in my understanding).

[Baron]

In my last question I was meaning the sensitive cursor type.

Sorry but I dont understand exactly what you mean with "during each fetch". Does this mean the begining of the loop (where the select statement is, so in my case in "time1")?

It was also my first thought that the evaulation takes place in "time1", specially in case of using sensitive clause. But after several tests I figured out that it works somehow in another way, and so that went with myproc2

[VolkerBarth]

Does this mean the begining of the loop (where the select statement is, so in my case in "time1")?

Sensitive cursors do not build their result set on opening the cursor (in contrast, say, to insensitive cursors) but evaluate each row of the underlying table(s) when doing the next fetch. However, that does not mean they "requery" the complete query on each fetch, it just means that current changes by different transactions after the cursor has been opened will be regarded (influenced by isolation levels, as usually). If the cursor has checked a row for a particular user and evaluated its condition as not true, it will not check that row again even if the condition is true lateron and the cursor is still opened. (Note: This is all in my limited understanding of a certainly very complex topic, so I may be wrong.)

---

Aside: Note that the database engine is free to use a cursor with different semantics than requested (and issuing a warning) if it cannot provide the desired sensitivity for the actual query, as different cursor types do have different limitations on the possible queries. Do you get such a warning, so the real cursor type is not the one you asked for?

[Baron]

Ok, thanks for the explanation.

I did not get any such warning.

Moreover, I couldn't even see such warnings (if any) since I am starting the loop through triggering an event (separate connection).