select for update in stored procedure (concurrently increment a field)

Question

I want to retrieve the value of a field and increment it safely in Informix 12.1 when multiple users are connected.

What I want in C terms is lastnumber = counter++; in a concurrent environment.

The documentation mentions one way of doing this which is to make everyone connect with a wait parameter, lock the row, read the data, increment it and release the lock.

So this is what I tried:

begin work;

  select 
    lastnum 
  from tbllastnums
  where id = 1
  for update;

And I can see that the row is locked until I commit or end my session.

However when I put this in a stored procedure:

create procedure "informix".select_for_update_test();

  define vLastnum decimal(15);

begin work;

  select 
    lastnum 
  into vLastnum
  from tbllastnums
  where id = 1
  for update;

commit;

end procedure;

The database gives me a syntax error. (tried with different editors) So why is it a syntax error to write for update clause within a stored procedure? Is there an alternative to this?

Edit

Here's what I ended up with:

DROP TABLE if exists tstcounter;
^!^
CREATE TABLE tstcounter
(
   id       INTEGER   NOT NULL,
   counter  INTEGER   DEFAULT 0 NOT NULL
)
EXTENT SIZE 16
NEXT SIZE 16
LOCK MODE ROW;
^!^
ALTER TABLE tstcounter
   ADD CONSTRAINT PRIMARY KEY (id)
   CONSTRAINT tstcounter00;

^!^
insert into tstcounter values(1, 0);
^!^
select * from tstcounter;
^!^
drop function if exists tstgetlastnumber;
^!^
create function tstgetlastnumber(pId integer) 
returning integer as lastCounter

  define vCounter integer;

  foreach curse for 
    select counter into vCounter from tstcounter where id = pId 

    update tstcounter set counter = vCounter + 1 where current of curse;

    return vCounter with resume;
  end foreach;


end function;
^!^

Jonathan Leffler · Accepted Answer · 2016-12-23T04:20:32.237

SPL and cursors 'FOR UPDATE'

If you manage to find the right bit of the manual — Updating or Deleting Rows Identified by Cursor Name under the FOREACH statement in the SPL (Stored Procedure Language) section of the Informix Guide to SQL: Syntax manual — then you'll find the magic information:

Specify a cursor name in the FOREACH statement if you intend to use the WHERE CURRENT OF cursor clause in UPDATE or DELETE statements that operate on the current row of cursor within the FOREACH loop. Although you cannot include the FOR UPDATE keywords in the SELECT ... INTO segment of the FOREACH statement, the cursor behaves like a FOR UPDATE cursor.

So, you'll need to create a FOREACH loop with a cursor name and take it from there.

Access to the manuals

Incidentally, if you go to the IBM Informix Knowledge Center and see this icon:

that is the 'show table of contents' icon and you need to press it to see the useful information for navigating to the manuals. If you see this icon:

it is the 'hide table of contents' icon, but you should be able to see the contents down the left side. It took me a while to find out this trick. And I've no idea why the contents were hidden by default for me, but I think that was a UX design mistake if other people also suffer from it.

I saw a use of `foreach` in the docs but it wasn't linked to that part of the manual, so thanks for pointing that out. — nurettin, Dec 23 '16 at 06:36

select for update in stored procedure (concurrently increment a field)

1 Answers1

SPL and cursors 'FOR UPDATE'

Access to the manuals