Row Locking

[wellhole]

I'm trying to create a stored procedure to grab a record from a table and update the record.

declare val integer;
select COL2 into val from TAB1 where COL1 = 777;
update TAB1 set COL2 = COL2 + 1;

But, I fear this will allow anyone to run over the procedure and get the same COL2 value before I update the record. I hear using "lock table in exclusive mode" would be a bad thing to do. What can I do?

[cburnett]

Assuming that you only wish to update COL2 for the rows where COL1 = 777 then, in order of preference:

1. UPDATE TAB1 SET COL2 = COL2+1 WHERE COL1 = 777; SELECT COL2 - 1 INTO VAL FROM TAB1 WHERE COL1 = 777;
2. SELECT COL2 into val from TAB1 where COL1 = 777 WITH RS; UPDATE TAB1 SET COL2 = COL2+1 WHERE COL1 = 777;

Advantages are:

1. Takes X row locks on all changed rows first so no-one else can change the row you are selecting.
2. Takes a Next Key Share lock on the row retrieved and this prevents others from changing this row.

If you want to update all the rows in the table, then LOCK TABLE IN EXCLUSIVE MODE is probably your best choice as otherwise the UPDATE statement will take a row lock on every row in the table (same effect but less efficient).

[wellhole]

You're right I forgot to put in where COL1 = 777 in my update statement. The forum won't let me edit the first post to correct my mistake...

Oh, I wish it was that easy with those statements. I have to do some processing of the COL2 value before I do the update so #1 would not work. Sorry I didn't explain it better.

Would #2 prevent reading from this record for others? What I fear is that after I read from the record and while I'm processing the value, someone else reads the record and now we both have that same COL2 value.

[cburnett]

First up, you won't be able to prevent users reading with UNCOMMITTED READ. To stop others reading as well as writing you will need an exclusive row lock. In theory this can be done in 3 ways:

1. SELECT COL2 INTO val1 FROM TAB1 WHERE COL1 = 777 WITH RS USE AND KEEP EXCLUSIVE LOCKS
2. UPDATE TAB1 SET COL2 = COL2 WHERE COL1 = 777
3. UPDATE TAB1 SET COL2 = COL2+1 WHERE COL1 = 777

Interestingly although these all take and hold X row locks, only the last of these actually blocks reads; seems DB2 is trying to be clever and realising that the data isn't actually changing and reading through the X lock. So you might want to do the last option before selecting the data and then undoing the change with the real value after processing.

I should also have mentioned the traditional approach:

DECLARE CURSOR X FOR 
   SELECT COL2 from TAB1 where COL1 = 777 FOR UPDATE OF COL2;
OPEN CURSOR X;
FETCH X INTO val1;
...process val1
UPDATE X SET COL2=val1 WHERE CURRENT OF X;
CLOSE X;
 

Used to be the only way to do this in the past but a lot more code change for you.

[wellhole]

(option #1 doesn't compile for me so it must be for a different system).

I'm trying to test the row lock with a waiting loop immediately doing one of the above and running a select statement separately to read the row to see if its locked. The select has no problem reading the record. Do you know what I'm doing wrong?

create procedure sp_test
language SQL
begin
  declare v_counter integer default 0;
  update tab1 set col2 = col2 + 1 where col1 = 777;
 
  aloop:
  loop
    set v_counter = v_counter + 1;
    if v_counter = 5000000 then
      leave aloop;
    end if;
  end loop aloop;
end
 

create procedure sp_test
language SQL
begin
  declare v_counter integer default 0;
  declare val integer;
  declare c1 cursor for select col2 from tab1 where col1 = 777;
  open c1;
  fetch c1 into val;
 
  aloop:
  loop
    set v_counter = v_counter + 1;
    if v_counter = 5000000 then
      leave aloop;
    end if;
  end loop aloop;
end
 

[cburnett]

Two things come to mind:

• The procedure names are the same. Therefore the "read" procedure is actually being run twice.
• Your isloation level is Uncommitted Read. Change your select statement to add WITH CS to confirm this as this will override your default isolation level

INcidentally, when I rename the second procedure and run the two I get a lock wait as expected.

[wellhole]

After some testing with your recommendations, I've found that "for update of COL2" worked, and then I slapped on "with cs" for good measure. Thanks a lot; I've learned a lot.

Oddly, using just "for update" won't block and nor will just "with cs", but "for update of col2" apparently will do it.

declare v_counter integer default 0;
declare val integer;
declare c1 cursor for select col2 from tab1 where col1 = 777
for update of col2 with cs;
open c1;
fetch c1 into val;