Re: LOCK TABLE oddness in PLpgSQL function called via JDBC - Mailing list pgsql-jdbc

Dave Harkness wrote:
>
> At 01:45 PM 10/2/2001, Barry Lind wrote:
> >Dave,
> >
> >Secondly, you don't need a table lock, you just need to lock the row
> >between the select and the update.  You should use 'select for update' to
> >do this.  That way when you issue the select to get the current value, it
> >will lock the row, preventing other select for update requests from
> >completing until the lock is released.  That way the select and the update
> >can be assured that no one else is changing the data.
>
> THANK YOU! That's what I thought, but the documentation was a bit light on
> the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if I do
>
>      next_id_block ( count )
>      (1)   read idfactory row FOR UPDATE
>
>      (2)   update idfactory row
>                increment next_id by count
>                increment change_num by 1
>            where change_num is equal to that read in (1)
>
>      (3)   return next_id read in (1)

As far as I see, this is a stored function issue not a Java
issue.
I got the exact code of the function from Dave.

    create function next_id_block (
       varchar , integer
     )
     returns bigint
     as '
     DECLARE
       -- Parameters
       name_key          alias for $1 ;
       block_size        alias for $2 ;

       -- Locals
       id_rec            record ;
       num_rows          integer ;
     BEGIN
       -- To avoid a retry-loop, lock the whole table for the
transaction
       lock table idfactory in exclusive mode ;

       -- Read the current value of next_id
       select into id_rec * from idfactory where name = name_key ;

       -- Increment it by block_size
       update idfactory
           set next_id = next_id + block_size,
               change_num = change_num + 1
           where name = name_key and change_num = id_rec.change_num ;

       -- If the update failed, raise an exception
       get diagnostics num_rows = ROW_COUNT ;
       if num_rows != 1 then
         raise exception ''Update failed'' ;
         return -1 ;
       end if ;

       return id_rec.next_id ;
     END ;
     ' language 'plpgsql' ;

The cause is that the stored function uses a common
snapshot throughout the function execution. As I've
complained many times, the current implementaion is
far from intuition and this case seems to show that
it isn't proper at all either.

*lock table* certainly locks idfactory table but the
subsequenct *select* sees the table using the snapshot
taken before the function call. The *update* statement
find the row matching the where clause using the common
snapshot but will find the row was already updated and
the updated row doesn't satisfy the condition any longer.

[In case when we remove the *lock* statement and add a
 *for update* clause to the subsequent *select* statement]

The *select .. for update* statement gets the latest
(may be updated) change_num value. Unfortunately
the subsequent *update* statement has a where clause
containing change_num. The *update* statemnet can't
find the row matching the where clause using the snapshot
taken before the function call.

regards,
Hiroshi Inoue