cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
2,355

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

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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
0 Kudos

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
Participant
0 Kudos

@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.

Answers (0)