Can I do a one-pass UPDATE at Isolation Level 1?

[Former Member]

I have a procedure that contains the following code that works perfectly in ISQL. Essentially, I SET a random number to each record, then SET another value (panel_code) based on that random number. It works great... with one pass through the table.

    UPDATE cheetah.mailing_pool mp
        SET 
            random_nbr = dba.f_rand(record_id) , 
            panel_code =  
            ( CASE 
              WHEN random_nbr >= @Panel_a_lower_threshold and random_nbr <  @Panel_b_lower_threshold   then 'A'
              WHEN random_nbr >= @Panel_b_lower_threshold and random_nbr <  @Panel_c_lower_threshold   then 'B'
              WHEN random_nbr >= @Panel_c_lower_threshold and random_nbr <  @Panel_d_lower_threshold   then 'C'
              WHEN random_nbr >= @Panel_d_lower_threshold and random_nbr <= 1   then 'D'

ELSE '!!!'  -- error, this shouldn't happen
              END )
        WHERE mp.mailing_id = @mailing_id;

However, the problem is that when I run it in my application, it fails. The table is updated with a reasonable random number, but the CASE expression drop through to the ELSE every time.

So I tried this...

    UPDATE cheetah.mailing_pool mp
        SET 
            random_nbr = dba.f_rand(record_id)     
     WHERE mp.mailing_id = @mailing_id;

UPDATE cheetah.mailing_pool mp
        SET 
            panel_code =  
            ( CASE 
              WHEN random_nbr >= @Panel_a_lower_threshold and random_nbr <  @Panel_b_lower_threshold   then 'A'
              WHEN random_nbr >= @Panel_b_lower_threshold and random_nbr <  @Panel_c_lower_threshold   then 'B'
              WHEN random_nbr >= @Panel_c_lower_threshold and random_nbr <  @Panel_d_lower_threshold   then 'C'
              WHEN random_nbr >= @Panel_d_lower_threshold and random_nbr <= 1   then 'D'

ELSE '!!!'  -- error, this shouldn't happen
              END )
        WHERE mp.mailing_id = @mailing_id;

And guess what? It works perfectly every time. It never drops though to the ELSE. So what's different?

I've tested the input parameters -- I know all the variables have the same value in the code leading up to the UPDATE... so that left me looking for Connection differences.

The only thing different I can find is the Isolation level -- ISQL comes in at Isolation level 0, and the application (where the one-pass code doesn't work) uses Isolation level 1.

So.. is this a reasonable outcome of using Isolation level 1, or do I have a differnt problem.

Mods... please edit the question. I had no idea how to phrase the question!

Thanks!

I'm using 9.0.2.3850

[MarkCulp · Accepted solution]

The issue is that the query is using the original (column) value of random_nbr in the case expression to determine which case sub-expression to use to assign to panel_code.

To do it in one pass, you need to do is compute the new set of random numbers and get your case expression to use the new random number for each row.

For example:

UPDATE cheetah.mailing_pool mp
   SET 
       random_nbr = new_rand,
       panel_code =  
            ( CASE 
              WHEN new_rand >= @panel_a_lower_threshold 
               and new_rand < panel_b_lower_threshold
              THEN 'A'
              WHEN new_rand >= @panel_b_lower_threshold
               and new_rand <  @panel_c_lower_threshold
              THEN 'B'
              WHEN new_rand >= @panel_c_lower_threshold
               and new_rand <  @panel_d_lower_threshold
              THEN'C'
              WHEN new_rand >= @panel_d_lower_threshold
               and new_rand <= 1
              THEN 'D'
              ELSE '!!!'  -- error, this shouldn't happen
              END )
  FROM ( select dba.f_rand(record_id) as new_rand,
                mailing_id
           from cheetah.mailing_pool
          where mailing_id = @mailing_id
       ) dt
 WHERE mp.mailing_id = dt.mailing_id
   and mp.mailing_id = @mailing_id;

The above may be more (or less) than what you need - i.e. I do not know how many rows in mailing_pool are going to be matched by your predicate mp.mailing_id = @mailing_id? If it matches more than one row, then you are going to need to add additional conditions between mp & dt so that each row gets a different random number from the derived table dt.

Note: I am assume that your f_rand function has been declared not deterministic so that it returns different random numbers for every row that is being updated? E.g.

CREATE FUNCTION f_rand()
RETURNS double
NOT DETERMINISTIC
BEGIN
    RETURN rand();
END;

FWIW: I tested the above using

create table foo(
     i int default autoincrement,
     r double,
     s varchar(100),
     primary key( i )
);

insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
insert into foo( s ) values( null );
commit;

select * from foo;

update foo f
   set r = new_rand,
       s = ( case
         when new_rand >= 0 and new_rand < 0.5 then 'a'
         when new_rand >= 0.5 and new_rand < 0.75 then 'b'
         when new_rand >= 0.75 and new_rand <= 1 then 'c'
         else '!'
         end )
 from ( select f_rand() as new_rand, * from foo ) dt
  where dt.i = f.i

select * from foo;

[Former Member · Accepted solution]

Thanks Mark -- you wrote "The issue is that the query is using the original (column) value of random_nbr in the case expression to determine which case sub-expression to use to assign to panel_code."

Does that behavior change with Isolation level? Is my diagnosis correct that it's the isolation level that determines if either the original value or the new value of random_nbr is used to process the CASE sub-expression?

[MarkCulp · Accepted solution]

@Ron: I do not believe that isolation level has any direct effect on the behaviour of how the expressions are evaluated. I tried my test (see bottom of post) with different isolation levels and did not see any difference in behaviour. I'm not sure why you saw a difference? Perhaps you ran your statement twice? Note that you need to be careful when using dbisql - depending on settings, it will sometimes run your statement twice under-the-covers. FWIW I used dbisqlc during my tests.